SQL Server Compact Support
The Compact Edition of Microsoft SQL Server is embedded database, which can serve as lightweight database server for smaller applications. The new version 4.0 (in CTP stage at time of writing) supports the ASP.NET web applications (unlike the previous versions).
The
TableMembershipProvider and
TableRoleProvider classes in this release support SQL CE as their data store.
Creating the SDF file
The SQL Compact database lives in .sdf file. Right now it's quite tricky to create SQL CE 4.0 sdf files, because of limited tooling support. There are third-party tools (like
SQL CE Toolbox. Service Pack 1 for Visual Studio 2010 will add supprt to VS 2010 and you can also manage SDF files with WebMatrix.
The source includes SDF file with empty tables in
/trunk/Altairis.Web.Security/Resources/TableProvidersCE.sdf. You can also use the following SQL commands to create the required tables (please note that you must run only one command at a time, as SQL CE does not understand batches):
-- Table for TableMembershipProvider
CREATE TABLE Users (
UserName nvarchar(100) NOT NULL,
PasswordHash binary(64) NOT NULL,
PasswordSalt binary(128) NOT NULL,
Email nvarchar(100) NOT NULL,
Comment nvarchar(4000) NULL,
IsApproved bit NOT NULL,
DateCreated datetime NOT NULL,
DateLastLogin datetime NULL,
DateLastActivity datetime NULL,
DateLastPasswordChange datetime NOT NULL,
CONSTRAINT PK_Users PRIMARY KEY (UserName)
)
-- Tables for TableRoleProvider
CREATE TABLE Roles (
RoleName nvarchar(100) NOT NULL,
CONSTRAINT PK_Roles PRIMARY KEY (RoleName)
)
CREATE TABLE RoleMemberships (
UserName nvarchar(100) NOT NULL,
RoleName nvarchar(100) NOT NULL,
CONSTRAINT PK_RoleMemberships PRIMARY KEY (UserName, RoleName),
CONSTRAINT FK_RoleMemberships_Roles FOREIGN KEY (RoleName)
REFERENCES Roles (RoleName) ON UPDATE CASCADE ON DELETE CASCADE
)
-- When using both these providers together, you may want to add the foreign key
ALTER TABLE RoleMemberships
ADD CONSTRAINT FK_RoleMemberships_Users FOREIGN KEY (UserName)
REFERENCES Users (UserName) ON UPDATE CASCADE ON DELETE CASCADE
Configuring providers to use database
No special configuration is needed. The only requirement is to set
providerName attribute of connection string to
System.Data.SqlServerCe.4.0. The library will use the right provider based on this. You must also reference the
See sample web.config file for using SQL CE database:
<?xml version="1.0"?>
<configuration>
<appSettings/>
<connectionStrings>
<add name="TableAuthDB"
providerName="System.Data.SqlServerCe.4.0"
connectionString="Data Source=|DataDirectory|TableAuthDB.sdf"/>
</connectionStrings>
<!-- No furhter changes required, common provider configuration included for reference -->
<system.web>
<membership defaultProvider="MyMembershipProvider">
<providers>
<clear/>
<add name="MyMembershipProvider"
type="Altairis.Web.Security.TableMembershipProvider, Altairis.Web.Security"
connectionStringName="TableAuthDB" />
</providers>
</membership>
<roleManager enabled="true" defaultProvider="MyRoleProvider">
<providers>
<clear/>
<add name="MyRoleProvider"
type="Altairis.Web.Security.TableRoleProvider, Altairis.Web.Security"
connectionStringName="TableAuthDB"/>
</providers>
</roleManager>
</system.web>
</configuration>
Sample
The download includes example
TableProviderSampleCE, which is similar to
TableProviderSample, just uses the SQL CE instead of SQL Server.