Simple SQL Providers

Please note that the "Simple SQL" family of providers is no longer developed and supported and is included for backward compatibility only. For new development please use TableMembershipProvider and TableRoleProvider instead. The profile provider is no longer developed here, use SqlTableProfileProvider.

The provider object model, as present in ASP.NET 2.0, is a great step forward from hand-coded behavior in ASP.NET 1.x. But the provider model is only as useful as are available providers.

Microsoft .NET itself contains set of profiles which can store their data in Microsoft SQL Server database: SqlMembershipProvider, SqlRoleProvider and SqlProfileProvider. However, database structure used by these providers is pretty complicated and almost impossible to interconnect with your own tables etc. Drop the “almost” for profile provider.

So I created set of my own providers and called them SimpleSqlMembershipProvider, SimpleSqlRoleProvider and SimpleSqlProfileProvider. They are simple in two ways: first, they are sort of minimal implementation and do not have the full functionality of built-in providers. Second, they use simple table structure (one table per provider, role provider has two), which can be linked to other database structure as usual.

These providers are of course written in 100% managed code (C#) and are totally independent on each other. So you may for example use my profile provider if you like it and keep using whatever else provider for membership. This should be general truth, but in reality it isn’t. For example, the Table profile provider samples, although having the same virtue of simple table structure for profile properties, are internally dependent on default SQL Membership Provider and cannot be used with any other.

Creating the table structure

The process of creating required tables is described in the documentation (SimpleSqlProviders-Documentation.docx). It can be highly customized, table names and even their structure to some degree are configurable. To create the default configuration, use the SimpleSqlProviders-CreateTables.sql script. Its content is commented below

Membership provider

The following SQL code will create the Users table, the only table needed for membership provider:

CREATE TABLE dbo.Users(
UserId int IDENTITY(1,1) NOT NULL,
UserName varchar(100) NOT NULL,
PasswordHash char(86) NOT NULL,
PasswordSalt char(5) NOT NULL,
Email varchar(100) NOT NULL,
Comment text NULL,
Enabled bit NOT NULL,
DateCreated datetime NOT NULL,
DateLastLogin datetime NULL,
DateLastActivity datetime NULL,
DateLastPasswordChange datetime NOT NULL,

CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserId ASC),
CONSTRAINT IX_Users_UserName UNIQUE NONCLUSTERED (UserName ASC)
)

Role provider

Role provider uses two tables. The Roles table contains list of available roles and UsersInRoles table contains mapping of users and roles. The following code will create the tables:

CREATE TABLE dbo.Roles(
RoleName varchar(100) NOT NULL,

CONSTRAINT PK_Roles PRIMARY KEY CLUSTERED (RoleName ASC
)
)


CREATE TABLE dbo.UsersInRoles(
HashId int IDENTITY(1,1) NOT NULL,
UserName varchar(100) NOT NULL,
RoleName varchar(100) NOT NULL,

CONSTRAINT PK_UsersInRoles PRIMARY KEY CLUSTERED (HashId ASC),
CONSTRAINT FK_UsersInRoles_Roles FOREIGN KEY (RoleName) REFERENCES dbo.Roles (RoleName) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FK_UsersInRoles_Users FOREIGN KEY (UserName) REFERENCES dbo.Users (UserName) ON UPDATE CASCADE ON DELETE CASCADE
)

The code above expects that you're using the SimpleSqlMembershipProvider as well and creates foreign key link to the Users table, so when user goes deleted, the role membership is deleted as well. If you are using another provider or don't want this functionality, do not create the FKUsersInRolesUsers constraint.

Profile provider

The structure of profile provider table depends on the profile structure. See documentation and examples.

Configuration

After creating the required tables, you must set the providers in web.config, instead of default providers. The following configuration file uses full suite of the Simple SQL Providers:
<?xml version="1.0"?>
<
configuration
>
    <
appSettings
/>
    <
connectionStrings
>

        <
add name="SimpleAuthDB" providerName="System.Data.SqlClient"
connectionString=""/>
    </
connectionStrings
>
    <
system.web
>
        <
compilation debug="true" targetFramework="4.0"
/>
        <
authentication mode="Forms"
>
            <
forms defaultUrl="~/Auth/Default.aspx" loginUrl="~/Default.aspx"
/>
        </
authentication
>
        <
membership defaultProvider="MyMembershipProvider"
>
            <
providers
>
                <
clear
/>
                <
add name="MyMembershipProvider" 
type="Altairis.Web.Security.SimpleSqlMembershipProvider, Altairis.Web.Security"
connectionStringName="SimpleAuthDb"/>
            </
providers
>
        </
membership
>
        <
roleManager enabled="true" defaultProvider="MyRoleProvider"
>
            <
providers
>
                <
clear
/>
                <
add name="MyRoleProvider" 
type="Altairis.Web.Security.SimpleSqlRoleProvider, Altairis.Web.Security" 
connectionStringName="SimpleAuthDb"
/>
            </
providers
>
        </
roleManager
>
        <
profile enabled="true" automaticSaveEnabled="false" defaultProvider="MyProfileProvider"
>
            <
providers
>
                <
clear
/>
                <
add name="MyProfileProvider" 
type="Altairis.Web.Security.SimpleSqlProfileProvider, Altairis.Web.Security" 
connectionStringName="SimpleAuthDb" 
tableName="Profiles" 
keyColumnName="UserName" 
lastUpdateColumnName="LastUpdate"
/>
            </
providers
>
            <
properties
>
                <
add name="FullName" type="String" 
customProviderData="FullName;varchar;50"
/>
                <
add name="BirthDate" type="DateTime"
defaultValue="01.01.1900" 
customProviderData="BirthDate;smalldatetime"
/>
                <
add name="PageSize" type="Int32"
defaultValue="10" 
customProviderData="PageSize;int"
/>
            </
properties
>
        </
profile
>
    </
system.web
>
</
configuration
>

Last edited Dec 25, 2010 at 7:22 PM by altair, version 5

Comments

No comments yet.