Sample Session Providers with PostgreSQL

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 Npgsql;
using NpgsqlTypes;

namespace ThreeOaks.PostgreSQLSession
{



    /* This session state store provider supports the following schema:

    CREATE TABLE sessions
    (
      sessionid character varying(80) NOT NULL,
      applicationname character varying(255) NOT NULL,
      created timestamp without time zone NOT NULL,
      expires timestamp without time zone NOT NULL,
      lockdate timestamp without time zone NOT NULL,
      lockid integer NOT NULL,
      timeout integer NOT NULL,
      locked boolean NOT NULL,
      sessionitems text,
      flags integer NOT NULL,
      CONSTRAINT pksessions PRIMARY KEY (sessionid, applicationname)
    ) 
    WITHOUT OIDS;
    ALTER TABLE sessions OWNER TO postgres;

    This session state store provider does not automatically clean up 
    expired session item data. It is recommended
    that you periodically delete expired session information from the
    data store with the following code (where 'conn' is the NpgsqlConnection
    for the session state store provider):

      string commandString = "DELETE FROM Sessions WHERE Expires < ?";
      NpgsqlConnection conn = new NpgsqlConnection(connectionString);
      NpgsqlCommand cmd = new NpgsqlCommand(commandString, conn);
      cmd.Parameters.Add("@Expires", NpgsqlDbType.Timestamp).Value = DateTime.Now;
      conn.Open();
      cmd.ExecuteNonQuery();
      conn.Close();
  
    */

    /// <summary>
    /// A Session State provider using the PostgreSQL RDBMS without stored procedures
    /// </summary>
    public sealed class PostgreSQLSessionStateStoreNoSP : SessionStateStoreProviderBase,
        ThreeOaks.SessionProviders.IThreeOaksSessionProvider
    {

        private SessionStateSection _configSessionStateSection = null;
        /// <summary>
        /// The connection name used in the connection configuration
        /// </summary>
        public string connectionName = "PostgreSQLSessionServices";

        private string _connectionString;
        private ConnectionStringSettings _connectionStringSettings;
        private readonly string _eventSource = "PostgreSQLSessionStateStore";
        private readonly string _eventLog = "Application";
        private string _exceptionMessage =
          "An exception occurred. Please contact your administrator.";
        private string _applicationName;


        private bool _writeExceptionsToEventLog = 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 _writeExceptionsToEventLog; }
            set { _writeExceptionsToEventLog = value; }
        }

        /// <summary>
        /// The application currently using the provider.
        /// </summary>
        public string ApplicationName
        {
            get { return _applicationName; }
        }


        /// <summary>
        /// Called once for the instance.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="config"></param>
        public override void Initialize(string name, NameValueCollection config)
        {

            Debug.WriteLine("Initialize:" + DateTime.Now.Ticks.ToString());

            //
            // Initialize values from web.config.
            //

            if (config == null)
                throw new ArgumentNullException("config");

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

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

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


            //
            // Initialize the ApplicationName property.
            //

            _applicationName =
              System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;


            //
            // Get <sessionState> configuration element.
            //

            Configuration cfg =
              WebConfigurationManager.OpenWebConfiguration(_applicationName);
            _configSessionStateSection =
              (SessionStateSection)cfg.GetSection("system.web/sessionState");


            //
            // Initialize connection string.
            //

            _connectionStringSettings =
              ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

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

            _connectionString = _connectionStringSettings.ConnectionString;


            //
            // Initialize WriteExceptionsToEventLog
            //

            _writeExceptionsToEventLog = false;

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


        /// <summary>
        /// Not used, needed by base
        /// </summary>
        public override void Dispose()
        {
        }


        /// <summary>
        /// SessionStateProviderBase.SetItemExpireCallback
        /// Set to false to tell that Expiring Item won't be called back to 
        /// ASP.NET to generate a Session_OnEnd event
        /// </summary>
        /// <param name="expireCallback"></param>
        /// <returns></returns>
        public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)
        {

            Debug.WriteLine("SetItemExpireCallback");

            return false;
        }


        /// <summary>
        /// SessionStateProviderBase.SetAndReleaseItemExclusive
        /// If new item, checks if item is expired - deletes and creates new if it is.
        /// If not new, updates record and releases lock
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></param>
        /// <param name="item"></param>
        /// <param name="lockId"></param>
        /// <param name="newItem"></param>
        public override void SetAndReleaseItemExclusive(HttpContext context,
          string sessionId,
          SessionStateStoreData item,
          object lockId,
          bool newItem)
        {

            Debug.WriteLine("SetAndReleaseItemExclusive id:" + sessionId);

            // Serialize the SessionStateItemCollection as a string.
            string sessItems = Serialize((SessionStateItemCollection)item.Items);

            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            NpgsqlCommand cmd;
            NpgsqlCommand deleteCmd = null;

            if (newItem)
            {
                // NpgsqlCommand to clear an existing expired session if it exists.
                deleteCmd = new NpgsqlCommand(
                    @"DELETE FROM Sessions WHERE SessionId = @SessionId 
                    AND ApplicationName = @ApplicationName AND Expires < @Expires", conn);
                deleteCmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                deleteCmd.Parameters.Add
                  ("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = ApplicationName;
                deleteCmd.Parameters.Add
                  ("@Expires", NpgsqlDbType.Timestamp).Value = DateTime.Now;

                // NpgsqlCommand to insert the new session item.
                cmd = new NpgsqlCommand(
                    @"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", NpgsqlDbType.Varchar, 80).Value = sessionId;
                cmd.Parameters.Add
                  ("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = ApplicationName;
                cmd.Parameters.Add
                  ("@Created", NpgsqlDbType.Timestamp).Value = DateTime.Now;
                cmd.Parameters.Add
                  ("@Expires", NpgsqlDbType.Timestamp).Value = DateTime.Now.AddMinutes((Double)item.Timeout);
                cmd.Parameters.Add
                  ("@LockDate", NpgsqlDbType.Timestamp).Value = DateTime.Now;
                cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = 0;
                cmd.Parameters.Add
                  ("@Timeout", NpgsqlDbType.Integer).Value = item.Timeout;
                cmd.Parameters.Add("@Locked", NpgsqlDbType.Boolean).Value = false;
                cmd.Parameters.Add
                  ("@SessionItems", NpgsqlDbType.Varchar, sessItems.Length).Value = sessItems;
                //cmd.Parameters.Add
                //  ("@SessionItems", NpgsqlDbType.Text).Value = sessItems;
                cmd.Parameters.Add("@Flags", NpgsqlDbType.Integer).Value = 0;
            }
            else
            {
                // NpgsqlCommand to update the existing session item.
                cmd = new NpgsqlCommand(
                  @"UPDATE Sessions SET Expires = @Expires
                , SessionItems = @SessionItems, Locked = @Locked
                WHERE SessionId = @SessionId 
                AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
                cmd.Parameters.Add("@Expires", NpgsqlDbType.Timestamp).Value =
                  DateTime.Now.AddMinutes((Double)item.Timeout);
                cmd.Parameters.Add("@SessionItems",
                  NpgsqlDbType.Varchar, sessItems.Length).Value = sessItems;
                //cmd.Parameters.Add
                //  ("@SessionItems", NpgsqlDbType.Text).Value = sessItems;
                cmd.Parameters.Add("@Locked", NpgsqlDbType.Boolean).Value = false;
                cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
                  255).Value = ApplicationName;
                cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = lockId;
            }

            try
            {
                conn.Open();

                if (deleteCmd != null)
                    deleteCmd.ExecuteNonQuery();
                Debug.WriteLine("sessItems.Length = " + sessItems.Length.ToString());
                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "SetAndReleaseItemExclusive");
                    throw new ProviderException(_exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// Gets the session state item, but does not lock it.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></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 sessionId,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)
        {

            Debug.WriteLine("GetItem id: " + sessionId);

            return GetSessionStoreItem(false, context, sessionId, out locked,
              out lockAge, out lockId, out actionFlags);
        }

        /// <summary>
        /// SessionStateProviderBase.GetItemExclusive
        /// Gets the session items from the store and locks it
        /// to a lockId.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></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 sessionId,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)
        {

            Debug.WriteLine("GetItemExclusive id: " + sessionId);

            return GetSessionStoreItem(true, context, sessionId, 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="sessionId"></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 sessionId,
          out bool locked,
          out TimeSpan lockAge,
          out object lockId,
          out SessionStateActions actionFlags)
        {

            Debug.WriteLine("GetSessionStoreItem id: " + sessionId);

            // Initial values for return value and out parameters.
            SessionStateStoreData item = null;
            lockAge = TimeSpan.Zero;
            lockId = null;
            locked = false;
            actionFlags = 0;

            // Npgsql database connection.
            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            // NpgsqlCommand for database commands.
            NpgsqlCommand cmd = null;
            // DataReader to read database record.
            NpgsqlDataReader 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 NpgsqlCommand(
                      @"UPDATE Sessions SET
                     Locked = @Locked1, LockDate = @LockDate
                     WHERE SessionId = @SessionId 
                    AND ApplicationName = @ApplicationName 
                    AND Locked = @Locked2 AND Expires > @Expires", conn);
                    cmd.Parameters.Add("@Locked1", NpgsqlDbType.Boolean).Value = true;
                    cmd.Parameters.Add("@LockDate", NpgsqlDbType.Timestamp).Value
                      = DateTime.Now;
                    cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                    cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
                      255).Value = ApplicationName;
                    cmd.Parameters.Add("@Locked2", NpgsqlDbType.Boolean).Value = false;
                    cmd.Parameters.Add
                      ("@Expires", NpgsqlDbType.Timestamp).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 NpgsqlCommand(
                  @"SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout 
                  FROM Sessions 
                  WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.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)
                {
                    Debug.WriteLine("Deleting session record.", "GetSessionStoreItem");
                    cmd = new NpgsqlCommand(
                        @"DELETE FROM Sessions 
                    WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                    cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                    cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.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 NpgsqlCommand(
                        @"UPDATE Sessions SET
                     LockId = @LockId, Flags = 0 
                     WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
                    cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = lockId;
                    cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
                    cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar, 255).Value = ApplicationName;

                    cmd.ExecuteNonQuery();

                    // If the actionFlags parameter is not InitializeItem, 
                    // deserialize the stored SessionStateItemCollection.
                    if (actionFlags == SessionStateActions.InitializeItem)
                        item = CreateNewStoreData(context, _configSessionStateSection.Timeout.Minutes);
                    else
                        item = Deserialize(context, serializedItems, timeout);
                }
            }
            catch (NpgsqlException 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)
        {

            Debug.WriteLine("Serialize");

            MemoryStream ms = null;
            try
            {
                ms = new MemoryStream();
                BinaryWriter writer = new BinaryWriter(ms);

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

                Debug.WriteLine("Serialize Length: " + ms.Length.ToString());
                writer.Close();

                return Convert.ToBase64String(ms.ToArray());
            }
            finally
            {
                if (ms != null)
                {
                    ms.Close();
                }

            }
        }

        /// <summary>
        /// DeSerialize is called by the GetSessionStoreItem method to 
        /// convert the Base64 string stored in the 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)
        {

            Debug.WriteLine("Deserialize");

            MemoryStream ms = null;
            SessionStateItemCollection sessionItems = null;
            try
            {
                if (serializedItems != "")
                {
                    ms =
                     new MemoryStream(Convert.FromBase64String(serializedItems));
                    BinaryReader reader = new BinaryReader(ms);
                    sessionItems = SessionStateItemCollection.Deserialize(reader);
                }
                else
                {
                    sessionItems = new SessionStateItemCollection();
                }
                return new SessionStateStoreData(sessionItems,
                  SessionStateUtility.GetSessionStaticObjects(context),
                  timeout);

            }
            finally
            {
                if (ms != null)
                {
                    ms.Close();
                }
            }
        }

        /// <summary>
        /// SessionStateProviderBase.ReleaseItemExclusive
        /// Releases an exclusive lock on a session at the
        /// lockId level. Also bumps up the Expires dates/time.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></param>
        /// <param name="lockId"></param>
        public override void ReleaseItemExclusive(HttpContext context,
          string sessionId,
          object lockId)
        {

            Debug.WriteLine("ReleaseItemExclusive id:" + sessionId);

            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            NpgsqlCommand cmd =
              new NpgsqlCommand(
              @"UPDATE Sessions SET Locked = @Locked, Expires = @Expires 
            WHERE SessionId = @SessionId 
            AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
            cmd.Parameters.Add("@Locked", NpgsqlDbType.Boolean).Value = false;
            cmd.Parameters.Add("@Expires", NpgsqlDbType.Timestamp).Value =
              DateTime.Now.AddMinutes(_configSessionStateSection.Timeout.Minutes);
            cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
            cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
              255).Value = ApplicationName;
            cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = lockId;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ReleaseItemExclusive");
                    throw new ProviderException(_exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// SessionStateProviderBase.RemoveItem
        /// Removes all rows associated to the Session,
        /// application, and lockId
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></param>
        /// <param name="lockId"></param>
        /// <param name="item"></param>
        public override void RemoveItem(HttpContext context,
          string sessionId,
          object lockId,
          SessionStateStoreData item)
        {

            Debug.WriteLine("RemoveItem id:" + sessionId);

            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            NpgsqlCommand cmd = new NpgsqlCommand(
                @"DELETE * FROM Sessions 
                WHERE SessionId = @SessionId 
                AND ApplicationName = @ApplicationName AND LockId = @LockId", conn);
            cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
            cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
              255).Value = ApplicationName;
            cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = lockId;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "RemoveItem");
                    throw new ProviderException(_exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// SessionStateProviderBase.CreateUninitializedItem
        /// Called when using cookie-less sessions
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></param>
        /// <param name="timeout"></param>
        public override void CreateUninitializedItem(HttpContext context,
         string sessionId,
         int timeout)
        {

            Debug.WriteLine("CreateUninitializedItem id: " + sessionId);

            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            NpgsqlCommand cmd = new NpgsqlCommand(
              @"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", NpgsqlDbType.Varchar, 80).Value = sessionId;
            cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
              255).Value = ApplicationName;
            cmd.Parameters.Add("@Created", NpgsqlDbType.Timestamp).Value
              = DateTime.Now;
            cmd.Parameters.Add("@Expires", NpgsqlDbType.Timestamp).Value
              = DateTime.Now.AddMinutes((Double)timeout);
            cmd.Parameters.Add("@LockDate", NpgsqlDbType.Timestamp).Value
              = DateTime.Now;
            cmd.Parameters.Add("@LockId", NpgsqlDbType.Integer).Value = 0;
            cmd.Parameters.Add("@Timeout", NpgsqlDbType.Integer).Value = timeout;
            cmd.Parameters.Add("@Locked", NpgsqlDbType.Boolean).Value = false;
            cmd.Parameters.Add("@SessionItems", NpgsqlDbType.Varchar, 0).Value = "";
            //cmd.Parameters.Add("@SessionItems", NpgsqlDbType.Text).Value = "";
            cmd.Parameters.Add("@Flags", NpgsqlDbType.Integer).Value = 1;

            try
            {
                conn.Open();

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


        /// <summary>
        /// SessionStateProviderBase.CreateNewStoreData
        /// Creates a new SessionStateStoreData.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="timeout"></param>
        /// <returns></returns>
        public override SessionStateStoreData CreateNewStoreData(
          HttpContext context,
          int timeout)
        {

            Debug.WriteLine("CreateNewStoreData");

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


        /// <summary>
        /// SessionStateProviderBase.ResetItemTimeout
        /// Called a lot, even on pages that don't want session.
        /// </summary>
        /// <param name="context"></param>
        /// <param name="sessionId"></param>
        public override void ResetItemTimeout(HttpContext context,
                                              string sessionId)
        {

            Debug.WriteLine("ResetItemTimeout id: " + sessionId);

            NpgsqlConnection conn = new NpgsqlConnection(_connectionString);
            NpgsqlCommand cmd =
              new NpgsqlCommand(
              @"UPDATE Sessions SET Expires = @Expires 
            WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName", conn);
            cmd.Parameters.Add("@Expires", NpgsqlDbType.Timestamp).Value
              = DateTime.Now.AddMinutes(_configSessionStateSection.Timeout.Minutes);
            cmd.Parameters.Add("@SessionId", NpgsqlDbType.Varchar, 80).Value = sessionId;
            cmd.Parameters.Add("@ApplicationName", NpgsqlDbType.Varchar,
              255).Value = ApplicationName;

            try
            {
                conn.Open();

                cmd.ExecuteNonQuery();
            }
            catch (NpgsqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "ResetItemTimeout");
                    throw new ProviderException(_exceptionMessage);
                }
                else
                    throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// SessionStateProviderBase.InitializeRequest
        /// Happens on each request to session.
        /// </summary>
        /// <param name="context"></param>
        public override void InitializeRequest(HttpContext context)
        {
            Debug.WriteLine("InitializeRequest");
        }


        /// <summary>
        /// Happens at the end of each request for a session
        /// </summary>
        /// <param name="context"></param>
        public override void EndRequest(HttpContext context)
        {
            Debug.WriteLine("EndRequest");
        }

        /// <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)
        {

            Debug.WriteLine("WriteToEventLog");

            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);
        }

        /// <summary>
        /// Deletes any Sessions that are expired
        /// </summary>
        public void DeleteExpiredSessions()
        {
            //retrieve the connection string
            _connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionName];

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

            using (NpgsqlConnection conn = new NpgsqlConnection(_connectionString))
            {
                //delete any session that expired before now.
                NpgsqlCommand cmd = new NpgsqlCommand("DELETE FROM sessions WHERE Expires < CURRENT_TIMESTAMP", conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

    }
}