Deserialization of Session variables from ASPStateTempSessions database table

Question

we have recently moved to session state 'SQL Server' in our Asp.net website.

I can see sessions data with SessionItemShort as NULL and SessionItemLong column having data present in binary format as below.

As per research, SessionItemLong data is stored in a collection of 8-KB pages and not in Image datatype.

I tried converting into Image but it didn't work.

I would need to see what kind of details SessionItemLong is holding.

Is there any way to deserialize data present in SessionItemLong column from ASPStateTempSessions table.

Answer 1

Anyone struggling with Deserialization issue:

1. Create Entity Model class mapped with ASPStateTempSessions table.
2. Use below code:

public object GetSession(SSOSessionEntity sentity)
{
    object obj = null;
    
    if(sentity.SessionItemLong is null)
    {
        return obj;
    }
    
    System.IO.MemoryStream stream = new System.IO.MemoryStream();
    System.IO.BinaryReader reader = new System.IO.BinaryReader(stream);
    stream.SetLength(0);
    stream.Write(sentity.SessionItemLong, 0, sentity.SessionItemLong.Length);
    stream.Seek(0, System.IO.SeekOrigin.Begin);
    reader.ReadInt32();
    bool bol_flag = reader.ReadBoolean();
    reader.ReadBoolean();
    if(bol_flag)
    {
        SessionStateItemCollection sessionItems = System.Web.SessionState.SessionStateItemCollection.Deserialize(reader);
        foreach(string key in sessionItems.Keys) // All Session in the current sessionid
        {
            obj = sessionItems[key];
            double totalSessionBytes = 0;
            BinaryFormatter b = new BinaryFormatter();
            MemoryStream m;
            var sessionKeyObj = sessionItems[key];
            if(sessionKeyObj != null)
            {
                m = new MemoryStream();
                b.Serialize(m, obj);
                totalSessionBytes += m.Length;
            }
            totalSessionBytes = totalSessionBytes / 1024;
        }
    }
    
    return obj;    
}

SSOSessionEntity is nothing but data received from select * from dbo.ASPStateTempSessions where sessionid=@sessionid

public SSOSessionEntity GetSessionRow(string connectionString, string sessionId)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * FROM dbo.ASPStateTempSessions WHERE SessionId = @SessionId", con);
        cmd.Parameters.AddWithValue("@SessionId", sessionId);
        SqlDataReader rdr = cmd.ExecuteReader();
        
        DataTable dataTable = new DataTable();

        if (!rdr.HasRows)
        {
            return null;
        }
        
        dataTable.Load(rdr);        
        var row = dataTable.Rows[0];

        return new SSOSessionEntity
        {
            SessionId = (string)row["SessionId"],
            Created = (DateTime)row["Created"],
            Expires = (DateTime)row["Expires"],
            LockDate = (DateTime)row["LockDate"],
            LockDateLocal = (DateTime)row["LockDateLocal"],
            LockCookie = (int)row["LockCookie"],
            Timeout = (int)row["Timeout"],
            Locked = (bool)row["Locked"],
            SessionItemShort = row.IsNull("SessionItemShort") ? null : (byte[])row["SessionItemShort"],
            SessionItemLong = row.IsNull("SessionItemLong") ? null : (byte[])row["SessionItemLong"],
            Flags = (int)row["Flags"],
        };
    }    
}

public class SSOSessionEntity 
{
    public string SessionId { get; set; }
    
    public DateTime Created { get; set; }
    
    public DateTime Expires { get; set; }
    
    public DateTime LockDate { get; set; }
    
    public DateTime LockDateLocal { get; set; }
    
    public int LockCookie { get; set; }
    
    public int Timeout { get; set; }
    
    public bool Locked { get; set; }
    
    public byte[] SessionItemShort { get; set; }
    
    public byte[] SessionItemLong { get; set; }
    
    public int Flags { get; set; }
}

Note: This works for data stored in SessionItemLong column.
same can be used for SessionItemShort column as well.