Web Sessions Stored in SQL

Summary

If you have ever worked with sessions in a web application using multiple languages you’ll know that you need to do some special configuration to get the session data to be shared across all languages. Another issue involves the problem of cycling or resetting an IIS server while using the default session settings. Basically, your sessions for everyone using that IIS server will get dumped. One way around this problem is to use SQL Server to store the session data. Unfortunately, Classic ASP cannot save sessions to a SQL server. In this post I’m going to describe how the raw session data is stored and how you can serialize and deserialize this data easily.

The Session Data

In an earlier post of mine, I talked about storing session data in MS SQL Server. The data is stored in the tempdb table called ASPStateTempSessions. The actual session data is stored in either the SessionItemShort or the SessionItemLong field depending on if it is greater or less than 7k in size. If you setup a C# program to use SQL Server sessions you can look at this data and you’ll see something like this:

0x14000000010001000000FFFFFFFF047661723112000000011054686973206973207468652064617461FF

This data represents a session with one item in the list:

Session["var1"] = "This is the data";

There is an object that behaves a lot like the Dictionary object and is available for .Net. That object is called SessionStateItemCollection. It’s part of System.Web.SessionState. This object contains a serialize and a desearialize method to convert the data into binary data. Here’s the code that can be used to serialize the same session data as above (assuming you’re using a console application):

SessionStateItemCollection Session2 = new SessionStateItemCollection();
Session2["var1"] = "This is the data";
Byte[] state = null;
using (MemoryStream ms = new MemoryStream())
{
    using (var bw = new BinaryWriter(ms))
    {
        Session2.Serialize(bw);

        ms.Flush();
        state = ms.ToArray();
    }
}

Console.WriteLine(BitConverter.ToString(state).Replace("-", ""));

Now look at the output that this produces:

01000000FFFFFFFF047661723112000000011054686973206973207468652064617461

Look similar? That’s because it’s the same data, minus the first part and the last part. You can ignore the “0x”. That’s SQL’s prefix to signify hex data. The prefix part:

140000000100

Represents several variables such as the session timeout (hex 14 is equal to 20 minutes). There is also an extra hex “FF” at the end of the whole string. To make your output look identical you can change your code to look something like this:

SessionStateItemCollection Session2 = new SessionStateItemCollection();
Session2["var1"] = "This is the data";
Byte[] state = null;
using (MemoryStream ms = new MemoryStream())
{
    using (var bw = new BinaryWriter(ms))
    {
        bw.Write((int)20); // session timeout time
        bw.Write((bool)(Session2.Count > 0)); // state 
        bw.Write((bool)false); // (ignored)

        Session2.Serialize(bw);

        bw.Write((byte)0xff); 

        ms.Flush();
        state = ms.ToArray();
    }
}

Console.WriteLine(BitConverter.ToString(state).Replace("-", ""));

Now run your program and see what is outputted. It’s identical.

When you deserialize, you’ll have to remember to remove the first int, boolean and boolean. The last byte can be ignored, the desearlize method ignores it:

using (MemoryStream ms = new MemoryStream(state))
{
    using (BinaryReader br = new BinaryReader(ms))
    {
        br.ReadInt32();
        bool sessionDataExists = br.ReadBoolean();
        br.ReadBoolean();

        if (sessionDataExists)
        {
            Session2 = SessionStateItemCollection.Deserialize(br);
        }
        else
        {
            Session2 = new SessionStateItemCollection();
        }
    }
}

Take note that the Desearialize() method is a static method, unlike the Serialize() method.

From this information and the information in the previous post on creating a COM module, you should have enough pieces to put together a session object that can be used in Classic ASP. Then you can store your session variables in the same exact storage place that C# and VB.Net use.

Leave a Reply