SQLite Membership, Role, and Profile Providers

• Download source - 1 MB

Introduction

This is an implementation of the Membership, Role, and Profile providers for ASP.NET 2.0. It is fully functional and implements 100% of the membership, roles, and profiles API defined by Microsoft.

This is the same code that is included with the shipping release of Gallery Server Pro, so you can be assured it is production ready. However, if you do find any issues, please contact me and I will correct them.

It is intended that this code can be used as a drop-in replacement for the SQL Server providers by Microsoft. You can even use the backup/restore functionality in Gallery Server Pro to migrate your SQL Server membership, role, and profile data to SQLite, or the other way around. (You don't have to use the rest of the Gallery Server Pro functionality if all you need is help migrate your users.)

Background

I am the creator and lead developer for Gallery Server Pro, an open source photo, video, audio, and document web gallery (read the CodeProject article). It was originally designed to store data in SQL Server, but in September 2008, I added SQLite as the default data provider, while keeping SQL Server as an option.

I couldn't find any off-the-shelf SQLite providers, so I started with the work done by another CodeProject author. I thank mascix for getting me started, but in the end, I did a nearly complete rewrite of the code. The original code had a lot of issues, a few security holes, inefficiencies, behavior inconsistent with the SQL Server providers, and no support for transactions (which is critical for performance in SQLite).

I addressed these issues to create a solid set of providers for SQLite. I thought they might be useful to the general community, so I refactored them out of the Gallery Server Pro codebase and wrote up this article. Enjoy!

Using the Sample Code

The source code is a web application containing the SQLite providers and an empty SQLite database. Download the code, compile it, and run the ASP.NET Configuration tool to add a few roles and users. (In Visual Studio, choose ASP.NET Configuration from the Project menu.)

Using the Code in Your Own Project

Step 1: Get the latest version of System.Data.SQLite, the ADO.NET wrapper around the SQLite core DLL. Robert Simpson has done an incredible job with this wrapper. Send this guy a donation!

Step 2: Copy the SQLite database file into the App_Data directory of your project. You will find it at ~/App_Data/app_data.sqlite in the download code. Or, create a new, empty database, and execute the SQL in the InstallMembership.sql file to set up the tables.

Step 3: Add the code files containing the classes SQLiteMembershipProvider, SQLiteRoleProvider, and SQLiteProfileProvider to your project.

Step 4: Edit web.config to configure the providers, like this:

<?xml version="1.0" ?>
  <configuration>
  <connectionStrings>
    <clear />
    <add name="SQLiteDbConnection"
     connectionString="Data Source=
        |DataDirectory|app_data.sqlite;Version=3;" />
  </connectionStrings>

  <system.web>
    <compilation debug="false" />
    <authentication mode="Forms" />

    <!-- Configure the Membership provider.-->
    <membership defaultProvider="SQLiteMembershipProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
                passwordFormat="Clear"
                minRequiredNonalphanumericCharacters="0" 
                minRequiredPasswordLength="2"
                maxInvalidPasswordAttempts="2" 
                enablePasswordReset="true" 
                enablePasswordRetrieval="true"
                passwordAttemptWindow="10" 
                requiresQuestionAndAnswer="false"
                requiresUniqueEmail="false" 
                connectionStringName="SQLiteDbConnection"
                name="SQLiteMembershipProvider" 
                type="TechInfoSystems.Data.SQLite.SQLiteMembershipProvider, 
                     TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </membership>

    <!-- Configure the Role provider.-->
    <roleManager enabled="true" cacheRolesInCookie="true" 
                    cookieProtection="Validation"
                    defaultProvider="SQLiteRoleProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
            connectionStringName="SQLiteDbConnection"
            name="SQLiteRoleProvider" 
            type="TechInfoSystems.Data.SQLite.SQLiteRoleProvider, 
                 TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </roleManager>

    <!-- Configure the Profile provider.-->
    <profile defaultProvider="SQLiteProfileProvider">
      <providers>
        <clear />
        <add applicationName="SQLite ASP.NET Provider" 
            connectionStringName="SQLiteDbConnection"
            name="SQLiteProfileProvider" 
            type="TechInfoSystems.Data.SQLite.SQLiteProfileProvider, 
                 TechInfoSystems.Data.SQLiteProvider" />
      </providers>
    </profile>

  </system.web>
</configuration>

Step 5: Add a reference to System.Data.SQLite.DLL to your web project. Choose either the 32-bit or 64-bit version, depending on your server. The download for this article includes both versions, but there is probably a newer version available from here.

Step 6: Compile your application, and you are ready to go! For example, start the ASP.NET Configuration tool to add a few roles and users. (In Visual Studio, choose ASP.NET Configuration from the Project menu.)

At this point, you can use any of the membership, role, and profile functions.

Managing Your SQLite Database

There are several management tools available that let you view/edit the tables and other objects. Here are a few I have used:

• System.Data.SQLite - The ADO.NET wrapper around the SQLite core DLL also includes excellent data management tools that integrate directly into Visual Studio. Sweet!
• SQLite Manager - This is an add-on that plugs into various applications, such as Mozilla Firefox. It is lightweight and easy to use.
• SQLite Administrator - I don't use this any more, as the first two get the job done. But, this one also works well.

Transaction Support

It is well known among SQLite developers that executing multiple SQL statements in individual transactions result in terrible performance. Consider the following:

foreach (ProfileInfo profile in ProfileManager.GetAllInactiveProfiles(
  ProfileAuthenticationOption.Anonymous,
  DateTime.Today))
{
  Membership.DeleteUser(profile.UserName, true);
}

This code deletes all anonymous users who visited your site before today. Each call to DeleteUser executes in its own transaction, so if there are dozens or hundreds of users to delete, this code can take a very long time.

This code is not a problem for SQL Server, but it is a serious performance issue for SQLite. The solution is to explicitly start a transaction and then commit it when finished:

private static void DeleteAnonymousProfiles()
{
  BeginTransaction();

  try
  {
    foreach (ProfileInfo profile in ProfileManager.GetAllInactiveProfiles(
      ProfileAuthenticationOption.Anonymous,
      DateTime.Today))
    {
      Membership.DeleteUser(profile.UserName, true);
    }
    CommitTransaction();
  }
  catch
  {
    RollbackTransaction();
    throw;
  }
}

/// <summary>
/// Begins a new database transaction. All subsequent database actions occur within 
/// the context of this transaction. Use <see cref="CommitTransaction"/> to 
/// commit this transaction or <see cref="RollbackTransaction" /> to abort it. 
/// If a transaction is already in progress, then this method returns without any 
/// action, which preserves the original transaction.</summary>
/// <remarks>Transactions are supported only when the client is a web application.
/// This is because the transaction is stored in the HTTP context Items property. If 
/// the client is not a web application, then 
/// <see cref="System.Web.HttpContext.Current"
/// /> is null. When this happens, this method returns without taking any action.
/// </remarks>
public override void BeginTransaction()
{
  // Create new connection and transaction and place in HTTP context.
  if (System.Web.HttpContext.Current == null)
    return;

  if (IsTransactionInProgress())
    return;

  SQLiteConnection cn = GetDBConnection();
  if (cn.State == ConnectionState.Closed)
    cn.Open();

  SQLiteTransaction tran = cn.BeginTransaction();

  System.Web.HttpContext.Current.Items["SQLiteTran"] = tran;
}

/// <summary>
/// Commits the current transaction, if one exists. A transaction is created with 
/// the <see cref="BeginTransaction"/> method. If there is not an existing 
/// transaction, no action is taken. If this method is called when a datareader 
/// is open, the actual commit is delayed until all datareaders are disposed.
/// </summary>
/// <remarks>Transactions are supported only when the client is a web 
/// application. This is because the transaction is stored in the HTTP context Items 
/// property. If the client is not a web application, then <see 
/// cref="System.Web.HttpContext.Current" /> is null. When this happens, this 
/// method returns without taking any action.</remarks>
public override void CommitTransaction()
{
  // Look in HTTP context for previously created connection and transaction.
  // Commit transaction.
  if (System.Web.HttpContext.Current == null)
    return;

  SQLiteTransaction tran =
    (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];
   
  if (tran == null)
    return;

  // This closes the connection and nulls out the Connection property 
  // on the transaction.
  tran.Commit(); 

 System.Web.HttpContext.Current.Items.Remove("SQLiteTran");
}

/// <summary>
/// Aborts the current transaction, if one exists. A transaction is created with 
/// the <see cref="BeginTransaction"/> method. If there is not an existing 
/// transaction, no action is taken./// </summary>
/// <remarks>Transactions are supported only when the client is a web 
/// application. This is because the transaction is stored in the HTTP context Items 
/// property. If the client is not a web application, then <see 
/// cref="System.Web.HttpContext.Current" /> is null. When this happens, this 
/// method returns without taking any action.</remarks>
public override void RollbackTransaction()
{
  // Look in HTTP context for previously created connection and transaction.
  // Abort transaction.
  if (System.Web.HttpContext.Current == null)
    return;

  SQLiteTransaction tran = 
    (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];
    
  if (tran == null)
    return;

  // This closes the connection and nulls out the Connection property 
  // on the transaction.
	try
	{
		tran.Rollback();
	}
	catch (SQLiteException) { }

  System.Web.HttpContext.Current.Items.Remove("SQLiteTran");
}

/// <summary>
/// Determines whether a database transaction is in progress.
/// </summary>
/// <returns>
///     <c>true</c> if a database transaction is in progress; 
/// otherwise, <c>false</c>.
/// </returns>
/// <remarks>A transaction is considered in progress if an instance of 
/// <see cref="SQLiteTransaction"/> is found in the <see 
/// cref="System.Web.HttpContext.Current"/> Items property and its connection 
/// string is equal to the current provider's connection string.</remarks>
private static bool IsTransactionInProgress()
{
  if (System.Web.HttpContext.Current == null)
    return false;

  SQLiteTransaction tran = 
      (SQLiteTransaction)System.Web.HttpContext.Current.Items["SQLiteTran"];

  if ((tran != null) && (String.Equals
        (tran.Connection.ConnectionString, _connectionString)))
    return true;
  else
    return false;
}

The SQLite providers are hard-coded to look for an instance of a SQLiteTransaction object in the current HttpContext Items bag. If it finds one, it uses that transaction for the current action. If it doesn't find one - either because you didn't add a transaction to HttpContext or because the current app is not a web application, the current action runs in its own transaction.

Often, you don't need to worry about transactions. For example, say you are creating a user with Membership.CreateUser. Behind the scenes, this will execute just one SQL statement (or at most two or three), so you won't gain much by wrapping it in a transaction. Just worry about those cases where you are making a lot of calls in a single HTTP request.

I recommend you place the functions BeginTransaction, CommitTransaction, and RollbackTransaction in your data layer, along with the provider implementations. This will save you from having to reference the SQLite DLL in your UI layer. For an example of how these providers are used in a production application, download the source code for Gallery Server Pro.

Splitting Off Roles and Profiles into a Separate Application

These providers include one feature not found in the standard provider API - the ability to associate roles and profiles with a different application than the membership. First, I'll show how to do this, then I'll provide some background and explain why you might use this feature.

Here is an example where the roles and profile providers use a different application than the membership:

<membership defaultProvider="SQLiteMembershipProvider"> 
 <providers> 
  <clear /> 
   <add
    applicationName="App A"
    name="SQLiteMembershipProvider"
    ...additional stuff here... />   
 </providers> 
</membership>

<roleManager defaultProvider="SQLiteRoleProvider" ...> 
 <providers> 
  <clear /> 
  <add
   applicationName="App B"
   membershipApplicationName="App A"
   name="SQLiteRoleProvider"
   ...additional stuff here... /> 
 </providers> 
</roleManager>
	
<profile defaultProvider="SQLiteProfileProvider">
 <providers>
  <clear />
  <add
   applicationName="App B"
   membershipApplicationName="App A"
   name="SQLiteProfileProvider"
   ...additional stuff here...  />
 </providers>
</profile>
	

Notice the membership section specifies "App A" for the application name while the role and profile sections specify "App B". This will cause the roles and profile data to be associated with a different application than the membership. But in order to function properly, the role and profile providers need to know where the users are stored, so I created a new attribute membershipApplicationName and assigned it to "App A". That allows the role and profile providers to look up a user when necessary while keeping their own role and profile data in a separate application.

If you are not sure what I mean when I mean by "application", recall that the ASP.NET membership system can use a single data store for one or more applications. An application is uniquely defined by the applicationName attribute in the membership, roles, and provider definitions in web.config. In the example above, there are two applications defined: "App A" and "App B". If you peek in the aspnet_Applications table in your database, you will see one record for each. If you define a third provider with a new app name, ASP.NET will automatically insert a third record during initialization.

Why would you want to do this? Perhaps you have several web sites that all use the same set of users for authentication, but you want to keep the roles and profiles in each web site separate from the others. By separating the roles into their own application space, you can share users across web sites while keeping the roles unique to each one.

Migrating Data Between SQL Server and SQLite

If you have existing accounts in SQL Server, you can import them into SQLite using the backup / restore function in Gallery Server Pro. The basic steps are:

1. Install Gallery Server Pro and configure it to point to your existing membership, role, and profile data in SQL Server.
2. Use the backup function in Gallery Server Pro to export your user accounts to an XML file.
3. Re-run the Gallery Server Pro Web Installer, this time selecting SQLite as the data store.
4. Use the restore function to import your user accounts from the XML file.
5. Use your SQLite management tool to open the SQLite database (~/App_Data/galleryserverpro_data.sqlite). Delete the tables that begin with "gs_". These are Gallery Server Pro tables and you don't need them - you just want to keep the ones that start with "aspnet_".
6. Delete the Gallery Server Pro web application, but keep the SQLite database file.
7. Now, your users, roles, and profile data are in SQLite!

This is admittedly a kind of hacky way to do the migration, but if you absolutely must move accounts between SQL Server and SQLite, it will save you a lot of time.

Detailed instructions for installing Gallery Server Pro and using the backup and restore functions can be found in the Administrator's Guide, available here.

You can migrate the other direction, too, from SQLite to SQL Server, by modifying the steps accordingly.

Points of Interest

I asked Microsoft if there were any unit tests available to verify that new providers (such as these SQLite ones) behave the same as their SQL Server counterparts. Unfortunately, the answer was no. This makes it hard to guarantee that the SQLite providers are 100% compatible with the SQL Server ones.

But, to the best of my ability, I ensured the behavior is the same. I used Reflector to study the existing SQL Server providers. I looked at the SQL Server Stored Procedures to see what was going on under the hood.

All SQL is parameterized and protected against SQL injection attacks.

History

April 26, 2011 - Added support for binary profile properties. Added property MembershipApplicationName to roles and profile providers. Fixed several bugs.

• SQLiteProfileProvider: (New feature) Added support for binary profile properties
• SQLiteRoleProvider, SQLiteProfileProvider: (New feature) Added property MembershipApplicationName to allow roles and profile provider to use a different application than the membership provider
• Changed data types of NVARCHAR(XX) columns to TEXT
• SQLiteMembershipProvider.CreateUser: Fixed bug where a null e-mail address caused a NullReferenceException
• SQLiteMembershipProvider.UpdateUser: Fixed bug where e-mail address was not correctly updated
• SQLiteMembershipProvider.GetUser(object providerUserKey, bool userIsOnline): Fixed bug where method would not return a user when a valid providerUserKey was specified.
• SQLiteMembershipProvider.Initialize: ApplicationName property now defaults to System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath when it is not specified in config file
• SQLiteMembershipProvider.UnEncodePassword: Fixed bug where an empty password caused a NullReferenceException
• SQLiteRoleProvider.Initialize: Throw ProviderException if an unrecognized attribute is present in configuration section in config file
• Wrap all calls to SQLiteTransaction.Rollback in a try..catch to swallow any SQLiteException instances that are thrown

March 30, 2009 - Fixed a few bugs and corrected behavior that was inconsistent with the SQL Server providers.

• SQLiteMembershipProvider.CreateUser: LastLoginDate and LastPasswordChangedDate now default to the CreateDate instead of 1753-01-01. This is consistent with the SQL Server Membership provider.
• SQLiteMembershipProvider.ChangePassword: It now verifies that the passwords conform to validation requirements during a password change, such as MinRequiredNonAlphanumericCharacters, PasswordStrengthRegularExpression, and MinRequiredPasswordLength.
• SQLiteMembershipProvider.ChangePassword: Fixed bug where the isNewUser parameter of the ValidatePasswordEventArgs constructor was incorrectly set to true. It is now set to false.
• SQLiteMembershipProvider.ChangePasswordQuestionAndAnswer: Fixed bug where SQL parameter names were inconsistent between the SQL and the subsequent code that assigns values to the parameters.
• SQLiteRoleProvider.GetUsersInRole: Fixed bug where method does not return any users when a role name has one or more upper case letters.
• SQLiteRoleProvider.CreateRole: It now throws a ProviderException if a user attempts to create a role with a name that differs only by case with another role. Previously it allowed roles that differed by case (example: SysAdmin and sysadmin are no longer both valid). This is consistent with the SQL Server Role provider.
• The fix for the previous two bugs required adding a new column LoweredRoleName to the aspnet_Roles table. This change caused a ripple effect where most of the SQL statements had to be updated to take into account the new column.
• SQLiteRoleProvider.DeleteRole: Fixed bug where the user/role relationship records were not deleted from the aspnet_UsersInRoles table when a role is deleted.

November 3, 2008 - Added XML comments to code, fixed a few bugs where DataReaders were incorrectly closed, refactored a couple minor constants to variables, and corrected an error in the sample code in this article.

September 9, 2008 - Initial release