SQLite membership, profile, role, and personalization providers

• Download source code v2.1 - 341 KB

Introduction

This article provides four files that contain a Membership Provider, a Role Provider, a Profile Provider, and a Personalization Provider. I took the Membership and Role providers from here. I rewrote the Profile Provider from here. I rewrote the Personalization Provider from here. Also take a look at SQLite ASP.NET providers.

Background

It would be useful if you have used Membership and Profile Providers before. But it is not necessary.

Using the provider

Step 1: Get any kind of SQLite management tool. I used this.

Step 2: Build the SQL below:

CREATE TABLE Roles
(
  Rolename Varchar (255) NOT NULL,
  ApplicationName varchar (255) NOT NULL
);

CREATE TABLE UsersInRoles
(
  Username Varchar (255) NOT NULL,
  Rolename Varchar (255) NOT NULL,
  ApplicationName Text (255) NOT NULL
);

CREATE TABLE `aspnet_applications` (
  `ApplicationName` text default NULL,
  `ApplicationId` int(11) default NULL,
  `Description` text default NULL,
  `PropertyValuesString` text default NULL,
  PRIMARY KEY  (`ApplicationId`)
);

CREATE TABLE `aspnet_profile` (
 `PKID` varchar(36)   NOT NULL default '',
 `LastUpdatedDate` datetime default NULL,
 `PropertyNames` text default NULL,
 `PropertyValuesString` text default NULL
);

CREATE TABLE [personalization] (
[username] varchar(255) DEFAULT 'NULL' NULL,
[path] varchar(255) DEFAULT 'NULL' NULL,
[applicationname] varchar(255) DEFAULT 'NULL' NULL,
[personalizationblob] BLOB DEFAULT 'NULL' NULL
);

CREATE TABLE 'users' (
  'PKID' varchar(36)  NOT NULL default '',
  'Username' varchar(255)  NOT NULL default '',
  'ApplicationName' varchar(100) 
                     NOT NULL default '',
  'Email' varchar(100)  NOT NULL default '',
  'Comment' varchar(255)  default NULL,
  'Password' varchar(128)  NOT NULL default '',
  'PasswordQuestion' varchar(255)  default NULL,
  'PasswordAnswer' varchar(255)  default NULL,
  'IsApproved' tinyint(1) default NULL,
  'LastActivityDate' datetime default NULL,
  'LastLoginDate' datetime default NULL,
  'LastPasswordChangedDate' datetime default NULL,
  'CreationDate' datetime default NULL,
  'IsOnLine' tinyint(1) default NULL,
  'IsLockedOut' tinyint(1) default NULL,
  'LastLockedOutDate' datetime default NULL,
  'FailedPasswordAttemptCount' int(11) default NULL,
  'FailedPasswordAttemptWindowStart' datetime default NULL,
  'FailedPasswordAnswerAttemptCount' int(11) default NULL,
  'FailedPasswordAnswerAttemptWindowStart' datetime default NULL,
  PRIMARY KEY  ('PKID')
);

CREATE INDEX idxRoles ON Roles ( 'Rolename' , 'ApplicationName' );

CREATE INDEX idxUIR ON UsersInroles ( 'Username', 'Rolename', 'ApplicationName');

CREATE INDEX [IDX_PERSONALIZATION_] ON [personalization](
[username]  ASC,
[path]  ASC,
[applicationname]  ASC
);

Step 3: Add SQLiteMembershipProvider.cs, SQLiteProfileProvider.cs, SQLiteRoleProvider.cs, and SQLitePersonalizationProvider.cs to your app_code folder.

Step 4: Change your web.config like this:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <appSettings/>
  <connectionStrings>
    <add name="ConnString"
         connectionString="Data Source=|DataDirectory|Provider.db;Version=3;" />
  </connectionStrings>
  <system.web>
    <authorization>
      <deny roles="Admin" />
    </authorization>
    <authentication mode="Forms" />
    <compilation debug="true">
    </compilation>
    <roleManager
      defaultProvider="SQLiteRoleProvider"
      enabled="true"
      cacheRolesInCookie="true"
      cookieName=".ASPROLES"
      cookieTimeout="30" cookiePath="/"
      cookieRequireSSL="false"
      cookieSlidingExpiration="true"
      cookieProtection="All">
      <providers>
        <clear/>
        <add 
          name="SQLiteRoleProvider"
          type="Mascix.SQLiteProviders.SQLiteRoleProvider"
          connectionStringName="ConnString"
          applicationName="TEST"
          writeExceptionsToEventLog="true"/>
      </providers>
    </roleManager>
    <membership defaultProvider="SQLiteMembershipProvider"
                userIsOnlineTimeWindow="15">
      <providers>
        <clear/>
        <add
          name="SQLiteMembershipProvider"
          type="Mascix.SQLiteProviders.SqliteMembershipProvider"
          connectionStringName="ConnString"
          applicationName="TEST"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          requiresQuestionAndAnswer="false"
          requiresUniqueEmail="true"
          passwordFormat="Hashed"
          writeExceptionsToEventLog="true"/>
      </providers>
    </membership>

    <profile defaultProvider="SQLiteProfileProvider">
      <providers>
        <clear/>
        <add 
          name="SQLiteProfileProvider"
          type="Mascix.SQLiteProviders.SQLiteProfileProvider"
          connectionStringName="ConnString"
          applicationName="TEST"/>
      </providers>
      <properties>
        <add name="Name" type="System.String"/>
        <add name="SureName" type="System.String"/>
        <add name="Address" type="System.String"/>
        <add name="County" type="System.Int32"/>
        <add name="City" type="System.Int32"/>
        <add name="PostalCode" type="System.String"/>
        <add name="HomePhone" type="System.String"/>
        <add name="WorkPhone" type="System.String"/>
        <add name="CellPhone" type="System.String"/>
        <add name="AdsenseID" type="System.String"/>
        <add name="AdsenseChannelID" type="System.String"/>
      </properties>
    </profile>
    <webParts>
      <personalization defaultProvider="personalizationProvider">
        <providers>
          <clear />
          <add name="personalizationProvider"
               type="Mascix.SQLiteProviders.SQLitePersonalizationProvider" 
               connectionStringName="ConnString" 
               applicationName="TEST"
               description="MySQL Personalization Provider"
               />
        </providers>
      </personalization>
    </webParts>
  </system.web>
</configuration>

Step 5: Add System.Data.SQLite.DLL as a reference to your web project.

Step 6: Here is an example of usage:

protected void Page_Load(object sender, EventArgs e)
{
    Profile.HomePhone = "2122233";
}

Now you are ready to call and use all these four providers from your code. And, you can use fancy ASP.NET login components. http://sqlite.phxsoftware.com/ has done most of the job already, thanks for such a nice engine for SQLite. It would be very nice if you can send me some test results or bugs and benchmarks.

Points of interest

SQLite is developing nice and smoothly. Because this article says you have to pay attention to the connection string because it has to point directly to the DB file, no virtual path is usable. But after I upgraded the DLL, the SQLite.net engine can understand this "Data Source=|DataDirectory|Provider.db;Version=3;". It gave me some headache at the beginning.

History

• 08-01-2009 - Added WebParts ability (Personalization Provider).
• 26-09-2007 - First release.