Click here to Skip to main content
15,867,921 members
Articles / Web Development / ASP.NET

SQLite membership, profile, role, and personalization providers

Rate me:
Please Sign up or sign in to vote.
4.54/5 (15 votes)
30 Mar 2009GPL31 min read 77.8K   904   77   15
This is SQLite based Profile, Membership, Role, and Personalization Providers written in C#.

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:

SQL
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
<?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:

C#
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.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Software Developer (Senior) http://www.mascix.com/
Turkey Turkey

Comments and Discussions

 
GeneralFantastic work! Pin
ashantiel31-May-12 11:23
ashantiel31-May-12 11:23 
GeneralGreat! Pin
varunmaggo14-Sep-10 2:39
varunmaggo14-Sep-10 2:39 
GeneralGPL Version Pin
Sam Collett8-Apr-10 23:50
Sam Collett8-Apr-10 23:50 
GeneralMany thanks!!! Pin
shyouryuudou13-Dec-09 1:51
shyouryuudou13-Dec-09 1:51 
GeneralRe: Many thanks!!! Pin
ozkan.pakdil13-Dec-09 20:38
ozkan.pakdil13-Dec-09 20:38 
very nice. btw you should check this out
SQLite Membership, Role, and Profile Providers[^]


GeneralBug in SQLiteMembershipProvider Pin
marc1530-Mar-09 1:59
marc1530-Mar-09 1:59 
GeneralRe: Bug in SQLiteMembershipProvider Pin
ozkan.pakdil30-Mar-09 6:25
ozkan.pakdil30-Mar-09 6:25 
GeneralRe: Bug in SQLiteMembershipProvider Pin
marc1530-Mar-09 6:56
marc1530-Mar-09 6:56 
GeneralRe: Bug in SQLiteMembershipProvider Pin
ozkan.pakdil30-Mar-09 10:17
ozkan.pakdil30-Mar-09 10:17 
GeneralYou should explain more Pin
Donsw16-Feb-09 15:24
Donsw16-Feb-09 15:24 
GeneralAlternative SQLite providers Pin
Roger Martin2-Oct-08 11:13
professionalRoger Martin2-Oct-08 11:13 
GeneralProfile provider does not work Pin
Roger Martin27-May-08 11:02
professionalRoger Martin27-May-08 11:02 
GeneralRe: Profile provider does not work [modified] Pin
ozkan.pakdil7-Jan-09 21:23
ozkan.pakdil7-Jan-09 21:23 
NewsI have tried this Pin
ozkan.pakdil4-Oct-07 14:27
ozkan.pakdil4-Oct-07 14:27 
GeneralRe: I have tried this Pin
Roger Martin2-Oct-08 11:14
professionalRoger Martin2-Oct-08 11:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.