Using MySQL Session State Provider for ASP.NET

Content

• Introduction 
• Manually Setup MySQL Session State Provider
• Sample Codes 
• Codes from Official MySQL Dot Net Connector 
• Q&A 

Introduction

What is Session State?  

ASP.NET session state enables you to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications. (Retrieved from ASP.NET Session State Overview) 

By default, ASP.NET Session State is stored in computer's physical memory. If website is accessed by lots of clients at the same time, the physical memory will be completely exhausted and it is definitely insufficient. Disable the usage of Session State can resolve this problem. Another option for resolving this is by storing the Session State's data in database.

This article will be explaining on using MySQL as Session State Storage Provider and how to configure it to work with ASP.NET.

Manually Setup MySQL Session State Provider  

First, we have to create a class and inherit System.Web.SessionState.SessionStateStoreProviderBase, then implements all the abstract and virtual methods.

Example: 

using System;
using System.Web;
using System.Web.Configuration;
using System.Configuration;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System.Web.SessionState;
using System.Data;
using System.Diagnostics;
using System.IO;
using MySql.Data.MySqlClient;

namespace MyWebsite
{
    public class MySessionStateConfig : SessionStateStoreProviderBase
    {
        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);
        }

        public override SessionStateStoreData CreateNewStoreData(HttpContext context, int timeout)
        {
            throw new NotImplementedException();
        }

        public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
        {
            throw new NotImplementedException();
        }

        public override void Dispose()
        {
            throw new NotImplementedException();
        }

        public override void EndRequest(HttpContext context)
        {
            throw new NotImplementedException();
        }

        public override SessionStateStoreData GetItem(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actions)
        {
            throw new NotImplementedException();
        }

        public override SessionStateStoreData GetItemExclusive(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actions)
        {
            throw new NotImplementedException();
        }

        public override void InitializeRequest(HttpContext context)
        {
            throw new NotImplementedException();
        }

        public override void ReleaseItemExclusive(HttpContext context, string id, object lockId)
        {
            throw new NotImplementedException();
        }

        public override void RemoveItem(HttpContext context, string id, object lockId, SessionStateStoreData item)
        {
            throw new NotImplementedException();
        }

        public override void ResetItemTimeout(HttpContext context, string id)
        {
            throw new NotImplementedException();
        }

        public override void SetAndReleaseItemExclusive(HttpContext context, string id, SessionStateStoreData item, object lockId, bool newItem)
        {
            throw new NotImplementedException();
        }

        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)
        {
            throw new NotImplementedException();
        }
    }
}

Then, we need to tell ASP.NET to use our defined engine (the class) to handle Session State. This is done by modifying the Web.config. 

<configuration>
  <system.web>
    <sessionState cookieless="false" regenerateExpiredSessionId="true" mode="Custom" customProvider="MyCustomMadeSessionProvider">
      <providers>
        <add name="MyCustomMadeSessionProvider" 
		type="MyWebsite.MySessionStateConfig" 
		applicationName="/mywebapp" 
		description="my custom state provider" 
		writeExceptionsToEventLog="False"
		autogenerateschema="True"
		enableExpireCallback="False" />
      </providers>
    </sessionState>
  </system.web>
</configuration> 

We direct the Session State Management Engine to our class at this line in Web.config:

type="<namespace>.<class>.<class>...."

which is this (in the above example): 

type="MyWebsite.MySessionStateConfig"

Next, what we need to do is write the logic codes for the Methods in the created class. 

Example:  

This method is called when a new Session State is created: 

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

This is an example of how this method can possible be handled:

public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
{
    try
    {
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = @"INSERT INTO sessions 
                (SessionId, ApplicationName, Created, Expires, LockDate, 
                LockId, Timeout, Locked, SessionItems, Flags)
                Values(@SessionId, @ApplicationName, @Created, @Expires, 
                @LockDate, @LockId, @Timeout, @Locked, @SessionItems, @Flags)";

                cmd.Parameters.Add("@SessionId", MySqlDbType.VarChar, 80).Value = id;
                cmd.Parameters.Add("@ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName;
                cmd.Parameters.Add("@Created", MySqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@Expires", MySqlDbType.DateTime).Value = DateTime.Now.AddMinutes((Double)timeout);
                cmd.Parameters.Add("@LockDate", MySqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@LockId", MySqlDbType.Int32).Value = 0;
                cmd.Parameters.Add("@Timeout", MySqlDbType.Int32).Value = timeout;
                cmd.Parameters.Add("@Locked", MySqlDbType.Int32).Value = 0;
                cmd.Parameters.Add("@SessionItems", MySqlDbType.VarChar, 0).Value = "";
                cmd.Parameters.Add("@Flags", MySqlDbType.Int32).Value = 1;

                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
    catch (MySqlException e)
    {
        if (WriteExceptionsToEventLog)
        {
            WriteToEventLog(e, "CreateUninitializedItem");
            throw new ProviderException(exceptionMessage);
        }
        else
            throw e;
    } 
}

For a full list of class members and methods of SessionStateStoreProviderBase and the descriptions of when the methods are called and what do they do, you may refer:

               >>  MSDN official documentation - SessionStateStoreProviderBase Class  

There is no fixed Database table structure for storing Session State's Data. You can define your own table structure. Below is an example of table structure:

 CREATE TABLE `sessions` (
  `SessionId` varchar(80) NOT NULL,
  `ApplicationName` varchar(255) NOT NULL,
  `Created` datetime NOT NULL,
  `Expires` datetime NOT NULL,
  `LockDate` datetime NOT NULL,
  `LockId` int(10) unsigned NOT NULL,
  `Timeout` int(10) unsigned NOT NULL,
  `Locked` smallint(1) unsigned NOT NULL,
  `SessionItems` longtext NOT NULL,
  `Flags` int(10) unsigned NOT NULL,
  PRIMARY KEY (`SessionId`,`ApplicationName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Sample Codes 

Sample Code of Access Database Session-State Store Provider :

Database, Table, Web.config (Setup): http://msdn.microsoft.com/en-us/library/ms178588(v=vs.100).aspx

SessionStateStoreProviderBase Class: http://msdn.microsoft.com/en-us/library/ms178589(v=vs.100).aspx 

Sample Code of MySQL Session-State Store Provider: 

In 2007, there is a developer named Harry Kimpel, made a class by modifying from the above Access Database codes (from MSDN) to handle the Session States in MySQL. He has written an article and shared it in codeproject.com.

               >> ASP.NET session state store provider for MySQL 

Codes from Official MySQL Dot Net Connector 

Now, the good news, start from MySQL Dot Net Connector Version 6.1 (September 2009, Read More),  MySQL Connector/Net has included/written the class - MySql.Web.SessionState.MySqlSessionStateStore. 

Table structure will be created automatically created too. 

Therefore, we won't have to manually write our own class to handle the Session States in MySQL. What we are going to do now is just configure Web.config  to tell our ASP.NET to handle Session States by using MySql.Web.SessionState.MySqlSessionStateStore. Add a reference of MySql.Web.dll (or MySql.Web from Global Assembly Cache if you have install the connector) into the project.  

Modifying Web.config 

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <remove name="LocalMySqlServer" />
    <add name="LocalMySqlServer" connectionString="server=localhost;user=root;pwd=1234;database=mydatabase;" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
  <system.web>
    <sessionState cookieless="false" 
           regenerateExpiredSessionId="true" 
           mode="Custom" 
           customProvider="MySqlSessionStateProvider">
      <providers>
        <add name="MySqlSessionStateProvider" 
           type="MySql.Web.SessionState.MySqlSessionStateStore, MySql.Web, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" 
           applicationName="/mywebapp" 
           description="MySQL Session State" 
           connectionStringName="LocalMySqlServer" 
           writeExceptionsToEventLog="False" 
           autogenerateschema="True" 
           enableExpireCallback="False" />
      </providers>
    </sessionState>
  </system.web>
</configuration>   

Automatic Web.config Configuration

And yes, another good news, MySQL Connector/NET has provided another tool called MySQL Website Configuration Tool to perform the configuration of Web.config automatically. This tool will be available after integration of MySQL Connector/NET with Visual Studio. 

Download: MySQL Connector/NET.  

If you are unable to see the MySQL Website Configuration Tool's icon in Visual Studio's Solution Explorer, you can try this: How to recover MySQL Website Configuration Tool Icon in VS 2010

Just key in all required information, the tool will modify Web.config and write everything that is needed to tell ASP.NET to handle Session States in MySQL.  

A tutorial of using MySQL Website Configuration Tool: http://dev.mysql.com/doc/refman/5.7/en/connector-net-website-config.html 

By default, MySql.Web.SessionState.MySqlSessionStateStore obtains the connection string from the Web.config. This behaviour can be modified. For some reason, you might not want to store the connection string at Web.config. Create a class (for example: MySession.MyModifiedSessionEngine) inherit MySql.Web.SessionState.MySqlSessionStateStore and override this Initialize method. 

public override void Initialize(string name, NameValueCollection config) 

The default connection string is defined like this in Initialize(): 

.... 
// Initialize connection.
connectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
if (connectionStringSettings == null || connectionStringSettings.ConnectionString.Trim() == "")
    throw new HttpException("Connection string can not be blank");
connectionString = connectionStringSettings.ConnectionString; 
.... 

apply your own way to obtain connection string:

 .... 
// Initialize connection.
connectionString = SomeMethodToGetConnectionString();
....  

modify Web.config to use your new Session State Engine: (the connection string is deleted as it is no more needed)

<?xml version="1.0"?>
<configuration>
  <system.web>
    <sessionState cookieless="false" 
           regenerateExpiredSessionId="true" 
           mode="Custom" 
           customProvider="MySqlSessionStateProvider">
      <providers>
        <add name="MySqlSessionStateProvider" 
           type="MySession.MyModifiedSessionEngine" 
           applicationName="/mywebapp" 
           description="MySQL Session State" 
           connectionStringName="LocalMySqlServer" 
           writeExceptionsToEventLog="False" 
           autogenerateschema="True" 
           enableExpireCallback="False" />
      </providers>
    </sessionState>
  </system.web>
</configuration>   

Q&A   

Can I configure the settings of Session States in Code Behind, not in Web.config? 

Well, to the best of my knowledge (I could be wrong), according to this documentation:  SessionStateSection Class (MSDN), Session State settings can only be changed in Web.config and it is read-only in code behind. 

Can I obtain the source code of MySql.Web.SessionState.MySqlSessionStateStore? 

Yes, it is open source. You may download it from here:  http://dev.mysql.com/downloads/connector/net 

Change the "Platform" to "Source Code". 

End of article. Happy coding.