SQL CE as session state provider RSS

• • Reply
  

  DracoBlanc

  0 Points

  3 Posts

  SQL CE as session state provider

  Jan 17, 2011 06:46 PM|LINK

  Based on the code from this article I cobbled together a CE version, that does support session_end as well.  I am posting the code inline, enjoy :D

  
  

  The sessionState section of the web.config would look similar to this:

      <sessionState mode="Custom" customProvider="CEProvider">
        <providers>
          <add name="CEProvider" type="GPMS.SqlCeSessionStateStore" connectionStringName="GPMSSession" writeExceptionsToEventLog="false" />
        </providers>
      </sessionState>
  

  Where the name "GPMSSession" refers to a named connection string in the same web.config file.

  /*
  This session state store provider supports the following schema:
  
  
    CREATE TABLE Sessions
    (
      SessionId       NVarChar(80)  NOT NULL,
      ApplicationName NVarChar(255) NOT NULL,
      Created         DateTime  NOT NULL,
      Expires         DateTime  NOT NULL,
      LockDate        DateTime  NOT NULL,
      LockId          Integer   NOT NULL,
      Timeout         Integer   NOT NULL,
      Locked          Bit     NOT NULL,
      SessionItems    NText,
      Flags           Integer   NOT NULL,
        PRIMARY KEY (SessionId, ApplicationName)
    )
  
  
  This session state store provider will autocreate the sessions table if
   * it is not present in the datastore initially
  */
  
  
  public sealed class SqlCeSessionStateStore : SessionStateStoreProviderBase
      {
          private SessionStateSection _config;
          private string _connectionString;
          private ConnectionStringSettings _connectionStringSettings;
          private const string EventSource = "SqlCESessionStateStore";
          private const string EventLog = "Application";
          private const string ExceptionMessage ="An exception occurred. Please contact your administrator.";
          private string _applicationName;
          private bool _writeExceptionsToEventLog;
          private SessionStateItemExpireCallback _expired;
          public bool WriteExceptionsToEventLog
          {
              get { return _writeExceptionsToEventLog; }
              set { _writeExceptionsToEventLog = value; }
          }
          public string ApplicationName
          {
              get { return _applicationName; }
          }
          public override void Initialize(string name, NameValueCollection config)
          {
              //
              // Initialize values from web.config.
              //
              if (config == null)
                  throw new ArgumentNullException("config");
  
  
              if (string.IsNullOrEmpty(name))
                  name = "SqlCeSessionStateStore";
  
  
              if (String.IsNullOrEmpty(config["description"]))
              {
                  config.Remove("description");
                  config.Add("description", "Sql CE 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);
              _config =(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;
              }
  
  
              //Make sure our table is present:
              CreateStructure();
          }
          private void CreateStructure()
          {
              const string newtablesql = "CREATE TABLE Sessions( SessionId NVarChar(80)  NOT NULL , ApplicationName NVarChar(255) NOT NULL, Created DateTime  NOT NULL,Expires DateTime  NOT NULL,LockDate DateTime  NOT NULL, LockId Integer   NOT NULL,Timeout Integer   NOT NULL,Locked Bit NOT NULL,SessionItems ntext,Flags Integer   NOT NULL, PRIMARY KEY (SessionId,ApplicationName))";
  
  
              var conn = new SqlCeConnection(_connectionString);
              try
              {
                  var cmd = new SqlCeCommand("select count(*) from INFORMATION_SCHEMA.Tables where TABLE_NAME='Sessions'",conn);
                  conn.Open();
                  if(Convert.ToInt32(cmd.ExecuteScalar())==0)
                  {
                      //We need to create our table
                      var cmdcreate = new SqlCeCommand(newtablesql, conn);
                      cmdcreate.ExecuteNonQuery();
                  }
              }
              catch (Exception e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "SetAndReleaseItemExclusive");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          public override void Dispose()
          {
          }
          public override bool SetItemExpireCallback(SessionStateItemExpireCallback expireCallback)
          {
              Observable.Interval(new TimeSpan(0, 0, 0, 10), Scheduler.ThreadPool).Subscribe(HandleSessionTimeOutTick);
              _expired = expireCallback;
              return true;
          }
          private void HandleSessionTimeOutTick(long tick)
          {
              var conn = new SqlCeConnection(_connectionString);
  
  
              try
              {
                  DateTime dtnow = DateTime.Now;
                  var cmdexpired = new SqlCeCommand("Select SessionId,SessionItems from Sessions where Expires < @current", conn);
                  var cmddelete = new SqlCeCommand("Delete from Sessions where sessionid=@id",conn);
                  cmdexpired.Parameters.Add("@current", SqlDbType.DateTime).Value =dtnow;
                  cmddelete.Parameters.Add("@id", SqlDbType.NVarChar, 80);
                  conn.Open();
                  var reader = cmdexpired.ExecuteReader();
                  while(reader.Read())
                  {
                      string items = reader.GetString(1);
                      string id = reader.GetString(0);
                      var ms = new MemoryStream(Convert.FromBase64String(items));
  
  
                      var sessionItems = new SessionStateItemCollection();
                      if (ms.Length > 0)
                      {
                          var binreader = new BinaryReader(ms);
                          sessionItems = SessionStateItemCollection.Deserialize(binreader);
                      }
                      var sssd=new SessionStateStoreData(sessionItems,new HttpStaticObjectsCollection(), 0);
                      _expired(id, sssd);
                      cmddelete.Parameters["@id"].Value = id;
                      cmddelete.ExecuteNonQuery();
                  }
                  reader.Close();
              }
              catch (Exception e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "SetAndReleaseItemExclusive");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          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);
              var conn = new SqlCeConnection(_connectionString);
              SqlCeCommand cmd;
              SqlCeCommand deleteCmd = null;
             
  
             
  
              if (newItem)
              {
                  // SqlCeCommand to clear an existing expired session if it exists.
                  deleteCmd = new SqlCeCommand("DELETE FROM Sessions WHERE SessionId = @id AND ApplicationName = @appname AND Expires < @exp", conn);
                  deleteCmd.Parameters.Add("@id", SqlDbType.NVarChar, 80).Value = id;
                  deleteCmd.Parameters.Add("@appname", SqlDbType.NVarChar, 255).Value = ApplicationName;
                  deleteCmd.Parameters.Add("@exp", SqlDbType.DateTime).Value = DateTime.Now;
  
  
                  // SqlCeCommand to insert the new session item.
                  cmd = new SqlCeCommand("INSERT INTO Sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) " +
                    " Values(@id, @name, @created, @expires, @lockdate, @lockid , @timeout, @locked, @sessionitems, @flags)", conn);
                  cmd.Parameters.Add("@id", SqlDbType.NVarChar, 80).Value = id;
                  cmd.Parameters.Add("@name", SqlDbType.NVarChar, 255).Value = ApplicationName;
                  cmd.Parameters.Add("@created", SqlDbType.DateTime).Value = DateTime.Now;
                  cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value = DateTime.Now.AddMinutes(item.Timeout);
                  cmd.Parameters.Add("@lockDate", SqlDbType.DateTime).Value = DateTime.Now;
                  cmd.Parameters.Add("@lockId", SqlDbType.Int).Value = 0;
                  cmd.Parameters.Add("@timeout", SqlDbType.Int).Value = item.Timeout;
                  cmd.Parameters.Add("@locked", SqlDbType.Bit).Value = false;
                  cmd.Parameters.Add("@sessionitems", SqlDbType.NVarChar, sessItems.Length).Value = sessItems;
                  cmd.Parameters.Add("@flags", SqlDbType.Int).Value = 0;
              }
              else
              {
                  // SqlCeCommand to update the existing session item.
                  cmd = new SqlCeCommand("UPDATE Sessions SET Expires = @expires, SessionItems = @sessionitems, Locked = @locked WHERE SessionId = @sessionid AND ApplicationName = @applicationname AND LockId = @lockid", conn);
                  cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value =DateTime.Now.AddMinutes(item.Timeout);
                  cmd.Parameters.Add("@sessionitems",SqlDbType.NVarChar, sessItems.Length).Value = sessItems;
                  cmd.Parameters.Add("@locked", SqlDbType.Bit).Value = false;
                  cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
                  cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
                  cmd.Parameters.Add("@lockid", SqlDbType.Int).Value = lockId;
              }
  
  
              try
              {
                  conn.Open();
  
  
                  if (deleteCmd != null)
                      deleteCmd.ExecuteNonQuery();
  
  
                  cmd.ExecuteNonQuery();
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "SetAndReleaseItemExclusive");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          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);
          }
          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);
          }
          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;
  
  
              // ODBC database connection.
              var conn = new SqlCeConnection(_connectionString);
              // SqlCeCommand for database commands.
              SqlCeCommand cmd;
              // DataReader to read database record.
              SqlCeDataReader reader = null;
              // DateTime to check if current session item is expired.
              // 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 SqlCeCommand("UPDATE Sessions SET Locked = @locked, LockDate = @lockdate  WHERE SessionId = @sessionid AND ApplicationName = @applicationname AND Locked = @islocked AND Expires > @expires", conn);
                      cmd.Parameters.Add("@locked", SqlDbType.Bit).Value = true;
                      cmd.Parameters.Add("@lockdate", SqlDbType.DateTime).Value= DateTime.Now;
                      cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
                      cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
                      cmd.Parameters.Add("@islocked", SqlDbType.Int).Value = false;
                      cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value = DateTime.Now;
                      locked = cmd.ExecuteNonQuery() == 0;
                  }
  
  
                  // Retrieve the current session item information.
                  cmd = new SqlCeCommand("SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout FROM Sessions WHERE SessionId = @sessionid AND ApplicationName = @applicationname", conn);
                  cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
                  cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
  
  
                  // Retrieve session item data from the data source.
                  reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                  while (reader.Read())
                  {
                      DateTime 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 SqlCeCommand("DELETE FROM Sessions WHERE SessionId = @sessionid AND applicationname = @applicationname", conn);
                      cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
                      cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,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 SqlCeCommand("UPDATE Sessions SET LockId = @lockid, Flags = 0 WHERE SessionId = @sessionid AND ApplicationName = @applicationname", conn);
                      cmd.Parameters.Add("@lockid", SqlDbType.Int).Value = lockId;
                      cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
                      cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar, 255).Value = ApplicationName;
  
  
                      cmd.ExecuteNonQuery();
  
  
                      // If the actionFlags parameter is not InitializeItem,
                      // deserialize the stored SessionStateItemCollection.
                      item = actionFlags == SessionStateActions.InitializeItem ? CreateNewStoreData(context, Convert.ToInt32(Math.Ceiling(_config.Timeout.TotalMinutes))) : Deserialize(context, serializedItems, timeout);
                  }
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "GetSessionStoreItem");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  if (reader != null) { reader.Close(); }
                  conn.Close();
              }
  
  
              return item;
          }
          private static string Serialize(SessionStateItemCollection items)
          {
              var ms = new MemoryStream();
              var writer = new BinaryWriter(ms);
  
  
              if (items != null)
                  items.Serialize(writer);
  
  
              writer.Close();
  
  
              return Convert.ToBase64String(ms.ToArray());
          }
          private static SessionStateStoreData Deserialize(HttpContext context,string serializedItems, int timeout)
          {
              var ms =new MemoryStream(Convert.FromBase64String(serializedItems));
  
  
              var sessionItems =new SessionStateItemCollection();
  
  
              if (ms.Length > 0)
              {
                  var reader = new BinaryReader(ms);
                  sessionItems = SessionStateItemCollection.Deserialize(reader);
              }
  
  
              return new SessionStateStoreData(sessionItems,SessionStateUtility.GetSessionStaticObjects(context),timeout);
          }
          public override void ReleaseItemExclusive(HttpContext context,string id,object lockId)
          {
              var conn = new SqlCeConnection(_connectionString);
              var cmd =new SqlCeCommand("UPDATE Sessions SET Locked = 0, Expires = @expires WHERE SessionId = @sessionid AND ApplicationName = @applicationname AND LockId = @lockid", conn);
              cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value =DateTime.Now.AddMinutes(_config.Timeout.TotalMinutes);
              cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
              cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
              cmd.Parameters.Add("@lockid", SqlDbType.Int).Value = lockId;
  
  
              try
              {
                  conn.Open();
  
  
                  cmd.ExecuteNonQuery();
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "ReleaseItemExclusive");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          public override void RemoveItem(HttpContext context,string id,object lockId,SessionStateStoreData item)
          {
              var conn = new SqlCeConnection(_connectionString);
              var cmd = new SqlCeCommand("DELETE * FROM Sessions WHERE SessionId = @sessionid AND ApplicationName = @applicationname AND LockId = @lockid", conn);
              cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
              cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
              cmd.Parameters.Add("@lockid", SqlDbType.Int).Value = lockId;
  
  
              try
              {
                  conn.Open();
  
  
                  cmd.ExecuteNonQuery();
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "RemoveItem");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          public override void CreateUninitializedItem(HttpContext context,string id,int timeout)
          {
              var conn = new SqlCeConnection(_connectionString);
              var cmd = new SqlCeCommand("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", SqlDbType.NVarChar, 80).Value = id;
              cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
              cmd.Parameters.Add("@created", SqlDbType.DateTime).Value= DateTime.Now;
              cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value= DateTime.Now.AddMinutes(timeout);
              cmd.Parameters.Add("@lockdate", SqlDbType.DateTime).Value= DateTime.Now;
              cmd.Parameters.Add("@lockid", SqlDbType.Int).Value = 0;
              cmd.Parameters.Add("@timeout", SqlDbType.Int).Value = timeout;
              cmd.Parameters.Add("@locked", SqlDbType.Bit).Value = false;
              cmd.Parameters.Add("@sessionitems", SqlDbType.NText).Value = "";
              cmd.Parameters.Add("@flags", SqlDbType.Int).Value = 1;
  
  
              try
              {
                  conn.Open();
  
  
                  cmd.ExecuteNonQuery();
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "CreateUninitializedItem");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          public override SessionStateStoreData CreateNewStoreData(HttpContext context,int timeout)
          {
              return new SessionStateStoreData(new SessionStateItemCollection(),SessionStateUtility.GetSessionStaticObjects(context),timeout);
          }
          public override void ResetItemTimeout(HttpContext context,string id)
          {
              var conn = new SqlCeConnection(_connectionString);
              var cmd = new SqlCeCommand("UPDATE Sessions SET Expires = @expires WHERE SessionId = @sessionid AND ApplicationName = @applicationname", conn);
              cmd.Parameters.Add("@expires", SqlDbType.DateTime).Value= DateTime.Now.AddMinutes(_config.Timeout.TotalMinutes);
              cmd.Parameters.Add("@sessionid", SqlDbType.NVarChar, 80).Value = id;
              cmd.Parameters.Add("@applicationname", SqlDbType.NVarChar,255).Value = ApplicationName;
  
  
              try
              {
                  conn.Open();
  
  
                  cmd.ExecuteNonQuery();
              }
              catch (SqlCeException e)
              {
                  if (WriteExceptionsToEventLog)
                  {
                      WriteToEventLog(e, "ResetItemTimeout");
                      throw new ProviderException(ExceptionMessage);
                  }
                  else
                      throw;
              }
              finally
              {
                  conn.Close();
              }
          }
          public override void InitializeRequest(HttpContext context)
          {
          }
          public override void EndRequest(HttpContext context)
          {
          }
          private static void WriteToEventLog(Exception e, string action)
          {
              var log = new EventLog {Source = EventSource, Log = EventLog};
  
  
              string message ="An exception occurred communicating with the data source.\n\n";
              message += "Action: " + action + "\n\n";
              message += "Exception: " + e;
  
  
              log.WriteEntry(message);
          }
      }

  
  
  

  • Moved by SGWellens on Jan 17, 2011 07:29 PM
  • Edited by DracoBlanc on Jan 17, 2011 08:42 PM
  • Edited by DracoBlanc on Jan 17, 2011 08:44 PM