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