ASP.NET Custom Session Store Provider Compatible with Oracle, SQL

• Download source code - 8.7 KB

Introduction

This article aims to provide help in allowing an Oracle database to be used as Session Store for ASP.NET sessions. The article might also be considered as an introduction for building your own Custom Session Store Provider code instead of using the default SessionStore.

Background

Microsoft ASP.NET framework ships with necessary code blocks (inbuilt into the .NET framework itself actually) and SQL scripts (which if you are using SQL server to session store for the first time, you will need to install on a Microsoft SQL Server from the command line tool aspnet_regsql.exe) to jump start using sessions in your ASP.NET applications in all modes that are available to be configured.

Problems with ASP.NET's Default Approach to a Database-Based Session Store Management

1. Default session store provider (out of .NET and ASP.NET framework) supports only Microsoft SQL Server - obviously because Microsoft can't directly write code to support competitor databases.
2. The default session store - has a huge footprint on SQL Server, with many tables going in... and if you are trying to use a new Microsoft SQL Server database which has never been used as an ASP.NET session store, then you will probably run the aspnet_regsql.exe command line EXE to prepare your database on the server.

Now this Second Point has More Sub-Issues

1. All web applications use the common database on SQL Server for sessions. Absence of default isolation might make it look like all applications have the same requirements for session management or user rights management though some parameters like timeout are configurable at web.config level on each application.
2. Backing up session data, or customizing the session store for implementing features like use of session store to record user IP... user activity, etc., is complex as you have work with modifying Microsoft's table design for sessions which could affect many applications (because of lack of isolation) as compared to using sessions from your own tables and improving your session table design.

The Good News

Though all the above issues kind of limit the power behind session management in ASP.NET, Microsoft as always helps you here without this help we would have had to do session handling entirely ourselves instead of easily using sessions as with below syntax:

// Writing to session
Session["userLoggedIn"] = "some username"; 
Session["userLastLogInDT"] = "2010-08-16 10:00:00";

//Reading from session.
Your last login was at: <%= Response.Write (Session["userLastLogInDT"]); %>

How Do We Start a Custom Provider

Just by deriving from SessionStateStoreProviderBase class from System.Web.SessionState namespace. The abstract class when derived, will expect that the deriving class implement all of its definitions... like below:

public override void Initialize(string name, NameValueCollection config)

public override void Dispose()

public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)

public override void SetAndReleaseItemExclusive(HttpContext context,
          string id,
          SessionStateStoreData item,
          object lockId,
          bool newItem)

public override SessionStateStoreData GetItem(HttpContext context,
          string id,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)

public override SessionStateStoreData GetItemExclusive(HttpContext context,
          string id,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)

private SessionStateStoreData GetSessionStoreItem(bool lockRecord,
          HttpContext context,
          string id,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)

private string Serialize(SessionStateItemCollection items)

private SessionStateStoreData Deserialize(HttpContext context,
          string serializedItems, int timeout)

public override void ReleaseItemExclusive(HttpContext context,
          string id,
          object lockId)

public override void RemoveItem(HttpContext context,
          string id,
          object lockId,
          SessionStateStoreData item)

public override void CreateUninitializedItem(HttpContext context,
          string id,
          int timeout)

public override SessionStateStoreData CreateNewStoreData(
          HttpContext context,
          int timeout)

public override void ResetItemTimeout(HttpContext context,
                                              string id)

public override void InitializeRequest(HttpContext context)

public override void EndRequest(HttpContext context)

There is extensive reading material on MSDN, and elsewhere on what is expected to be the implementation for each of these mandatory methods.

Code Credits

Most part of the implementation in the attached code here (CustomSessionStoreProvider.cs) is derived from sources of another, similar, CodeProject article by Rob Smith MN.

The difference in this article's code is that I have added a DataAccess class (SessDataAccess.cs) to isolate dataaccess methods in such a manner that it is possible to use the code for both Oracle and MS SQL server with only config changes.

Also, because of the isolation of DataAccess methods, it is easily possible to extend this code for DB2, MySQL and many other database engines if need be.

Yes, this article's code may still be considered an extender of RobSmith's code. Thanks to RobSmith.

About the Attached Code

A considerably professional-use sample, with source code and test pages, is attached with this article. I believe the attached code could give you a 'basics-done' implementation for use, and also a good head start for extending it with your own features or extending it to more database engines.

The code attached has two important files, which I will explain here a bit so it helps you modify them if you need to add something.

CustomSessionStoreProvider.cs

Derives the session store provider base class.

Has hard-coded SQL Query statements for select, update, delete, and insert actions on the database. The attached code doesn't use Stored procedures to give a bare-minimum footprint database code so queries are directly added here. You could replace them with SP calls or move them to a constants.cs file.

SessDataAccess.cs

Has the data access code with switches to shift between an Oracle or an MS SQL server session store as may be appropriately configured in a web.config file. Yes the web.config file has two sample keys respectively for Oracle and SQL server... you could comment one of them as required.

Database Scripts for Oracle and MS SQL Server

The attached code expects only a Table called 'Sessions' to exist in the database. No SPs, Sequences, anything. The create table statements for Oracle and MSSQL server are also provided in separate .sql files.

Using the Attached Code

Create the SESSIONS Table

You can choose to keep the session data for your application in your application's database or in a separate database. Create the database and then run the CREATE TABLE script attached for your particular db Oracle / Microsoft SQL server.

By default, only the primary key, the session ID varchar field of the table will be indexed, which is enough for a medium use application if you need to, you may add further indexes for the expiry datetime field and applicationname fields because they are most used in the 'where clauses' in queries.

Copy the Class files to App_code

Copy the files CustomSessionStoreProvider.cs, and SessDataAccess.cs, into your application's app_code folder.

Configure keys in Web.config

Configure your web application's web.config to have two additional keys referenced in:

<appSettings>
    <add key="DBConnectionstring" value="DB_CONNECTION_STRING"/>
    <add key="DBProvider" value="Oracle"/>
</appSettings> 

The keys are read only from the CustomSessionStoreProvider.cs file, and the values/settings are updated to SessDataAccess.cs public static variables.

Change the DBProvider key to either "Oracle" or "sqlClient" based on whether you use Oracle server or MS Sql Server.

Configuring the Session

Add the SessionState configuration block to provide values for properties, for session management.

<sessionState mode="Custom" customProvider="custom_provider" 
           cookieless="false" timeout="1"><!-- timeout in minutes, 1 min set for testing-->
      <providers>
        <add name="custom_provider" type="Test.WebSession.CustomSessionStoreProvider"/>
      </providers>
</sessionState>

Notice the "customProvider" attribute value in sessionState config, and appropriate new provider addition through <provider> config item, which should tell the IIS web server about the custom provide for session... correctly pointing to our class at Test.WebSession.CustomSessionStoreProvider (if you change the Test.WebSession namespace in code, you should change it here too).

Web.config Dependencies in Attached Code

Requires OracleClient .NET Framework assembly reference... because its referenced in SessDataAccess.cs.

<compilation debug="true">
  <assemblies>
     <add assembly="System.Data.OracleClient, Version=2.0.0.0, 
           Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  </assemblies>
</compilation>  

You are done. Use Sessions with session variables.

// Writing to session
Session["userLoggedIn"] = "some username"; 
Session["userLastLogInDT"] = "2010-08-16 10:00:00";

//Reading from session.
Your last login was at: <%= Response.Write (Session["userLastLogInDT"]); %> 

Performance Tested

The sources have been tested by me on different machines, with both Oracle 10g, and MSQLserver 2005 and above (32 & 64 bit). I have used the attached code in production environment of a few medium-sized websites with close to 1000 users/sec hitrate.

Improvements Possible

1. Can be extended easily to use DB2 / MySQL or any database engine by adding code to SessDataAccess.cs
2. Can be improved with more indexes on sessions table, queries can be ported to StoredProcedures for further performance improvements which will be notable on high-hitrate applications
3. A mirror database can be setup for being a redundant session store
4. An archiver application can backup the data on sessions table alone for session-based reporting