SimpleMembership Providers for SQLite in ASP.NET MVC 4 Tutorial

SimpleMembership and SimpleRole Providers for SQLite in ASP.NET MVC 4 Application Tutorial

dotConnect for SQLite implements SimpleMembership functionality as custom SQLiteExtendedMembershipProvider and SQLiteExtendedRoleProvider classes. This tutorial describes how to use the new ASP.NET MVC membership provider functionality in ASP.NET MVC 4 application (using the Internet Application template).

To complete this tutorial you need ASP.NET MVC 4 installed. It is preinstalled with Visual Studio 2012 and later. For Visual Studio 2010 you can download it from here.

In order to create an ASP.NET MVC 4 application using our SQLiteExtendedMembershipProvider, perform the following steps:

Create an ASP.NET MVC 4 Web Application in Visual Studio. To do it, select New -> Project from the File menu, click Web under Installed Templates, and select ASP.NET MVC 4 Web Application in the list of available templates.

Then select the Internet Application template in the opened New ASP.NET MVC4 Project dialog box.
Add necessary references to our assemblies to the project. You need to add references to the Devart.Data, Devart.Data.SQLite, Devart.Data.SQLite.Web assemblies. Note that you should add the Devart.Data.SQLite.Web assembly by selecting it as a file (not from the extension list) from the NET4 subdirectory of the dotConnect for SQLite installation directory (by default, from Program Files\Devart\dotConnect\SQLite\NET4), not the one from the Extensions list. The Devart.Data.SQLite.Web assembly that is in Extensions list is for .NET Framework 2.0, and we need the one for .NET Framework 4. The required assembly has the revision number 4 in its version.
In the Web.config file replace the DefaultConnection connection string with the actual connection string to the app database. Replace the providerName value with "Devart.Data.SQLite". For example, your connection string may look like the following:
```
<connectionStrings>
	<add name="DefaultConnection" connectionString="DataSource=mydatabase.db" 
	  providerName="Devart.Data.SQLite" />
  </connectionStrings>
```

In the Web.config file remove the following tag:

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />

Add the following lines to the Web.config file:

  <membership defaultProvider="SQLiteExtendedMembershipProvider">
    <providers>
      <remove name="SQLiteExtendedMembershipProvider"/>
      <add name="SQLiteExtendedMembershipProvider" type="Devart.Data.SQLite.Web.Providers.SQLiteExtendedMembershipProvider, 
        Devart.Data.SQLite.Web, Version=5.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
        description="dotConnect for SQLite extended membership provider" connectionStringName="DefaultConnection" />
    </providers>
  </membership>
  <roleManager enabled="true" defaultProvider="SQLiteExtendedRoleProvider">
    <providers>
      <remove name="SQLiteExtendedRoleProvider"/>
      <add name="SQLiteExtendedRoleProvider" type="Devart.Data.SQLite.Web.Providers.SQLiteExtendedRoleProvider, 
          Devart.Data.SQLite.Web, Version=5.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
          description="dotConnect for SQLite extended role provider" connectionStringName="DefaultConnection" />
    </providers>
  </roleManager>

Note: Replace 5.1.26.4 in the code with your actual version.

In the InitializeSimpleMembershipAttribute.cs or InitializeSimpleMembershipAttribute.vb file (that is located in the Filters project folder), replace the namespace "WebMatrix.WebData" with the "Devart.Common.Web".
Now you can run the application and check whether it works by registering a user.

As you can see, the user is registered and logged in.

dotConnect for SQLite ASP.NET Membership Database

dotConnect for SQLite uses the following ASP.NET membership database for its SimpleMembership provider:

By default, the application automatically verifies if the tables exist by the specified connection string. The UserProfile table is automatically created by Entity Framework runtime if it is not already present in the database. Other tables are created by the WebSecurity.InitializeDatabaseConnection method, defined in the Devart.Common.Web namespace. If these tables are already created, no actions are performed.

However you can create these tables manually. Execute the DDL script %Install_Directory%\Devart\dotConnect\SQLite\NET4\InstallExtendedWebTables.sql (for example C:\Program Files\Devart\dotConnect\SQLite\NET4\InstallExtendedWebTables.sql) to create the database tables to store the user and role information.

If you are sure that the necessary tables are always present in the specified database and want to disable this automatic verification, you may re-register our provider in your Web.config file with the following settings:

  <membership defaultProvider="SQLiteExtendedMembershipProvider">
    <providers>
      <remove name="SQLiteExtendedMembershipProvider"/>
      <add name="SQLiteExtendedMembershipProvider" type="Devart.Data.SQLite.Web.Providers.SQLiteExtendedMembershipProvider, 
        Devart.Data.SQLite.Web, Version=5.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
        description="dotConnect for SQLite extended membership provider" connectionStringName="DefaultConnection" 
        useridcolumn="UserId" usernamecolumn="UserName" usertablename="UserProfile" />
    </providers>
  </membership>
  <roleManager enabled="true" defaultProvider="SQLiteExtendedRoleProvider">
    <providers>
      <remove name="SQLiteExtendedRoleProvider"/>
      <add name="SQLiteExtendedRoleProvider" type="Devart.Data.SQLite.Web.Providers.SQLiteExtendedRoleProvider, 
        Devart.Data.SQLite.Web, Version=5.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
        description="dotConnect for SQLite extended role provider" connectionStringName="DefaultConnection" 
        useridcolumn="UserId" usernamecolumn="UserName" usertablename="UserProfile" />
    </providers>
  </roleManager>

Note: Replace 5.1.26.4 in the code with your actual version.

Note: In case dotConnect for SQLite is installed on the target computer with the ASP.NET providers option, you may use shortened registration of our provider in the Web.config file as necessary records are added to machine.config. Add the following membership and roleManager elements to the system.web tag of your Web.config file:

<configuration>
  ...
  <system.web>
    ...
    <membership defaultProvider="SQLiteExtendedMembershipProvider"/>
    <roleManager enabled="true" defaultProvider="SQLiteExtendedRoleProvider"/>
  </system.web>
  ...
</configuration>