Custom Sybase Database to persist Session state

In this blog I would like to share the SybaseSessionState I have implemented. Thanks to this article http://www.codeproject.com/KB/aspnet/ASPNET_session_in_MySql.aspx in CodeProject. I only did a slight changes to user Sybase as the data store.

 In your Web.config, you needed to use the following configuration.

    <sessionState cookieless="false" regenerateExpiredSessionId="true" mode="Custom" customProvider="SybaseSessionProvider" >
      <providers>
        <add name="SybaseSessionProvider"
             type="SybaseSessionStateStore"
             connectionStringName="YourConnectionStringName"
             writeExceptionsToEventLog="false" />
      </providers>
    </sessionState>

 Please see the code

    /// <summary>
    /// <remarks>
    /// The following table has to be created
    ///     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          NOT NULL,
    ///         Timeout         int          NOT NULL,
    ///         Locked          bit          NOT NULL,
    ///         SessionItems    text,
    ///         Flags           int          NOT NULL,
    ///         CONSTRAINT PKSessions PRIMARY KEY (SessionId, ApplicationName)
    ///      )
    /// </remarks>
    /// </summary>
    public sealed class SybaseSessionStateStore : SessionStateStoreProviderBase
    {
        private SessionStateSection pConfig = null;
        private string connectionString;
        private ConnectionStringSettings pConnectionStringSettings;
        private string eventSource = "SybaseSessionStateStore";
        private string eventLog = "Application";
        private string exceptionMessage = "An exception occurred. Please contact your administrator.";
        private string pApplicationName;

        private bool pWriteExceptionsToEventLog = false;
        /// <summary>
        /// If false, exceptions are thrown to the caller. If true, exceptions are written to the event log.
        /// </summary>
        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }

        /// <summary>
        /// The ApplicationName property is used to differentiate sessions in the data source by application.
        /// </summary>
        public string ApplicationName
        {
            get { return pApplicationName; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="config"></param>
        public override void Initialize(string name, NameValueCollection config)
        {
            // Initialize values from web.config.
            if (config == null)
                throw new ArgumentNullException("config");

            if (name == null || name.Length == 0)
                name = "SybaseSessionStateStore";

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Sybase Session State Store provider");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);

            // Initialize the ApplicationName property.
            pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;

            // Get <sessionState> configuration element.
            System.Configuration.Configuration cfg = WebConfigurationManager.OpenWebConfiguration(ApplicationName);
            pConfig = (SessionStateSection)cfg.GetSection("system.web/sessionState");

            // Initialize connection string.
            pConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (pConnectionStringSettings == null || pConnectionStringSettings.ConnectionString.Trim() == "")
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = pConnectionStringSettings.ConnectionString;

            // Initialize WriteExceptionsToEventLog
            pWriteExceptionsToEventLog = false;

            if (config["writeExceptionsToEventLog"] != null)
            {
                if (config["writeExceptionsToEventLog"].ToUpper() == "TRUE")
                    pWriteExceptionsToEventLog = true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Dispose()
        {

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="expireCallback"></param>
        /// <returns></returns>
        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)
        {
            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="item"></param>
        /// <param name="lockId"></param>
        /// <param name="newItem"></param>
        public override void SetAndReleaseItemExclusive(HttpContext context, string id, SessionStateStoreData item, object lockId, bool newItem)
        {
            // Serialize the SessionStateItemCollection as a string.
            string sessItems = Serialize((SessionStateItemCollection)item.Items);

            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd;
            AseCommand deleteCmd = null;

            if (newItem)
            {
                // AseCommand to clear an existing expired session if it exists.
                deleteCmd = new AseCommand("DELETE FROM sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND Expires < @Expires", conn);
                deleteCmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                deleteCmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
                deleteCmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now;

                // AseCommand to insert the new session item.
                cmd = new AseCommand("INSERT INTO sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values(@SessionId, @ApplicationName, @Created, @Expires, @LockDate, @LockId, @Timeout, @Locked, @SessionItems, @Flags)", conn);
                cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
                cmd.Parameters.Add("@Created", AseDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now.AddMinutes((Double)item.Timeout);
                cmd.Parameters.Add("@LockDate", AseDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = 0;
                cmd.Parameters.Add("@Timeout", AseDbType.Integer).Value = item.Timeout;
                cmd.Parameters.Add("@Locked", AseDbType.Integer).Value = 0;
                cmd.Parameters.Add("@SessionItems", AseDbType.Text).Value = sessItems;
                cmd.Parameters.Add("@Flags", AseDbType.Integer).Value = 0;
            }
            else
            {
                // AseCommand to update the existing session item.
                cmd = new AseCommand("UPDATE sessions SET Expires = @Expires, SessionItems = @SessionItems, Locked = @Locked WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
                cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now.AddMinutes((Double)item.Timeout);
                cmd.Parameters.Add("@SessionItems", AseDbType.Text).Value = sessItems;
                cmd.Parameters.Add("@Locked", AseDbType.Integer).Value = 0;
                cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
                cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = lockId;
            }

            try
            {
                conn.Open();

                if (deleteCmd != null)
                    deleteCmd.ExecuteNonQuery();

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

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="locked"></param>
        /// <param name="lockAge"></param>
        /// <param name="lockId"></param>
        /// <param name="actionFlags"></param>
        /// <returns></returns>
        public override SessionStateStoreData GetItem(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actionFlags)
        {
            return GetSessionStoreItem(false, context, id, out locked, out lockAge, out lockId, out actionFlags);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="locked"></param>
        /// <param name="lockAge"></param>
        /// <param name="lockId"></param>
        /// <param name="actionFlags"></param>
        /// <returns></returns>
        public override SessionStateStoreData GetItemExclusive(HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actionFlags)
        {
            return GetSessionStoreItem(true, context, id, out locked, out lockAge, out lockId, out actionFlags);
        }

        /// <summary>
        /// GetSessionStoreItem is called by both the GetItem and GetItemExclusive methods. GetSessionStoreItem retrieves the 
        /// session data from the data source. If the lockRecord parameter is true (in the case of GetItemExclusive), then GetSessionStoreItem 
        /// locks the record and sets a new LockId and LockDate.
        /// </summary>
        /// <param name="lockRecord"></param>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="locked"></param>
        /// <param name="lockAge"></param>
        /// <param name="lockId"></param>
        /// <param name="actionFlags"></param>
        /// <returns></returns>
        private SessionStateStoreData GetSessionStoreItem(bool lockRecord, HttpContext context, string id, out bool locked, out TimeSpan lockAge, out object lockId, out SessionStateActions actionFlags)
        {
            // Initial values for return value and out parameters.
            SessionStateStoreData item = null;
            lockAge = TimeSpan.Zero;
            lockId = null;
            locked = false;
            actionFlags = 0;

            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd = null;
            AseDataReader reader = null;

            // Datetime to check if current session item is expired.
            DateTime expires;

            // String to hold serialized SessionStateItemCollection.
            string serializedItems = "";

            // True if a record is found in the database.
            bool foundRecord = false;

            // True if the returned session item is expired and needs to be deleted.
            bool deleteData = false;

            // Timeout value from the data store.
            int timeout = 0;

            try
            {
                conn.Open();

                // lockRecord is true when called from GetItemExclusive and  false when called from GetItem. 
                // Obtain a lock if possible. Ignore the record if it is expired.
                if (lockRecord)
                {
                    cmd = new AseCommand("UPDATE sessions SET Locked = @Locked1, LockDate = @LockDate WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND Locked = @Locked2 AND Expires > @Expires", conn);
                    cmd.Parameters.Add("@Locked1", AseDbType.Integer).Value = 1;
                    cmd.Parameters.Add("@LockDate", AseDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                    cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
                    cmd.Parameters.Add("@Locked2", AseDbType.Integer).Value = 0;
                    cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now;

                    if (cmd.ExecuteNonQuery() == 0)
                    {
                        // No record was updated because the record was locked or not found.
                        locked = true;
                    }
                    else
                    {
                        // The record was updated.
                        locked = false;
                    }
                }

                // Retrieve the current session item information.
                cmd = new AseCommand("SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout FROM sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;

                // Retrieve session item data from the data source.
                reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                while (reader.Read())
                {
                    expires = reader.GetDateTime(0);

                    if (expires < DateTime.Now)
                    {
                        // The record was expired. Mark it as not locked.
                        locked = false;
                        // The session was expired. Mark the data for deletion.
                        deleteData = true;
                    }
                    else
                        foundRecord = true;

                    serializedItems = reader.GetString(1);
                    lockId = reader.GetInt32(2);
                    lockAge = DateTime.Now.Subtract(reader.GetDateTime(3));
                    actionFlags = (SessionStateActions)reader.GetInt32(4);
                    timeout = reader.GetInt32(5);
                }
                reader.Close();

                // If the returned session item is expired, 
                // delete the record from the data source.
                if (deleteData)
                {
                    cmd = new AseCommand("DELETE FROM sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                    cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                    cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;

                    cmd.ExecuteNonQuery();
                }

                // The record was not found. Ensure that locked is false.
                if (!foundRecord)
                    locked = false;

                // If the record was found and you obtained a lock, then set 
                // the lockId, clear the actionFlags,
                // and create the SessionStateStoreItem to return.
                if (foundRecord && !locked)
                {
                    lockId = (int)lockId + 1;

                    cmd = new AseCommand("UPDATE sessions SET LockId = @LockId, Flags = 0 WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                    cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = lockId;
                    cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
                    cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;

                    cmd.ExecuteNonQuery();

                    // If the actionFlags parameter is not InitializeItem, 
                    // deserialize the stored SessionStateItemCollection.
                    if (actionFlags == SessionStateActions.InitializeItem)
                        item = CreateNewStoreData(context, pConfig.Timeout.Minutes);
                    else
                        item = Deserialize(context, serializedItems, timeout);
                }
            }
            catch (AseException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetSessionStoreItem");
                    throw new ProviderException(exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                if (reader != null) { reader.Close(); }
                conn.Close();
            }

            return item;
        }

        /// <summary>
        /// Serialize is called by the SetAndReleaseItemExclusive method to convert the SessionStateItemCollection into a Base64 string to 
        /// be stored in an Access Memo field.
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        private string Serialize(SessionStateItemCollection items)
        {
            MemoryStream ms = new MemoryStream();
            BinaryWriter writer = new BinaryWriter(ms);

            if (items != null)
                items.Serialize(writer);

            writer.Close();

            return Convert.ToBase64String(ms.ToArray());
        }

        /// <summary>
        /// DeSerialize is called by the GetSessionStoreItem method to convert the Base64 string stored in the Access Memo field to a 
        /// SessionStateItemCollection.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="serializedItems"></param>
        /// <param name="timeout"></param>
        /// <returns></returns>
        private SessionStateStoreData Deserialize(HttpContext context, string serializedItems, int timeout)
        {
            MemoryStream ms = new MemoryStream(Convert.FromBase64String(serializedItems));

            SessionStateItemCollection sessionItems = new SessionStateItemCollection();

            if (ms.Length > 0)
            {
                BinaryReader reader = new BinaryReader(ms);
                sessionItems = SessionStateItemCollection.Deserialize(reader);
            }

            return new SessionStateStoreData(sessionItems,
              SessionStateUtility.GetSessionStaticObjects(context),
              timeout);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="lockId"></param>
        public override void ReleaseItemExclusive(HttpContext context, string id, object lockId)
        {
            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd = new AseCommand("UPDATE sessions SET Locked = 0, Expires = @Expires WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
            cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now.AddMinutes(pConfig.Timeout.Minutes);
            cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
            cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
            cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = lockId;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (AseException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ReleaseItemExclusive");
                    throw new ProviderException(exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="lockId"></param>
        /// <param name="item"></param>
        public override void RemoveItem(HttpContext context, string id, object lockId, SessionStateStoreData item)
        {
            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd = new AseCommand("DELETE FROM sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
            cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
            cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
            cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = lockId;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (AseException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "RemoveItem");
                    throw new ProviderException(exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        /// <param name="timeout"></param>
        public override void CreateUninitializedItem(HttpContext context, string id, int timeout)
        {
            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd = new AseCommand("INSERT INTO sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values(@SessionId, @ApplicationName, @Created, @Expires, @LockDate, @LockId, @Timeout, @Locked, @SessionItems, @Flags)", conn);
            cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
            cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;
            cmd.Parameters.Add("@Created", AseDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now.AddMinutes((Double)timeout);
            cmd.Parameters.Add("@LockDate", AseDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@LockId", AseDbType.Integer).Value = 0;
            cmd.Parameters.Add("@Timeout", AseDbType.Integer).Value = timeout;
            cmd.Parameters.Add("@Locked", AseDbType.Integer).Value = 0;
            cmd.Parameters.Add("@SessionItems", AseDbType.Text).Value = "";
            cmd.Parameters.Add("@Flags", AseDbType.Integer).Value = 1;

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

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="timeout"></param>
        /// <returns></returns>
        public override SessionStateStoreData CreateNewStoreData(HttpContext context, int timeout)
        {
            return new SessionStateStoreData(new SessionStateItemCollection(), SessionStateUtility.GetSessionStaticObjects(context), timeout);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="id"></param>
        public override void ResetItemTimeout(HttpContext context,
                                              string id)
        {
            AseConnection conn = new AseConnection(connectionString);
            AseCommand cmd = new AseCommand("UPDATE sessions SET Expires = @Expires WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
            cmd.Parameters.Add("@Expires", AseDbType.DateTime).Value = DateTime.Now.AddMinutes(pConfig.Timeout.Minutes);
            cmd.Parameters.Add("@SessionId", AseDbType.VarChar, 80).Value = id;
            cmd.Parameters.Add("@ApplicationName", AseDbType.VarChar, 255).Value = ApplicationName;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (AseException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ResetItemTimeout");
                    throw new ProviderException(exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public override void InitializeRequest(HttpContext context)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        public override void EndRequest(HttpContext context)
        {
        }

        /// <summary>
        /// WriteToEventLog
        /// This is a helper function that writes exception detail to the event log. Exceptions are written to the event log as a security
        /// measure to ensure private database details are not returned to browser. If a method does not return a status or Boolean
        /// indicating the action succeeded or failed, the caller also throws a generic exception.
        /// </summary>
        /// <param name="e"></param>
        /// <param name="action"></param>
        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = eventSource;
            log.Log = eventLog;

            string message = "An exception occurred communicating with the data source.\n\n";
            message += "Action: " + action + "\n\n";
            message += "Exception: " + e.ToString();

            log.WriteEntry(message);
        }
    }