/// <summary>
/// 创建新会话
/// </summary>
/// <param name="id"></param>
/// <param name="value"></param>
public void CreateStoreItem(String id, String value)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(SessionId) FROM [ViewState] WHERE SessionId = @SessionId";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 100, id));
Int32 reader = Convert.ToInt32(cmd.ExecuteScalar());
if (reader.Equals(0))
{
cmd.CommandText = "INSERT INTO [ViewState] (SessionId, Created, Expires, Timeout, SessionItems) Values(@SessionId, @Created, @Expires,@Timeout,@SessionItems)";
}
else
{
cmd.CommandText = "UPDATE [ViewState] SET Created=@Created, Expires = @Expires, SessionItems = @SessionItems WHERE SessionId = @SessionId";
}
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Created", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now.AddMinutes(30)));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Timeout", DbType.Int32, 30));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionItems", DbType.String, value.Length, value));
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// SessionStateProviderBase.ResetItemTimeout
/// </summary>
public override void ResetItemTimeout(HttpContext context, String id)
{
IDbConnection conn = new SQLiteConnection(_connectionString);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Sessions SET Expires = @Expires WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now.AddMinutes(_config.Timeout.TotalMinutes)));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, ApplicationName));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ResetItemTimeout");
throw new ProviderException(_exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
/// <summary>
/// SessionStateProviderBase.RemoveItem
/// </summary>
public override void RemoveItem(HttpContext context, String id, Object lockId, SessionStateStoreData item)
{
IDbConnection conn = new SQLiteConnection(_connectionString);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 255, ApplicationName));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockId", DbType.Int32, lockId));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveItem");
throw new ProviderException(_exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
/// <summary>
/// Remove expired session data.
/// </summary>
private void CleanUpExpiredData()
{
IDbConnection conn = new SQLiteConnection(_connectionString);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Sessions WHERE Expires < @Expires";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
/// <summary>
/// 清理过期会话
/// </summary>
public static void CleanUpExpiredData()
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "DELETE FROM [viewstate] WHERE Expires < @Expires";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now));
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 获取会话
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
protected Byte[] GetStoreItem(String id)
{
Byte[] buffer;
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT SessionItems FROM [ViewState] WHERE SessionId = @SessionId";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 100, id));
buffer = (Byte[])cmd.ExecuteScalar();
}
}
return(buffer);
}
/// <summary>
/// 获取会话
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public String GetStoreItem(String id)
{
String SessionItems = String.Empty;
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT SessionItems FROM [ViewState] WHERE SessionId = @SessionId";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 100, id));
SessionItems = Convert.ToString(cmd.ExecuteScalar());
}
}
return(SessionItems);
}
/// <summary>
/// SessionStateProviderBase.CreateUninitializedItem
/// </summary>
public override void CreateUninitializedItem(HttpContext context, String id, int timeout)
{
IDbConnection conn = new SQLiteConnection(_connectionString);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values(@SessionId, @ApplicationName, @Created, @Expires, @LockDate, @LockId, @Timeout, @Locked, @SessionItems, @Flags)";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, ApplicationName));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Created", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now.AddMinutes((Double)timeout)));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockDate", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockId", DbType.Int32, 0));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Timeout", DbType.Int32, timeout));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Locked", DbType.Boolean, false));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionItems", DbType.Binary, null));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Flags", DbType.Int32, 1));
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUninitializedItem");
throw new ProviderException(_exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
/// <summary>
/// GetSessionStoreItem is called by both the GetItem and
/// GetItemExclusive methods. GetSessionStoreItem retrieves the </summary>
/// session data from the data source. If the lockRecord parameter<param name="lockRecord"></param>
/// is true (in the case of GetItemExclusive), then GetSessionStoreItem<param name="context"></param>
/// locks the record and sets a new LockId and LockDate.<param name="id"></param>
private SessionStateStoreData GetSessionStoreItem(Boolean lockRecord, HttpContext context, String id, out Boolean 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;
// SQLite database connection.
IDbConnection conn = new SQLiteConnection(_connectionString);
// SQLiteCommand for database commands.
IDbCommand cmd = null;
// DataReader to read database record.
IDataReader reader = null;
// DateTime to check if current session item is expired.
DateTime expires;
// String to hold serialized SessionStateItemCollection.
Byte[] serializedItems = new Byte[] { };
// True if a record is found in the database.
Boolean foundRecord = false;
// True if the returned session item is expired and needs to be deleted.
Boolean 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 = conn.CreateCommand();
cmd.CommandText = "UPDATE Sessions SET Locked = @Locked, LockDate = @LockDate WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND Locked = @Locked AND Expires > @Expires";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Locked", DbType.Boolean, true));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockDate", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, ApplicationName));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Locked", DbType.Int32, false));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, 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 = conn.CreateCommand();
cmd.CommandText = "SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout FROM Sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 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;
}
Object val = reader.GetValue(1);
if (val != DBNull.Value)
{
serializedItems = (Byte[])val;
}
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 = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 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 = conn.CreateCommand();
cmd.CommandText = "UPDATE Sessions SET LockId = @LockId, Flags = 0 WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockId", DbType.Int32, lockId));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 255, ApplicationName));
cmd.ExecuteNonQuery();
// If the actionFlags parameter is not InitializeItem,
// deserialize the stored SessionStateItemCollection.
if (actionFlags == SessionStateActions.InitializeItem)
{
item = CreateNewStoreData(context, (int)_config.Timeout.TotalMinutes);
}
else
{
item = Deserialize(context, serializedItems, timeout);
}
}
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetSessionStoreItem");
throw new ProviderException(_exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
conn.Close();
}
return(item);
}
// SessionStateProviderBase.SetAndReleaseItemExclusive
public override void SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)
{
// Serialize the SessionStateItemCollection as a String.
Byte[] sessItems = Serialize((SessionStateItemCollection)item.Items);
IDbConnection conn = new SQLiteConnection(_connectionString);
IDbCommand cmd;
IDbCommand deleteCmd = null;
if (newItem)
{
// SQLiteCommand to clear an existing expired session if it exists.
deleteCmd = conn.CreateCommand();
deleteCmd.CommandText = "DELETE FROM Sessions WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND Expires < @Expires";
deleteCmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
deleteCmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 255, ApplicationName));
deleteCmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now));
// SQLiteCommand to insert the new session item.
cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values(@SessionId, @ApplicationName, @Created, @Expires, @LockDate, @LockId, @Timeout, @Locked, @SessionItems, @Flags)";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, 255, ApplicationName));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Created", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now.AddMinutes((Double)item.Timeout)));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockDate", DbType.DateTime, DateTime.Now));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockId", DbType.Int32, 0));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Timeout", DbType.Int32, item.Timeout));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Locked", DbType.Boolean, false));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionItems", DbType.Binary, sessItems));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Flags", DbType.Int32, 0));
}
else
{
// SQLiteCommand to update the existing session item.
cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Sessions SET Expires = @Expires, SessionItems = @SessionItems, Locked = @Locked WHERE SessionId = @SessionId AND ApplicationName = @ApplicationName AND LockId = @LockId";
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Expires", DbType.DateTime, DateTime.Now.AddMinutes((Double)item.Timeout)));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionItems", DbType.Binary, sessItems));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@Locked", DbType.Boolean, false));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@SessionId", DbType.String, 80, id));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@ApplicationName", DbType.String, ApplicationName));
cmd.Parameters.Add(SQLiteHelper.CreateParameter("@LockId", DbType.Int32, lockId));
}
try
{
conn.Open();
if (deleteCmd != null)
{
deleteCmd.ExecuteNonQuery();
}
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "SetAndReleaseItemExclusive");
throw new ProviderException(_exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}