/// <summary>
/// Returns the number of rows in the underlying data store that exist within the specified scope.
/// </summary>
/// <param name="scope">A PersonalizationScope with the personalization information to be queried. This value cannot be a null reference (Nothing in Visual Basic).</param>
/// <param name="query">A PersonalizationStateQuery containing a query. This value can be a null reference (Nothing in Visual Basic).</param>
/// <returns>The number of rows in the underlying data store that exist for the specified scope parameter.</returns>
public override int GetCountOfState(System.Web.UI.WebControls.WebParts.PersonalizationScope scope, System.Web.UI.WebControls.WebParts.PersonalizationStateQuery query)
{
int totalRecords;
PersonalizationStateInfoCollection col = FindState(scope, query, 0, int.MaxValue, out totalRecords);
return(totalRecords);
}
/// <summary>
/// Finds state matching given parameters
/// </summary>
/// <param name="scope">Scope to search</param>
/// <param name="query">Query to action</param>
/// <param name="pageIndex">Index of page to retrieve</param>
/// <param name="pageSize">Size of page to retrieve</param>
/// <returns>Matching state</returns>
public FindStateResult FindState(PersonalizationScope scope, PersonalizationStateQuery query, int pageIndex, int pageSize)
{
int totalRecords = 0;
PersonalizationStateInfoCollection collection = Provider.FindState(scope, query, pageIndex, pageSize, out totalRecords);
FindStateResult result = new FindStateResult();
result.TotalRecords = totalRecords;
result.StateInfoCollection = collection;
return(result);
}
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)
{
const string findSharedState =
"SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" +
" FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
" WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
const string orderBy = " ORDER BY Paths.Path ASC";
const string findUserState =
"SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Paths Paths" +
" WHERE PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Paths.Path LIKE @Path";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
OleDbDataReader reader = null;
totalRecords = 0;
try {
try {
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand(findSharedState, connection);
OleDbParameterCollection parameters = command.Parameters;
OleDbParameter parameter;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
command.CommandText += " AND Paths.Path LIKE @Path";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
command.CommandText += orderBy;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
long recordCount = 0;
long lBound = pageIndex * pageSize;
long uBound = lBound + pageSize;
while (reader.Read())
{
recordCount++;
if (recordCount <= lBound || recordCount > uBound)
{
continue;
}
string returnedPath = reader.GetString(0);
DateTime lastUpdatedDate = reader.GetDateTime(1);
int size = reader.GetInt32(2);
// Create temp info since we need to retrieve the corresponding personalization size and count later
stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1));
}
totalRecords = (int)recordCount;
// We need to close the reader in order to make other queries
reader.Close();
command = new OleDbCommand(findUserState, connection);
parameters = command.Parameters;
parameters.AddWithValue("ApplicationId", appId);
parameter = parameters.Add("Path", OleDbType.WChar);
PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();
foreach (PersonalizationStateInfo stateInfo in stateInfoCollection)
{
parameter.Value = stateInfo.Path;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
int sizeOfPersonalizations = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture);
int countOfPersonalizations = reader.GetInt32(1);
reader.Close();
sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
stateInfo.Path, stateInfo.LastUpdatedDate,
stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations));
}
return(sharedStateInfoCollection);
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
if (reader != null)
{
reader.Close();
}
}
}
catch {
throw;
}
}
private PersonalizationStateInfoCollection FindUserState(string path,
DateTime inactiveSinceDate,
string username,
int pageIndex,
int pageSize,
out int totalRecords)
{
const string findUserState =
"SELECT Paths.Path, PerUser.LastUpdatedDate, LEN(PerUser.PageSettings), Users.UserName, Users.LastActivityDate" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId";
const string orderBy = " ORDER BY Paths.Path ASC, Users.UserName ASC";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
OleDbDataReader reader = null;
totalRecords = 0;
try {
try {
OleDbParameter parameter;
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
command.CommandText = findUserState;
if (inactiveSinceDate != DateTime.MinValue)
{
command.CommandText += " AND Users.LastActivityDate <= @InactiveSinceDate";
// Note: OleDb provider does not handle datetime that has non-
// zero millisecond, so it needs to be rounded up.
parameter = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
}
if (path != null)
{
command.CommandText += " AND Paths.Path LIKE @Path";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
if (username != null)
{
command.CommandText += " AND Users.UserName LIKE @UserName";
parameter = parameters.Add("UserName", OleDbType.WChar);
parameter.Value = username;
}
command.CommandText += orderBy;
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
long recordCount = 0;
long lBound = pageIndex * pageSize;
long uBound = lBound + pageSize;
while (reader.Read())
{
recordCount++;
if (recordCount <= lBound || recordCount > uBound)
{
continue;
}
string returnedPath = reader.GetString(0);
DateTime lastUpdatedDate = reader.GetDateTime(1);
int size = reader.GetInt32(2);
string returnedUsername = reader.GetString(3);
DateTime lastActivityDate = reader.GetDateTime(4);
stateInfoCollection.Add(new UserPersonalizationStateInfo(
returnedPath, lastUpdatedDate,
size, returnedUsername, lastActivityDate));
}
totalRecords = (int)recordCount;
return(stateInfoCollection);
}
finally {
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
if (reader != null)
{
reader.Close();
}
}
}
catch {
throw;
}
}
public static int ResetState(PersonalizationStateInfoCollection data) {}
/// <summary>
/// Returns a collection containing zero or more PersonalizationStateInfo-derived objects based
/// on scope and specific query parameters.
/// </summary>
/// <param name="query"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public static PersonalizationStateInfoCollection FindState(PersonalizationStateQuery query, int pageIndex, int pageSize, out int totalRecords)
{
PersonalizationStateInfoCollection tempResults = new PersonalizationStateInfoCollection();
//USERNAMES ARE NOT ASSOCIATED WITH SHARED DATA
//IF A USERNAME WAS SPECIFIED, RETURN AN EMPTY COLLECTION
totalRecords = 0;
if (!string.IsNullOrEmpty(query.UsernameToMatch))
{
return(tempResults);
}
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
//CHECK WHETHER TO LOAD ALL PATHS, OR TO FILTER
selectQuery.Append("SELECT " + SharedPersonalization.GetColumnNames("S") + ",P.Path");
selectQuery.Append(" FROM ac_SharedPersonalization S, ac_PersonalizationPaths P");
selectQuery.Append(" WHERE S.PersonalizationPathId = P.PersonalizationPathId AND P.StoreId = @storeId");
if (!string.IsNullOrEmpty(query.PathToMatch))
{
selectQuery.Append(" AND P.Path LIKE @pathToMatch");
}
selectQuery.Append(" ORDER BY P.Path");
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
if (!string.IsNullOrEmpty(query.PathToMatch))
{
database.AddInParameter(selectCommand, "@pathToMatch", System.Data.DbType.String, query.PathToMatch);
}
//EXECUTE THE COMMAND
int startRowIndex = (pageIndex * pageSize);
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((rowCount < pageSize)))
{
if (thisIndex >= startRowIndex)
{
SharedPersonalization p = new SharedPersonalization();
SharedPersonalization.LoadDataReader(p, dr);
SharedPersonalizationStateInfo i = new SharedPersonalizationStateInfo(dr.GetString(5), p.LastUpdatedDate, p.PageSettings.Length, 0, 0);
tempResults.Add(i);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
totalRecords = rowCount;
//LOOP RESULTS AND COMPILE USER STATISICS
int size, count;
PersonalizationStateInfoCollection results = new PersonalizationStateInfoCollection();
foreach (SharedPersonalizationStateInfo i in tempResults)
{
UserPersonalizationDataSource.CountForPath(i.Path, out size, out count);
results.Add(new SharedPersonalizationStateInfo(i.Path, i.LastUpdatedDate, i.Size, size, count));
}
return(results);
}
public static int ResetState(PersonalizationStateInfoCollection data)
{
}
