private int GetCountUserState(string path, DateTime userInactiveSinceDate, string userName)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
return(PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_GetCountOfState(
false, ApplicationName, ApplicationId, path,
userName, userInactiveSinceDate, connection));
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - GetCountUserState");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
private int ResetUserState(ResetUserStateMode mode, DateTime userInactiveSinceDate, string[] paths, string[] usernames)
{
var connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
if (ResetUserStateMode.PerInactiveDate == mode)
{
return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate.ToUniversalTime(), null, null, connection));
}
if (ResetUserStateMode.PerPaths == mode)
{
return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, null, paths, connection));
}
else
{
return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, usernames, paths, connection));
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetUserState");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Deletes personalization state corresponding to a specified user and a specified page from the database.
/// </summary>
/// <param name="webPartManager">The web part manager.</param>
/// <param name="path">The path indicating where to save the data.</param>
/// <param name="userName">The user name.</param>
protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
if (string.IsNullOrEmpty(userName))
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_ResetPageSettings(ApplicationId, path, connection);
}
else
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_ResetPageSettings(ApplicationId, userName, path, DateTime.UtcNow, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetPersonalizationBlob");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Retrieves personalization state as opaque blobs from the data source.
/// </summary>
/// <param name="webPartManager">The web part manager.</param>
/// <param name="path">The path indicating where to save the data.</param>
/// <param name="userName">The user name.</param>
/// <param name="sharedDataBlob">A byte array containing the user shared data to loaded.</param>
/// <param name="userDataBlob">A byte array containing the user data to be loaded.</param>
/// <remarks>Retrieves both shared and user personalization state corresponding to a specified user and a specified page.</remarks>
protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref Byte[] sharedDataBlob, ref Byte[] userDataBlob)
{
sharedDataBlob = null;
userDataBlob = null;
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
sharedDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_GetPageSettings(
ApplicationId, path, connection);
if (!String.IsNullOrEmpty(userName))
{
userDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_GetPageSettings(
ApplicationId, path, userName, DateTime.UtcNow, connection);
}
connection.CloseConnection();
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - LoadPersonazalitionBlobs");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Writes personalization state corresponding to a specified user and a specified page as an opaque blob to the database.
/// </summary>
/// <param name="webPartManager">The web part manager.</param>
/// <param name="path">The path indicating where to save the data.</param>
/// <param name="userName">The user name.</param>
/// <param name="dataBlob">A byte array containing the data to be saved.</param>
/// <remarks>If userName is <c>null</c>, then the personalization state is shared state and is not keyed by user name.</remarks>
protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, Byte[] dataBlob)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
if (!string.IsNullOrEmpty(userName))
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_SetPageSettings(ApplicationId, userName, path, dataBlob, DateTime.UtcNow, connection);
}
else
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_SetPageSettings(ApplicationId, path, dataBlob, DateTime.UtcNow, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - SavePersonalizationBlob");
}
throw;
}
}
/// <summary>
/// Deletes personalization state corresponding to the specified users and specified pages from the database.
/// </summary>
/// <param name="scope">The personalization scope.</param>
/// <param name="paths">The paths indicating where to save the data.</param>
/// <param name="usernames">The user names.</param>
/// <returns></returns>
public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames)
{
bool hasPaths = !(paths == null || paths.Length == 0);
bool hasUsers = !(usernames == null || usernames.Length == 0);
var count = 0;
var connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
if (scope == PersonalizationScope.Shared)
{
try
{
if (paths == null) // reset all state
{
return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection));
}
else
{
return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetSharedState(ApplicationId, paths, connection));
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetState");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
else
{
DateTime dateTime = Convert.ToDateTime("2038-01-19 03:14:07.999999"); // MySQL TimeStamp MaxValue
if (!hasPaths && !hasUsers)
{
count = PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
}
else if (!hasUsers)
{
count = ResetUserState(ResetUserStateMode.PerPaths, dateTime, paths, null);
}
else
{
count = ResetUserState(ResetUserStateMode.PerUsers, dateTime, paths, usernames);
}
}
return(count);
}
private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(true, ApplicationId, ApplicationName, pageIndex, pageSize,
path, null, DateTime.MinValue, connection, ref cmd);
PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string pathQuery = reader.GetString("Path");
DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue :
DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
int size = (reader.IsDBNull(2)) ? 0 : reader.GetInt32("SharedDataLength");
int userDataLength = (reader.IsDBNull(3)) ? 0 : reader.GetInt32("UserDataLength");
int userCount = (reader.IsDBNull(4)) ? 0 : reader.GetInt32("UserCount");
sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
pathQuery, lastUpdatedDate, size, userDataLength, userCount));
}
}
connection.CloseConnection();
return(sharedStateInfoCollection);
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - FindSharedState");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
private PersonalizationStateInfoCollection FindUserState(string path, DateTime inactiveSinceDate, string userName, int pageIndex, int pageSize, out int totalRecords)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(false, ApplicationId, ApplicationName, pageIndex, pageSize,
path, userName, inactiveSinceDate, connection, ref cmd);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string pathQuery = reader.GetString("Path");
DateTime lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime("LastUpdatedDate"), DateTimeKind.Utc);
int size = reader.GetInt32("Size");
string usernameQuery = reader.GetString("name");
DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime("LastActivityDate"), DateTimeKind.Utc);
stateInfoCollection.Add(new UserPersonalizationStateInfo(pathQuery, lastActivityDate, size, usernameQuery, lastActivityDate));
}
}
connection.CloseConnection();
return(stateInfoCollection);
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
{
WriteToEventLog(ex, "MySQLPersonalizationProvider - FindUserState");
}
throw;
}
finally
{
connection.CloseConnection();
}
}
private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(true, ApplicationId, ApplicationName, pageIndex, pageSize,
path, null, DateTime.MinValue, connection, ref cmd);
PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string pathQuery = reader.GetString("Path");
DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue :
DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
int size = (reader.IsDBNull(2)) ? 0 : reader.GetInt32("SharedDataLength");
int userDataLength = (reader.IsDBNull(3)) ? 0 : reader.GetInt32("UserDataLength");
int userCount = (reader.IsDBNull(4)) ? 0 : reader.GetInt32("UserCount");
sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
pathQuery, lastUpdatedDate, size, userDataLength, userCount));
}
}
connection.CloseConnection();
return sharedStateInfoCollection;
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - FindSharedState");
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Deletes personalization state corresponding to the specified users and specified pages from the database
/// </summary>
/// <param name="scope"></param>
/// <param name="paths"></param>
/// <param name="usernames"></param>
/// <returns></returns>
public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames)
{
bool hasPaths = !(paths == null || paths.Length == 0);
bool hasUsers = !(usernames == null || usernames.Length == 0);
var count = 0;
var connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
if (scope == PersonalizationScope.Shared)
{
try
{
if (paths == null) // reset all state
{
return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
}
else
{
return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetSharedState(ApplicationId, paths, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetState");
throw;
}
finally
{
connection.CloseConnection();
}
}
else
{
if (!hasPaths && !hasUsers)
{
count = PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
}
else if (!hasUsers)
{
count = ResetUserState(ResetUserStateMode.PerPaths, DateTime.MaxValue, paths, null);
}
else
{
count = ResetUserState(ResetUserStateMode.PerUsers, DateTime.MaxValue, paths, usernames);
}
}
return count;
}
/// <summary>
/// Writes personalization state corresponding to a specified user and a specified page as an opaque blob to the database.
/// If userName is null, then the personalization state is shared state and is not keyed by user name.
/// </summary>
/// <param name="webPartManager"></param>
/// <param name="path"></param>
/// <param name="userName"></param>
/// <param name="dataBlob"></param>
protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, Byte[] dataBlob)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
if (!string.IsNullOrEmpty(userName))
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_SetPageSettings(ApplicationId, userName, path, dataBlob, DateTime.UtcNow, connection);
}
else
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_SetPageSettings(ApplicationId, path, dataBlob, DateTime.UtcNow, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - SavePersonalizationBlob");
throw;
}
}
internal static Byte[] my_aspnet_PersonalizationPerUser_GetPageSettings(long applicationId, string path, string userName, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return null;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
//get pathid
var cmd = new MySqlCommand("select pathId from my_aspnet_paths where applicationid = @ApplicationId and loweredpath = lower(@Path)", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (cmd.ExecuteScalar() ?? "").ToString();
if (string.IsNullOrEmpty(pathId))
return null;
cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (cmd.ExecuteScalar() ?? "").ToString();
userId = string.IsNullOrEmpty(userId) ? "0" : userId;
if (int.Parse(userId) == 0)
return null;
UpdateUserLastActiveDate(connection.Connection, int.Parse(userId), currentTimeUtc);
cmd = new MySqlCommand("select pagesettings from my_aspnet_personalizationperuser as peruser where peruser.pathid = @PathId and peruser.userid = @UserId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@UserId", userId);
var reader = cmd.ExecuteReader();
byte[] settings = null;
while (reader.Read())
{
int size = (int)reader.GetBytes(0, 0, null, 0, 0);
settings = new byte[size];
reader.GetBytes(0, 0, settings, 0, size);
}
reader.Close();
return settings;
}
/// <summary>
/// Deletes personalization state corresponding to a specified user and a specified page from the database.
/// </summary>
/// <param name="webPartManager"></param>
/// <param name="path"></param>
/// <param name="userName"></param>
protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
if (string.IsNullOrEmpty(userName))
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_ResetPageSettings(ApplicationId, path, connection);
}
else
{
PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_ResetPageSettings(ApplicationId, userName, path, DateTime.UtcNow, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetPersonalizationBlob");
throw;
}
finally
{
connection.CloseConnection();
}
}
private int ResetUserState(ResetUserStateMode mode, DateTime userInactiveSinceDate, string[] paths, string[] usernames)
{
var connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
if (ResetUserStateMode.PerInactiveDate == mode)
{
return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate.ToUniversalTime(), null, null, connection);
}
if (ResetUserStateMode.PerPaths == mode)
{
return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, null, paths, connection);
}
else
{
return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, usernames, paths, connection);
}
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetUserState");
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Saves per-user state for the specified page and the specified user in the my_aspnet_PersonalizationPerUser table.
/// </summary>
/// <returns></returns>
internal static int my_aspnet_PersonalizationPerUser_SetPageSettings(long applicationId, string userName, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (pathId == null)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@Path", path);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT id FROM my_aspnet_users WHERE ApplicationId = @ApplicationId AND name = LOWER(@UserName)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (cmd.ExecuteScalar() ?? "").ToString();
userId = string.IsNullOrEmpty(userId) ? "0" : userId;
// create user
if (int.Parse(userId) == 0)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Parameters.Clear();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_users (applicationId, name, isAnonymous, lastActivityDate) values (@ApplicationId, @UserName, false, @CurrentTimeUtc)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT Id from my_aspnet_users where applicationId = @ApplicationId and name = @UserName)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
userId = (string)cmd.ExecuteScalar();
}
var rows = UpdateUserLastActiveDate(connection.Connection, int.Parse(userId), DateTime.UtcNow);
if (rows == 0)
throw new Exception("User not found");
cmd.Parameters.Clear();
cmd.CommandText = "Select COUNT(*) from my_aspnet_personalizationperuser where userid = @UserId and pathId = @PathId";
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
if ((long)cmd.ExecuteScalar() > 0)
{
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE my_aspnet_personalizationperuser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc " +
"where userid = @UserId and pathId = @PathId";
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
cmd.ExecuteNonQuery();
}
else {
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO my_aspnet_personalizationperuser(applicationId, pathId, userId, pageSettings, lastUpdatedDate) VALUES(@applicationId, @PathId, @userId, @PageSettings, @LastUpdatedDate)";
cmd.Parameters.AddWithValue("@applicationId", applicationId);
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
rows = cmd.ExecuteNonQuery();
}
return rows;
}
internal static Byte[] my_aspnet_PersonalizationPerUser_GetPageSettings(long applicationId, string path, string userName, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(null);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
//get pathid
var cmd = new MySqlCommand("select pathId from my_aspnet_paths where applicationid = @ApplicationId and loweredpath = lower(@Path)", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (cmd.ExecuteScalar() ?? "").ToString();
if (string.IsNullOrEmpty(pathId))
{
return(null);
}
cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (cmd.ExecuteScalar() ?? "").ToString();
userId = string.IsNullOrEmpty(userId) ? "0" : userId;
if (int.Parse(userId, CultureInfo.InvariantCulture) == 0)
{
return(null);
}
UpdateUserLastActiveDate(connection.Connection, int.Parse(userId, CultureInfo.InvariantCulture), currentTimeUtc);
cmd = new MySqlCommand("select pagesettings from my_aspnet_personalizationperuser as peruser where peruser.pathid = @PathId and peruser.userid = @UserId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@UserId", userId);
var reader = cmd.ExecuteReader();
byte[] settings = null;
while (reader.Read())
{
int size = (int)reader.GetBytes(0, 0, null, 0, 0);
settings = new byte[size];
reader.GetBytes(0, 0, settings, 0, size);
}
reader.Close();
return(settings);
}
internal static void my_aspnet_PersonalizationPerUser_ResetPageSettings(long applicationId, string userName, string path, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return;
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(pathId))
{
cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (int)cmd.ExecuteScalar();
if (userId != 0)
{
var rows = UpdateUserLastActiveDate(connection.Connection, userId, currentTimeUtc);
if (rows != 0)
{
cmd = new MySqlCommand("delete from my_aspnet_personalizationperuser WHERE pathId = @PathId AND userId = @UserId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Retrieves profile data from my_aspnet_PersonalizationAllUsers or my_aspnet_PersonalizationPerUser meeting several input criteria.
/// </summary>
internal static int myaspnet_PersonalizationAdministration_FindState(bool allUsersScope, long applicationId, string applicationName, int pageIndex, int pageSize, string path, string userName,
DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection, ref MySqlCommand findStateCommand)
{
// create memory table to store results
var sql = "CREATE TEMPORARY TABLE IF NOT EXISTS pageIndexResults(" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"ItemId int not null)";
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand(sql, connection.Connection);
cmd.ExecuteNonQuery();
//make sure table is empty
cmd.CommandText = "TRUNCATE TABLE pageIndexResults";
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageSize - 1 + pageLowerBound;
if (allUsersScope)
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT myaspnet_Paths.PathId " +
"FROM myaspnet_Paths, " +
"((SELECT aspnet_Paths.PathId " +
"FROM myaspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE myaspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT DISTINCT aspnet_Paths.PathId " +
"FROM my_aspnet_personalizationperuser, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE my_aspnet_Paths.PathId = SharedDataPerPath.PathId OR my_aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC)";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(PathId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
int totalRecords = (int)cmd.ExecuteScalar();
query = "SELECT my_aspnet_Paths.Path, " +
"SharedDataPerPath.LastUpdatedDate, " +
"SharedDataPerPath.SharedDataLength, " +
"UserDataPerPath.UserDataLength, " +
"UserDataPerPath.UserCount " +
"FROM aspnet_Paths, " +
"((SELECT pageIndexResults.ItemId AS PathId, " +
"aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
"LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
"FROM my_aspnet_personalizationallusers, PageIndex " +
"WHERE my_aspnet_personalizationallusers.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT pageIndexResults.ItemId AS PathId, " +
"SUM(LENGTH(my_aspnet_personalizationperuser.PageSettings)) AS UserDataLength, " +
"COUNT(*) AS UserCount " +
"FROM my_aspnet_personalizationperuser, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
"GROUP BY pageIndexResults.IndexId " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC ";
findStateCommand.CommandText = query;
findStateCommand.Connection = connection.Connection;
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
return totalRecords;
}
else
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT my_aspnet_personalizationperuser.Id " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_Users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
"AND (@UserName IS NULL OR my_aspnet_users.name LIKE @UserName) " +
"AND (@InactiveSinceDate IS NULL OR my_aspnet_users.LastActivityDate <= @InactiveSinceDate) " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC )";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(IndexId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
var totalRecords = cmd.ExecuteScalar().ToString();
query = "SELECT my_aspnet_Paths.Path, my_aspnet_personalizationperuser.LastUpdatedDate, LENGTH(my_aspnet_personalizationperuser.PageSettings) as Size, my_aspnet_Users.Name, my_aspnet_Users.LastActivityDate " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.Id = PageIndexResults.IndexId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_paths.PathId " +
"AND pageIndexResults.ItemId >= @PageLowerBound AND PageIndexResults.ItemId <= @PageUpperBound " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC ";
findStateCommand.CommandText = query;
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Connection = connection.Connection;
return int.Parse(totalRecords);
}
}
internal static void my_aspnet_PersonalizationAllUsers_ResetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (cmd.ExecuteScalar() ?? "").ToString();
if (!string.IsNullOrEmpty(pathId))
{
cmd = new MySqlCommand("delete my_aspnet_personalizationallusers.* from my_aspnet_personalizationallusers WHERE pathId = @PathId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.ExecuteNonQuery();
}
}
internal static int my_aspnet_PersonalizationAdministration_ResetUserState(long applicationId, DateTime inactiveSinceDate,string[] usernames, string[] paths, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var rows = 0;
var cmd = new MySqlCommand();
if (usernames == null) usernames = new string[1] { "" };
if (paths == null ) paths = new string[1] { "" };
foreach (var username in usernames)
{
foreach (var path in paths)
{
var query = "DELETE peruser.* FROM (my_aspnet_personalizationperuser as peruser " +
"INNER JOIN my_aspnet_users as users ON " +
"peruser.userid = users.id) " +
"INNER JOIN my_aspnet_paths as paths ON " +
"paths.applicationId = @ApplicationId AND " +
"paths.pathid = peruser.pathid AND " +
"(@InactiveSinceDate is null OR users.lastactivitydate <= @InactiveSinceDate) ";
query = string.IsNullOrEmpty(username) ? query : query += " AND (@UserName is null OR users.name = @UserName) ";
query = string.IsNullOrEmpty(path) ? query : query += " AND (@Path is null OR paths.loweredpath = LOWER(@Path))";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Connection = connection.Connection;
rows += cmd.ExecuteNonQuery();
}
}
return rows;
}
internal static int my_aspnet_PersonalizationAdministration_ResetSharedState(long applicationId, string[] paths, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
if (paths == null)
return 0;
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
var rows = 0;
foreach (var path in paths)
{
cmd.CommandText = "DELETE my_aspnet_personalizationallusers.* FROM my_aspnet_personalizationallusers " +
"INNER JOIN my_aspnet_paths as paths ON " +
"((paths.ApplicationId = @ApplicationId AND " +
"my_aspnet_personalizationallusers.PathId = paths.PathId) AND " +
"paths.loweredpath = LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
rows += cmd.ExecuteNonQuery();
}
return rows;
}
/// <summary>
/// Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table
/// </summary>
/// <returns></returns>
internal static int my_aspnet_PersonalizationAllUsers_SetPageSettings(long applicationId, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (pathId == null)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@Path", path);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
cmd.CommandText = "INSERT INTO my_aspnet_personalizationallusers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)";
cmd.CommandText += " ON DUPLICATE KEY UPDATE PageSettings=Values(PageSettings), LastUpdatedDate=Values(LastUpdatedDate)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", currentTimeUtc);
var rows = cmd.ExecuteNonQuery();
return rows;
}
private PersonalizationStateInfoCollection FindUserState(string path, DateTime inactiveSinceDate, string userName, int pageIndex, int pageSize, out int totalRecords)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(false, ApplicationId, ApplicationName, pageIndex, pageSize,
path, userName, inactiveSinceDate, connection, ref cmd);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
using(var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string pathQuery = reader.GetString("Path");
DateTime lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime("LastUpdatedDate"), DateTimeKind.Utc);
int size = reader.GetInt32("Size");
string usernameQuery = reader.GetString("name");
DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime("LastActivityDate"), DateTimeKind.Utc);
stateInfoCollection.Add(new UserPersonalizationStateInfo(pathQuery, lastActivityDate, size, usernameQuery, lastActivityDate));
}
}
connection.CloseConnection();
return stateInfoCollection;
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - FindUserState");
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Retrieves profile data from my_aspnet_PersonalizationAllUsers or my_aspnet_PersonalizationPerUser meeting several input criteria.
/// </summary>
internal static int myaspnet_PersonalizationAdministration_FindState(bool allUsersScope, long applicationId, string applicationName, int pageIndex, int pageSize, string path, string userName,
DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection, ref MySqlCommand findStateCommand)
{
// create memory table to store results
var sql = "CREATE TEMPORARY TABLE IF NOT EXISTS pageIndexResults(" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"ItemId int not null)";
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand(sql, connection.Connection);
cmd.ExecuteNonQuery();
//make sure table is empty
cmd.CommandText = "TRUNCATE TABLE pageIndexResults";
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageSize - 1 + pageLowerBound;
if (allUsersScope)
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT myaspnet_Paths.PathId " +
"FROM myaspnet_Paths, " +
"((SELECT aspnet_Paths.PathId " +
"FROM myaspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE myaspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT DISTINCT aspnet_Paths.PathId " +
"FROM my_aspnet_personalizationperuser, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE my_aspnet_Paths.PathId = SharedDataPerPath.PathId OR my_aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC)";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(PathId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
int totalRecords = (int)cmd.ExecuteScalar();
query = "SELECT my_aspnet_Paths.Path, " +
"SharedDataPerPath.LastUpdatedDate, " +
"SharedDataPerPath.SharedDataLength, " +
"UserDataPerPath.UserDataLength, " +
"UserDataPerPath.UserCount " +
"FROM aspnet_Paths, " +
"((SELECT pageIndexResults.ItemId AS PathId, " +
"aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
"LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
"FROM my_aspnet_personalizationallusers, PageIndex " +
"WHERE my_aspnet_personalizationallusers.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT pageIndexResults.ItemId AS PathId, " +
"SUM(LENGTH(my_aspnet_personalizationperuser.PageSettings)) AS UserDataLength, " +
"COUNT(*) AS UserCount " +
"FROM my_aspnet_personalizationperuser, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.PathId = pageIndexResults.IndexId " +
"AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
"GROUP BY pageIndexResults.IndexId " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY my_aspnet_Paths.Path ASC ";
findStateCommand.CommandText = query;
findStateCommand.Connection = connection.Connection;
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
return(totalRecords);
}
else
{
var query = "INSERT INTO pageIndexResults (ItemId) (" +
"SELECT my_aspnet_personalizationperuser.Id " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths " +
"WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_Users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_Paths.PathId " +
"AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
"AND (@UserName IS NULL OR my_aspnet_users.name LIKE @UserName) " +
"AND (@InactiveSinceDate IS NULL OR my_aspnet_users.LastActivityDate <= @InactiveSinceDate) " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC )";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Connection = connection.Connection;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT Count(IndexId) FROM pageIndexResults";
cmd.Connection = connection.Connection;
var totalRecords = cmd.ExecuteScalar().ToString();
query = "SELECT my_aspnet_Paths.Path, my_aspnet_personalizationperuser.LastUpdatedDate, LENGTH(my_aspnet_personalizationperuser.PageSettings) as Size, my_aspnet_Users.Name, my_aspnet_Users.LastActivityDate " +
"FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths, pageIndexResults " +
"WHERE my_aspnet_personalizationperuser.Id = PageIndexResults.IndexId " +
"AND my_aspnet_personalizationperuser.UserId = my_aspnet_users.Id " +
"AND my_aspnet_personalizationperuser.PathId = my_aspnet_paths.PathId " +
"AND pageIndexResults.ItemId >= @PageLowerBound AND PageIndexResults.ItemId <= @PageUpperBound " +
"ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC ";
findStateCommand.CommandText = query;
findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
findStateCommand.Connection = connection.Connection;
return(int.Parse(totalRecords, CultureInfo.InvariantCulture));
}
}
private int GetCountUserState(string path, DateTime userInactiveSinceDate, string userName)
{
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
try
{
MySqlCommand cmd = new MySqlCommand();
connection.OpenConnection(true);
return PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_GetCountOfState(
false, ApplicationName, ApplicationId, path,
userName, userInactiveSinceDate, connection);
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - GetCountUserState");
throw;
}
finally
{
connection.CloseConnection();
}
}
internal static void my_aspnet_PersonalizationPerUser_ResetPageSettings(long applicationId, string userName, string path, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(pathId))
{
cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (int)cmd.ExecuteScalar();
if (userId != 0)
{
var rows = UpdateUserLastActiveDate(connection.Connection, userId, currentTimeUtc);
if (rows != 0)
{
cmd = new MySqlCommand("delete from my_aspnet_personalizationperuser WHERE pathId = @PathId AND userId = @UserId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.ExecuteNonQuery();
}
}
}
}
internal static int myaspnet_PersonalizationAdministration_GetCountOfState(bool allUsersScope, string applicationName, long applicationId, string path, string userName, DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
if (allUsersScope)
{
MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationallusers, my_aspnet_paths " +
"where my_aspnet_paths.applicationId = @ApplicationId and " +
"my_aspnet_personalizationallusers.pathid = my_aspnet_paths.pathid and " +
"(@Path is null or my_aspnet_paths.loweredpath like lower(@Path))", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Connection = connection.Connection;
var count = cmd.ExecuteScalar().ToString();
return(int.Parse(count, CultureInfo.InvariantCulture));
}
else
{
MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationperuser as peruser, my_aspnet_users as users, " +
"my_aspnet_paths as paths " +
"where paths.applicationId = @ApplicationId and " +
"peruser.userid = users.id and " +
"peruser.pathId = paths.pathId and " +
"(@Path is null or paths.loweredpath like lower(@Path) and " +
"(@UserName is null or users.name like lower(@UserName))) and " +
"(@InactiveSinceDate is null or users.lastactivitydate <= @InactiveSinceDate) ", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Connection = connection.Connection;
var count = cmd.ExecuteScalar().ToString();
return(int.Parse(count, CultureInfo.InvariantCulture));
}
}
internal static int my_aspnet_PersonalizationAdministration_DeleteAllState(bool allUsersScope, long applicationId, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
if (allUsersScope)
{
cmd.CommandText = "DELETE FROM my_aspnet_personalizationallusers " +
"WHERE PathId IN " +
"(SELECT paths.PathId FROM my_aspnet_paths as paths " +
"WHERE paths.ApplicationId = @ApplicationId)";
}
else
{
cmd.CommandText = "DELETE FROM my_aspnet_personalizationperuser " +
"WHERE PathId IN " +
"(SELECT Paths.PathId FROM my_aspnet_paths Paths " +
"WHERE Paths.ApplicationId = @ApplicationId)";
}
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
var rows = cmd.ExecuteNonQuery();
return rows;
}
internal static byte[] my_aspnet_PersonalizationAllUsers_GetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(null);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(pathId))
{
cmd.CommandText = "Select PageSettings from my_aspnet_personalizationallusers where pathId = @PathId";
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Connection = connection.Connection;
var reader = cmd.ExecuteReader();
byte[] settings = null;
while (reader.Read())
{
int size = (int)reader.GetBytes(0, 0, null, 0, 0);
settings = new byte[size];
reader.GetBytes(0, 0, settings, 0, size);
}
reader.Close();
return(settings);
}
return(null);
}
internal static int my_aspnet_PersonalizationAdministration_ResetSharedState(long applicationId, string[] paths, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
if (paths == null)
{
return(0);
}
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
var rows = 0;
foreach (var path in paths)
{
cmd.CommandText = "DELETE my_aspnet_personalizationallusers.* FROM my_aspnet_personalizationallusers " +
"INNER JOIN my_aspnet_paths as paths ON " +
"((paths.ApplicationId = @ApplicationId AND " +
"my_aspnet_personalizationallusers.PathId = paths.PathId) AND " +
"paths.loweredpath = LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
rows += cmd.ExecuteNonQuery();
}
return(rows);
}
internal static void my_aspnet_PersonalizationAllUsers_ResetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return;
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (cmd.ExecuteScalar() ?? "").ToString();
if (!string.IsNullOrEmpty(pathId))
{
cmd = new MySqlCommand("delete my_aspnet_personalizationallusers.* from my_aspnet_personalizationallusers WHERE pathId = @PathId");
cmd.Connection = connection.Connection;
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Saves per-user state for the specified page and the specified user in the my_aspnet_PersonalizationPerUser table.
/// </summary>
/// <returns></returns>
internal static int my_aspnet_PersonalizationPerUser_SetPageSettings(long applicationId, string userName, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (pathId == null)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@Path", path);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT id FROM my_aspnet_users WHERE ApplicationId = @ApplicationId AND name = LOWER(@UserName)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
var userId = (cmd.ExecuteScalar() ?? "").ToString();
userId = string.IsNullOrEmpty(userId) ? "0" : userId;
// create user
if (int.Parse(userId, CultureInfo.InvariantCulture) == 0)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Parameters.Clear();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_users (applicationId, name, isAnonymous, lastActivityDate) values (@ApplicationId, @UserName, false, @CurrentTimeUtc)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT Id from my_aspnet_users where applicationId = @ApplicationId and name = @UserName)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@UserName", userName);
userId = (string)cmd.ExecuteScalar();
}
var rows = UpdateUserLastActiveDate(connection.Connection, int.Parse(userId, CultureInfo.InvariantCulture), DateTime.UtcNow);
if (rows == 0)
{
throw new Exception("User not found");
}
cmd.Parameters.Clear();
cmd.CommandText = "Select COUNT(*) from my_aspnet_personalizationperuser where userid = @UserId and pathId = @PathId";
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
if ((long)cmd.ExecuteScalar() > 0)
{
cmd.Parameters.Clear();
cmd.CommandText = "UPDATE my_aspnet_personalizationperuser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc " +
"where userid = @UserId and pathId = @PathId";
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
cmd.ExecuteNonQuery();
}
else
{
cmd.Parameters.Clear();
cmd.CommandText = "INSERT INTO my_aspnet_personalizationperuser(applicationId, pathId, userId, pageSettings, lastUpdatedDate) VALUES(@applicationId, @PathId, @userId, @PageSettings, @LastUpdatedDate)";
cmd.Parameters.AddWithValue("@applicationId", applicationId);
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
rows = cmd.ExecuteNonQuery();
}
return(rows);
}
internal static int my_aspnet_PersonalizationAdministration_ResetUserState(long applicationId, DateTime inactiveSinceDate, string[] usernames, string[] paths, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var rows = 0;
var cmd = new MySqlCommand();
if (usernames == null)
{
usernames = new string[1] {
""
}
}
;
if (paths == null)
{
paths = new string[1] {
""
}
}
;
foreach (var username in usernames)
{
foreach (var path in paths)
{
var query = "DELETE peruser.* FROM (my_aspnet_personalizationperuser as peruser " +
"INNER JOIN my_aspnet_users as users ON " +
"peruser.userid = users.id) " +
"INNER JOIN my_aspnet_paths as paths ON " +
"paths.applicationId = @ApplicationId AND " +
"paths.pathid = peruser.pathid AND " +
"(@InactiveSinceDate is null OR users.lastactivitydate <= @InactiveSinceDate) ";
query = string.IsNullOrEmpty(username) ? query : query += " AND (@UserName is null OR users.name = @UserName) ";
query = string.IsNullOrEmpty(path) ? query : query += " AND (@Path is null OR paths.loweredpath = LOWER(@Path))";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Connection = connection.Connection;
rows += cmd.ExecuteNonQuery();
}
}
return(rows);
}
internal static byte[] my_aspnet_PersonalizationAllUsers_GetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return null;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId and paths.loweredPath = lower(@Path)", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(pathId))
{
cmd.CommandText = "Select PageSettings from my_aspnet_personalizationallusers where pathId = @PathId";
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Connection = connection.Connection;
var reader = cmd.ExecuteReader();
byte[] settings = null;
while (reader.Read())
{
int size = (int)reader.GetBytes(0, 0, null, 0, 0);
settings = new byte[size];
reader.GetBytes(0, 0, settings, 0, size);
}
reader.Close();
return settings;
}
return null;
}
internal static int my_aspnet_PersonalizationAdministration_DeleteAllState(bool allUsersScope, long applicationId, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
if (allUsersScope)
{
cmd.CommandText = "DELETE FROM my_aspnet_personalizationallusers " +
"WHERE PathId IN " +
"(SELECT paths.PathId FROM my_aspnet_paths as paths " +
"WHERE paths.ApplicationId = @ApplicationId)";
}
else
{
cmd.CommandText = "DELETE FROM my_aspnet_personalizationperuser " +
"WHERE PathId IN " +
"(SELECT Paths.PathId FROM my_aspnet_paths Paths " +
"WHERE Paths.ApplicationId = @ApplicationId)";
}
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
var rows = cmd.ExecuteNonQuery();
return(rows);
}
/// <summary>
/// Retrieves personalization state as opaque blobs from the data source. Retrieves both shared and user personalization state corresponding to a specified user and a specified page.
/// </summary>
/// <param name="webPartManager"></param>
/// <param name="path"></param>
/// <param name="userName"></param>
/// <param name="sharedDataBlob"></param>
/// <param name="userDataBlob"></param>
protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref Byte[] sharedDataBlob, ref Byte[] userDataBlob)
{
sharedDataBlob = null;
userDataBlob = null;
MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
connection.OpenConnection(true);
try
{
sharedDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_GetPageSettings(
ApplicationId, path, connection);
if (!String.IsNullOrEmpty(userName))
{
userDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_GetPageSettings(
ApplicationId, path, userName, DateTime.UtcNow, connection);
}
connection.CloseConnection();
}
catch (Exception ex)
{
if (writeExceptionsToEventLog)
WriteToEventLog(ex, "MySQLPersonalizationProvider - LoadPersonazalitionBlobs");
throw;
}
finally
{
connection.CloseConnection();
}
}
/// <summary>
/// Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table
/// </summary>
/// <returns></returns>
internal static int my_aspnet_PersonalizationAllUsers_SetPageSettings(long applicationId, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
{
return(0);
}
if (!connection.Opened)
{
throw new Exception("Error: Connection should be open");
}
var cmd = new MySqlCommand();
cmd.Connection = connection.Connection;
cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
var pathId = (string)cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (pathId == null)
{
// create path
MySqlTransaction trans;
trans = connection.Connection.BeginTransaction();
try
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@Path", path);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
cmd.CommandText = "INSERT INTO my_aspnet_personalizationallusers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)";
cmd.CommandText += " ON DUPLICATE KEY UPDATE PageSettings=Values(PageSettings), LastUpdatedDate=Values(LastUpdatedDate)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@PageSettings", settings);
cmd.Parameters.AddWithValue("@PathId", pathId);
cmd.Parameters.AddWithValue("@CurrentTimeUtc", currentTimeUtc);
var rows = cmd.ExecuteNonQuery();
return(rows);
}
internal static int myaspnet_PersonalizationAdministration_GetCountOfState(bool allUsersScope, string applicationName, long applicationId, string path, string userName, DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection)
{
if (applicationId <= 0)
return 0;
if (!connection.Opened)
throw new Exception("Error: Connection should be open");
if (allUsersScope)
{
MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationallusers, my_aspnet_paths " +
"where my_aspnet_paths.applicationId = @ApplicationId and " +
"my_aspnet_personalizationallusers.pathid = my_aspnet_paths.pathid and " +
"(@Path is null or my_aspnet_paths.loweredpath like lower(@Path))", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Connection = connection.Connection;
var count = cmd.ExecuteScalar().ToString();
return int.Parse(count);
}
else {
MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationperuser as peruser, my_aspnet_users as users, "+
"my_aspnet_paths as paths " +
"where paths.applicationId = @ApplicationId and " +
"peruser.userid = users.id and " +
"peruser.pathId = paths.pathId and " +
"(@Path is null or paths.loweredpath like lower(@Path) and " +
"(@UserName is null or users.name like lower(@UserName))) and " +
"(@InactiveSinceDate is null or users.lastactivitydate <= @InactiveSinceDate) ", connection.Connection);
cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
cmd.Parameters.AddWithValue("@Path", path);
cmd.Parameters.AddWithValue("@UserName", userName);
cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
cmd.Connection = connection.Connection;
var count = cmd.ExecuteScalar().ToString();
return int.Parse(count);
}
}