Click here to Skip to main content
Click here to Skip to main content
Go to top

SQLite membership, profile, role, and personalization providers

, 30 Mar 2009
Rate this:
Please Sign up or sign in to vote.
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:

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.

License

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

Share

About the Author

ozkan.pakdil
Software Developer (Senior) http://www.mascix.com/
Turkey Turkey

Comments and Discussions

 
GeneralFantastic work! Pinmemberashantiel31-May-12 11:23 
GeneralGreat! Pinmembervarunmaggo14-Sep-10 2:39 
GeneralGPL Version PinmemberSam Collett8-Apr-10 23:50 
GeneralMany thanks!!! Pinmembershyouryuudou13-Dec-09 1:51 
GeneralRe: Many thanks!!! Pinmembermascix13-Dec-09 20:38 
GeneralBug in SQLiteMembershipProvider Pinmembermarc1530-Mar-09 1:59 
GeneralRe: Bug in SQLiteMembershipProvider Pinmembermascix30-Mar-09 6:25 
GeneralRe: Bug in SQLiteMembershipProvider Pinmembermarc1530-Mar-09 6:56 
GeneralRe: Bug in SQLiteMembershipProvider Pinmembermascix30-Mar-09 10:17 
GeneralYou should explain more PinmemberDonsw16-Feb-09 15:24 
You should explain more, outlining the code with no explaination of what is going on is not an article. I can get the code from many other places.
 
cheers,
Donsw
My Recent Article : Optimistic Concurrency with C# using the IOC and DI Design Patterns

GeneralAlternative SQLite providers PinmemberRoger Martin2-Oct-08 11:13 
GeneralProfile provider does not work PinmemberRoger Martin27-May-08 11:02 
GeneralRe: Profile provider does not work [modified] Pinmembermascix7-Jan-09 21:23 
NewsI have tried this Pinmembermascix4-Oct-07 14:27 
GeneralRe: I have tried this PinmemberRoger Martin2-Oct-08 11:14 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 30 Mar 2009
Article Copyright 2007 by ozkan.pakdil
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid