SQLite Integration with ASP.NET Provider Model

dotConnect for SQLite can be used in the ASP.NET provider model. It allows developers to write better structured applications and easily switch data storage in ASP.NET applications from other media such as Microsoft SQL Server. For detailed information on the basics refer to the MSDN whitepaper: ASP.NET 2.0 Provider Model: Introduction to the Provider Model.

dotConnect for SQLite implements the following providers: Membership, Session State, Role, Profile, Site Map, Web Event, and Personalization. This feature is available in Professional Edition only. This topic provides information on how to set up ASP.NET application to use dotConnect for SQLite as one of these providers.

Installation and Setup

All providers are contained in the Devart.Data.SQLite.Web.dll assembly. You should add a reference to this assembly in your project in order to use the ASP.NET provider model. Another condition for the providers to function properly is the existence of certain database objects. Before using ASP.NET providers, you should run the InstallWebTables.sql script against the database you wish to use. The script can be found in the \Program Files\Devart\dotConnect\SQLite\ folder.

During the installation process dotConnect for SQLite adds new entries into the machine.config file. They are used by web sites when the settings are not overridden with a local Web.config file. You can choose to keep the settings in machine.config, in which case they will be used for all web sites, or in Web.config files, where you can set up specific parameters. Note that the default connection string in the machine.config file is incorrect, so you will want to adjust or override it.

When you redefine an identifier in the Web.config file, you have to add the remove keyword in the appropriate section, as shown below:

  <remove name="SQLiteServices" />
  <add name="SQLiteServices" connectionString="DataSource=mydatabase.db" />

This example demonstrates how to adjust connection string parameters. By default dotConnect for SQLite creates a stub connection string with the name SQLiteServices.

You can also configure your website with the standard Control Panel applet "ASP.NET Configuration Settings". To open it, navigate to Control Panel | Administrative Tools | Internet Information Services, then select a site or a virtual directory, open its properties, switch to ASP.NET, and click Edit Configuration. This dialog provides control over various site settings, including the connection string.

ASP.NET Membership Provider

The fundamental job of a membership provider is to interface with data sources containing data regarding a site's registered users, and to provide methods for creating users, deleting users, verifying login credentials, changing passwords, and so on. The .NET Framework's System.Web.Security namespace includes a class named MembershipUser that defines the basic attributes of a membership user and that a membership provider uses to represent individual users.

To access this functionality, use Devart.Data.SQLite.Web.Providers.SQLiteMembershipProvider class. It behaves as the System.Web.Security.RoleProvider class (you can read its description in MSDN). The following example shows the Web.config file for an ASP.NET application configured to use SQLiteMembershipProvider.

<configuration>
  <connectionStrings>
    <add name="SQLiteServices"
         connectionString="DataSource=mydatabase.db" />
  </connectionStrings>
  <system.web>
    ...
    <membership defaultProvider="AspNetSQLiteMembershipProvider"
      userIsOnlineTimeWindow="15">
      <providers>
        <add
          name="AspNetSQLiteMembershipProvider"
          type="Devart.Data.SQLite.Web.Providers.SQLiteMembershipProvider,
                Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral, 
                PublicKeyToken=09af7300eec23701"
          connectionStringName="SQLiteServices"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          requiresQuestionAndAnswer="true"
          requiresUniqueEmail="false"
          passwordFormat="Hashed"
          maxInvalidPasswordAttempts="5"
          passwordAttemptWindow="10" />
      </providers>
    </membership>
  </system.web>
</configuration>

Note: replace "4.0.6.0" with the actual provider version.

ASP.NET Role Provider

The fundamental job of a role provider is to interface with data sources containing role data mapping users to roles, and to provide methods for creating roles, deleting roles, adding users to roles, and so on. Given a user name, the role manager relies on the role provider to determine what role or roles the user belongs to. The role manager also implements administrative methods such as Roles.CreateRole and Roles.AddUserToRole by calling the underlying methods in the provider.

To access this functionality use Devart.Data.SQLite.Web.Providers.SQLiteRoleProvider class. It behaves as the System.Web.Security.RoleProvider class (you can read its description in MSDN). The following example shows the Web.config file for an ASP.NET application configured to use SQLiteRoleProvider. Note that you can configure the SQLiteRoleProvider to use the same database and user information as the SQLiteMembershipProvider in order to use a single database for authentication and authorization information.

<configuration>
  <connectionStrings>
    <add name="SQLiteServices"
         connectionString="DataSource=mydatabase.db" />
  </connectionStrings>
  <system.web>
    ...
    <roleManager defaultProvider="AspNetSQLiteRoleProvider"
      enabled="true"
      cacheRolesInCookie="true"
      cookieName=".ASPROLES"
      cookieTimeout="30"
      cookiePath="/"
      cookieProtection="All" >
      <providers>
        <add
          name="AspNetSQLiteRoleProvider"
          type="Devart.Data.SQLite.Web.Providers.SQLiteMembershipProvider,
                Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral, 
                PublicKeyToken=09af7300eec23701"
          connectionStringName="SQLiteServices" />
      </providers>
    </roleManager>
  </system.web>
</configuration>

Note: replace "4.0.6.0" with the actual provider version.

SimpleMembership

ASP.NET 4.5 and ASP.NET MVC 4 introduce new membership API - SimpleMembership, that is designed to improve and simplify the use of the built in security in ASP.NET. These SimpleMembership API are implemented as SimpleRoleProvider and SimpleMembershipProvider providers. As their predecessors, they are intended to work with data sources containing data regarding a site's registered users and with data sources containing role data mapping users to roles respectively.

To support this functionality, dotConnect for SQLite implements SQLiteExtendedMembershipProvider and SQLiteExtendedRoleProvider classes, which behave as the above-mentioned standard provider classes. You can read our Using SimpleMembership Provider in ASP.NET MVC 4 Application tutorial to see how to use the dotConnect for SQLite SimpleMembership providers' implementation.

ASP.NET Profile Provider

The fundamental job of a profile provider is to write profile property values supplied by ASP.NET to persistent profile data sources, and to read the property values back from the data source when requested by ASP.NET.

Unlike the Session State provider, the Profile provider is a typed structured data storage. It supports both registered and anonymous users. Profile providers also implement methods that allow consumers to manage profile data sources - for example, to delete profiles that haven't been accessed since a specified date.

The user profile is accessed using the Profile property of the current HttpContext object. The following example shows the Web.config file for an ASP.NET application configured to use SQLiteProfileProvider.

<configuration>
  <connectionStrings>
    <add name="SQLiteServices"
         connectionString="DataSource=mydatabase.db" />
  </connectionStrings>
  <system.web>
    ...
    <profile defaultProvider="AspNetSQLiteProfileProvider" >
      <providers>
        <add
          name="AspNetSQLiteProfileProvider"
          type="Devart.Data.SQLite.Web.Providers.SQLiteMembershipProvider,
                Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral, 
                PublicKeyToken=09af7300eec23701"
          connectionStringName="SQLiteServices" />
      </providers>
      <properties>
        <add name="ZipCode" />
        <add name="CityAndState" />
      </properties>
    </profile>
  </system.web>
</configuration>

Note: replace "4.0.6.0" with the actual provider version.

Here ZipCode and CityAndState are examples of profile elements. For detailed information on how to construct the properties section refer to MSDN.

ASP.NET Session State Provider

The session-state store provider is called by the SessionStateModule class during the processing of an ASP.NET page to communicate with the data store for the storage and retrieval of session variables and related session information such as the time-out value. Session data within each ASP.NET application is stored separately for each SessionID property. ASP.NET applications do not share session data.

For information on how to employ this functionality refer to the ASP.NET Session State topic in MSDN. The following example shows the Web.config file for an ASP.NET application configured to use SQLiteSessionStateStore.

<configuration>
  <appSettings/>
  <connectionStrings>
    <add name="SQLiteServices"
         connectionString="DataSource=mydatabase.db"/>
  </connectionStrings>
  <system.web>
    <sessionState
      cookieless="true"
      regenerateExpiredSessionId="true"
      mode="Custom"
      customProvider="SQLiteSessionProvider">
      <providers>
        <add name="SQLiteSessionProvider"
          type="Devart.Data.SQLite.Web.Providers.SQLiteMembershipProvider,
                Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral, 
                PublicKeyToken=09af7300eec23701"
          connectionStringName="SQLiteSessionServices"
          writeExceptionsToEventLog="false"/>
      </providers>
    </sessionState>
  </system.web>
</configuration>

Note: replace "4.0.6.0" with the actual provider version.

ASP.NET Site Map Provider

The ASP.NET Site Map provider provides the interface between ASP.NET's data-driven site-navigation features and site map data sources. The fundamental job of OracleSiteMapProvider is to read site map data from a data source and build an upside-down tree of SiteMapNode objects, and to provide methods for retrieving nodes from the site map. Each node in the tree represents one node in the site map. Node properties such as Title, Url, and ChildNodes define the characteristics of each node and allow the tree to be navigated in any direction.

To access this functionality, use the Devart.Data.SQLite.Web.Providers.SQLiteSiteMapProvider class. It behaves as the System.Web.SiteMapProvider class (you can read its description in MSDN). The following example shows the Web.config file for an ASP.NET application configured to use SQLiteSiteMaProvider.

<system.web>
  <siteMap defaultProvider="SQLiteSiteMapProvider">
    <providers>
      <add name="SQLiteSiteMapProvider"
           type="Devart.Data.SQLite.Web.Providers.SQLiteSiteMapProvider,
                 Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral,
                 PublicKeyToken=09AF7300EEC23701"
           connectionStringName="ConnectionString"
           securityTrimmingEnabled="true"
           />
    </providers>
  </siteMap>
</system.web>

Note: replace "4.0.6.0" with the actual provider version.

ASP.NET Web Event Provider

The Web Event provider provides the interface between ASP.NET's health monitoring subsystem and data sources that log or further process the events ("Web events") fired by that subsystem.

To access this functionality use Devart.Data.SQLite.Web.Providers.SQLiteWebEventProvider class. It behaves as the System.Web.Management.WebEventProvider class (you can read its description in MSDN). The following example shows the Web.config file for an ASP.NET application configured to use SQLiteWebEventProvider. Note that proper use of the provider also requires many other subsections of healthMonitoring to be correctly configured.

<system.web>
  <healthMonitoring enabled="true" heartbeatInterval="0">
    ...
    <providers>
      <clear/>
      <add name="SQLiteWebEventProvider"
           type="Devart.Data.SQLite.Web.Providers.SQLiteWebEventProvider,
                 Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral,
                 PublicKeyToken=09AF7300EEC23701"
           connectionStringName="ConnectionString"
           maxEventDetailsLength="1073741823"
           buffer="false"
           bufferMode="Notification" />
    </providers>
    ...
  </healthMonitoring>
<system.web>

Note: replace "7.0.6.0" with the actual provider version.

Personalization Provider

The fundamental job of a personalization provider is to provide persistent storage for personalization state regarding the content and layout of web pages.

To access this functionality, use the Devart.Data.SQLite.Web.Providers.SQLitePersonalizationProvider class. It behaves as the System.Web.UI.WebControls.WebParts.PersonalizationProvider class (you can read its description in MSDN). The following example shows the Web.config file for an ASP.NET application configured to use SQLitePersonalizationProvider.

<system.web>
 <webParts>
   <personalization defaultProvider="WebPartProvider">
     <providers >
       <add name="WebPartProvider"
           connectionStringName="ConnectionString"
           type="Devart.Data.SQLite.Web.Providers.SQLitePersonalizationProvider,
                 Devart.Data.SQLite.Web, Version=4.0.6.0, Culture=neutral,
                 PublicKeyToken=09af7300eec23701" />
     </providers>
   </personalization>
 </webParts>
<system.web>

Note: replace "4.0.6.0" with the actual provider version.

Deployment

To deploy ASP.NET applications written with dotConnect for SQLite you should register the run-time assemblies Devart.Data.dll, Devart.Data.SQLite.dll, and Devart.Data.SQLite.Web.dll at Global Assembly Cache (GAC) for appropriate framework or place them in the folder of your application (the Bin folder for web projects). The engine library SQLite3.dll also should be available for the application.

Additionally, place the App_Licenses.dll assembly in the Bin folder. For more information about this assembly, refer to the Licensing topic in dotConnect for SQLite documentation.

Keep in mind that web application are usually run in a partially trusted environment.

Back to list