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.

Last edited Dec 25, 2010 at 9:18 PM by altair, version 1

Comments

No comments yet.