//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override string[] GetUsersInRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
StringCollection sc = new StringCollection();
String[] strReturn;
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbDataReader reader = null;
OleDbConnection connection = holder.Connection;
try
{
try
{
int appId = GetApplicationId(holder);
int roleId = GetRoleId(connection, appId, roleName);
OleDbCommand command;
if (roleId == 0)
{
throw new ProviderException("Role not found: " + roleName);
}
command = new OleDbCommand(@"SELECT UserName " +
@"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
@"WHERE ur.RoleId = @RoleId AND ur.UserId = u.UserId " +
@"ORDER BY UserName", connection);
command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
sc.Add(reader.GetString(0));
}
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch
{
throw;
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
internal static Exception GetBetterException(Exception e, AccessConnectionHolder holder)
{
try
{
if (!(e is OleDbException) || holder.Connection == null ||
holder.Connection.DataSource == null || holder.Connection.DataSource.Length < 1)
{
return(e);
}
if (!File.Exists(holder.Connection.DataSource))
{
return(new FileNotFoundException(String.Empty, holder.Connection.DataSource, e));
}
}
finally
{
if (holder.Connection != null)
{
holder.Connection.Close();
}
}
FileStream s = null;
Exception eWrite = null;
try
{
s = File.OpenWrite(holder.Connection.DataSource);
}
catch (Exception except)
{
eWrite = except;
}
finally
{
if (s != null)
{
s.Close();
}
}
if (eWrite != null && (eWrite is UnauthorizedAccessException))
{
HttpContext context = HttpContext.Current;
if (context != null)
{
context.Response.Clear();
context.Response.StatusCode = 500;
context.Response.Write("Cannot write to DB File");
context.Response.End();
}
return(new Exception("AccessFile is not writtable", eWrite));
}
return(e);
}
protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob)
{
sharedDataBlob = null;
userDataBlob = null;
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
int applicationID = GetApplicationID(connectionHolder);
if (applicationID != 0)
{
int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path);
if (pathID != 0)
{
string sharedDataValue = LoadPersonalizationBlob(connection, pathID);
sharedDataBlob = Deserialize(sharedDataValue);
if (userName != null)
{
int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName);
if (userID != 0)
{
string userDataValue = LoadPersonalizationBlob(connection, pathID, userID);
userDataBlob = Deserialize(userDataValue);
}
}
}
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
}
protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob)
{
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
try
{
try
{
string blobValue = Serialize(dataBlob);
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
int applicationID = GetApplicationID(connectionHolder);
if (applicationID != 0)
{
int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path, /* createIfNeeded */ true);
if (pathID != 0)
{
if (String.IsNullOrEmpty(userName))
{
SavePersonalizationBlob(connection, pathID, blobValue);
}
else
{
int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName, /* createIfNeeded */ true);
if (userID != 0)
{
SavePersonalizationBlob(connection, pathID, userID, blobValue);
}
}
}
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
public override int DeleteProfiles(string[] usernames)
{
SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
try
{
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
int numDeleted = 0;
bool fBeginTransCalled = false;
try
{
OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = true;
int appId = GetApplicationId(holder);
foreach (string username in usernames)
{
if (DeleteProfile(holder, username, appId))
{
numDeleted++;
}
}
cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = false;
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (fBeginTransCalled)
{
try
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
command.ExecuteNonQuery();
}
catch { }
}
holder.Close();
}
return(numDeleted);
}
catch
{
throw;
}
}
protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
{
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
int applicationID = GetApplicationID(connectionHolder);
if (applicationID != 0)
{
int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path);
if (pathID != 0)
{
if (String.IsNullOrEmpty(userName))
{
ResetPersonalizationBlob(connection, pathID);
}
else
{
int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName);
if (userID != 0)
{
ResetPersonalizationBlob(connection, pathID, userID);
}
}
}
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private bool DeleteProfile(AccessConnectionHolder holder, string username, int appId)
{
SecUtility.CheckParameter(ref username, true, true, true, 255, "username");
int userId = AccessConnectionHelper.GetUserID(holder.Connection, appId, username, false);
if (userId == 0)
{
return(false);
}
OleDbCommand cmd = new OleDbCommand(@"DELETE FROM aspnet_Profile WHERE UserId = @UserId", holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
return(cmd.ExecuteNonQuery() != 0);
}
private AccessConnectionHolder GetConnectionHolder()
{
OleDbConnection connection = null;
AccessConnectionHolder connectionHolder = AccessConnectionHelper.GetConnection(_databaseFileName, true);
if (connectionHolder != null)
{
connection = connectionHolder.Connection;
}
if (connection == null)
{
throw new ProviderException("PersonalizationProvider cannot access: " + Name);
}
return(connectionHolder);
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override string[] GetAllRoles()
{
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbConnection connection = holder.Connection;
OleDbDataReader reader = null;
try
{
try
{
int appId = GetApplicationId(holder);
OleDbCommand command;
StringCollection sc = new StringCollection();
String[] strReturn = null;
command = new OleDbCommand(@"SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = @AppId ORDER BY RoleName", connection);
command.Parameters.Add(new OleDbParameter("@AppId", appId));
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
sc.Add(reader.GetString(0));
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch
{
throw;
}
}
private int ResetAllState(string getStateCountQuery, string deleteStateQuery)
{
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
// Get the count of records that would be deleted
OleDbCommand command = new OleDbCommand(getStateCountQuery, connection);
object lookupResult = command.ExecuteScalar();
if ((lookupResult != null) && (lookupResult is Int32))
{
count = (Int32)lookupResult;
}
// Do the actual deletion
command.CommandText = deleteStateQuery;
command.ExecuteNonQuery();
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private int GetApplicationId(AccessConnectionHolder holder)
{
if (_ApplicationId != 0 && holder.CreateDate < _ApplicationIDCacheDate) // Already cached?
{
return(_ApplicationId);
}
string appName = _AppName;
if (appName.Length > 255)
{
appName = appName.Substring(0, 255);
}
_ApplicationId = AccessConnectionHelper.GetApplicationID(holder.Connection, appName, true);
_ApplicationIDCacheDate = DateTime.Now;
if (_ApplicationId != 0)
{
return(_ApplicationId);
}
throw new ProviderException("Provider Error");
}
private int GetApplicationID(AccessConnectionHolder holder)
{
if (_applicationID != 0 && holder.CreateDate < _applicationIDCacheDate)
{
return(_applicationID);
}
string appName = ApplicationName;
if (appName.Length > MaxStringLength)
{
appName = appName.Substring(0, MaxStringLength);
}
_applicationID = AccessConnectionHelper.GetApplicationID(holder.Connection, appName, true);
_applicationIDCacheDate = DateTime.Now;
if (_applicationID == 0)
{
throw new ProviderException("Failed to get ApplicationID");
}
return(_applicationID);
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override bool RoleExists(string roleName)
{
try
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
}
catch
{
return(false);
}
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbConnection connection = holder.Connection;
try
{
try
{
int appId = GetApplicationId(holder);
int roleId = GetRoleId(connection, appId, roleName);
return(roleId != 0);
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
holder.Close();
}
}
catch
{
throw;
}
}
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)
{
try
{
string username = (string)sc["UserName"];
bool userIsAuthenticated = (bool)sc["IsAuthenticated"];
if (username == null || username.Length < 1 || properties.Count < 1)
{
return;
}
string names = String.Empty;
string values = String.Empty;
byte[] buf = null;
PrepareDataForSaving(ref names, ref values, ref buf, false, properties, userIsAuthenticated);
if (names.Length == 0)
{
return;
}
////////////////////////////////////////////////////////////
// Step 2: Store strings in DB
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
bool fBeginTransCalled = false;
try
{
OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = true;
int appId = GetApplicationId(holder);
int userId = AccessConnectionHelper.GetUserID(holder.Connection, appId, username, true, !userIsAuthenticated);
if (userId == 0)
{ // User not creatable
return;
}
cmd = new OleDbCommand(@"SELECT UserId FROM aspnet_Profile WHERE UserId = @UserId", holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
object result = cmd.ExecuteScalar();
if (result != null && (result is int) && ((int)result) == userId)
{
cmd = new OleDbCommand(@"UPDATE aspnet_Profile SET PropertyNames = @PropertyNames, PropertyValuesString = @PropertyValuesString, LastUpdatedDate = @LastUpdatedDate WHERE UserId = @UserId", holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@PropertyNames", names));
cmd.Parameters.Add(new OleDbParameter("@PropertyValuesString", values));
cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastUpdatedDate", DateTime.Now));
cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
}
else
{
cmd = new OleDbCommand(@"INSERT INTO aspnet_Profile (UserId, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES (@UserId, @PropertyNames, @PropertyValuesString, @LastUpdatedDate)", holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
cmd.Parameters.Add(new OleDbParameter("@PropertyNames", names));
cmd.Parameters.Add(new OleDbParameter("@PropertyValuesString", values));
cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastUpdatedDate", DateTime.Now));
}
cmd.ExecuteNonQuery();
try
{ // Not a critical part -- don't throw exceptions here
cmd = new OleDbCommand(@"UPDATE aspnet_Users SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.Connection);
cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastActivityDate", DateTime.Now));
cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
cmd.ExecuteNonQuery();
}
catch { }
cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = false;
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (fBeginTransCalled)
{
try
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
command.ExecuteNonQuery();
}
catch { }
}
holder.Close();
}
}
catch
{
throw;
}
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
// Private methods
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private ProfileInfoCollection GetProfilesForQuery(string sqlQuery, OleDbParameter[] args, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("Page index must be non-negative", "pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("Page size must be positive", "pageSize");
}
long lBound = (long)pageIndex * pageSize;
long uBound = lBound + pageSize - 1;
if (uBound > System.Int32.MaxValue)
{
throw new ArgumentException("pageIndex*pageSize too large");
}
try
{
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
ProfileInfoCollection profiles = new ProfileInfoCollection();
OleDbDataReader reader = null;
try
{
OleDbCommand cmd = new OleDbCommand(sqlQuery, holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@AppId", GetApplicationId(holder)));
int len = args.Length;
for (int iter = 0; iter < len; iter++)
{
cmd.Parameters.Add(args[iter]);
}
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
totalRecords = 0;
while (reader.Read())
{
totalRecords++;
if (totalRecords - 1 < lBound || totalRecords - 1 > uBound)
{
continue;
}
string username;
DateTime dtLastActivity, dtLastUpdated;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = reader.GetDateTime(2);
dtLastUpdated = reader.GetDateTime(3);
int size = reader.GetInt32(4);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
}
return(profiles);
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (reader != null)
{
reader.Close();
}
holder.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 override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames");
SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try
{
try
{
int appId = GetApplicationId(holder);
int[] userIds = new int[usernames.Length];
int[] roleIds = new int[roleNames.Length];
OleDbCommand command;
command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
userIds[iterU] = AccessConnectionHelper.GetUserID(connection, appId, usernames[iterU], false);
if (userIds[iterU] == 0)
{
throw new ProviderException("User not found: " + usernames[iterU]);
}
}
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
roleIds[iterR] = GetRoleId(connection, appId, roleNames[iterR]);
if (roleIds[iterR] == 0)
{
throw new ProviderException("Role not found: " + roleNames[iterR]);
}
}
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
command = new OleDbCommand(@"SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId", connection);
command.Parameters.Add(new OleDbParameter("@UserId", userIds[iterU]));
command.Parameters.Add(new OleDbParameter("@RoleId", roleIds[iterR]));
object result = command.ExecuteScalar();
if (result == null || !(result is int) || ((int)result) != userIds[iterU])
{ // doesn't exist!
throw new ProviderException("The user " + usernames[iterU] + " is already not in role " + roleNames[iterR]);
}
}
}
for (int iterU = 0; iterU < usernames.Length; iterU++)
{
for (int iterR = 0; iterR < roleNames.Length; iterR++)
{
command = new OleDbCommand(@"DELETE FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId", connection);
command.Parameters.Add(new OleDbParameter("@UserId", userIds[iterU]));
command.Parameters.Add(new OleDbParameter("@RoleId", roleIds[iterR]));
if (command.ExecuteNonQuery() != 1)
{
throw new ProviderException("Unknown failure");
}
}
}
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
}
catch (Exception e)
{
try
{
if (fBeginTransCalled)
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
command.ExecuteNonQuery();
}
}
catch { }
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
holder.Close();
}
}
catch
{
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try
{
try
{
int appId = GetApplicationId(holder);
OleDbCommand command;
int roleId = GetRoleId(connection, appId, roleName);
if (roleId == 0)
{
return(false);
}
if (throwOnPopulatedRole)
{
command = new OleDbCommand(@"SELECT COUNT(*) " +
@"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
@"WHERE ur.RoleId = @RoleId AND ur.UserId = u.UserId", connection);
command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
object num = command.ExecuteScalar();
if (!(num is int) || ((int)num) != 0)
{
throw new ProviderException("Role is not empty");
}
}
command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
command = new OleDbCommand(@"DELETE FROM aspnet_Roles WHERE RoleId = @RoleId", connection);
command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
int returnValue = command.ExecuteNonQuery();
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = false;
return(returnValue == 1);
}
catch (Exception e)
{
if (fBeginTransCalled)
{
try
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
command.ExecuteNonQuery();
}
catch { }
}
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
holder.Close();
}
}
catch
{
throw;
}
}
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////
public override void CreateRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
OleDbConnection connection = holder.Connection;
bool fBeginTransCalled = false;
try
{
try
{
int appId = GetApplicationId(holder);
OleDbCommand command;
int roleId = GetRoleId(connection, appId, roleName);
if (roleId != 0)
{
throw new ProviderException("Provider role already exists: " + roleName);
}
command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = true;
command = new OleDbCommand(@"INSERT INTO aspnet_Roles (ApplicationId, RoleName) VALUES (@AppId, @RName)", connection);
command.Parameters.Add(new OleDbParameter("@AppId", appId));
command.Parameters.Add(new OleDbParameter("@RName", roleName));
int returnValue = command.ExecuteNonQuery();
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
fBeginTransCalled = false;
if (returnValue == 1)
{
return;
}
throw new ProviderException("Unknown provider failure");
}
catch (Exception e)
{
if (fBeginTransCalled)
{
try
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
command.ExecuteNonQuery();
}
catch { }
}
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
holder.Close();
}
}
catch
{
throw;
}
}
private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username)
{
string getUserStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId";
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 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);
parameter = parameters.AddWithValue("ApplicationId", appId);
if (path != null)
{
getUserStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
parameter = parameters.Add("Path", OleDbType.WChar);
parameter.Value = path;
}
if (username != null)
{
getUserStateCount += " AND Users.UserName LIKE '%'+@UserName+'%'";
parameter = parameters.Add("UserName", OleDbType.WChar);
parameter.Value = username;
}
if (inactiveSinceDate != DateTime.MinValue)
{
getUserStateCount += " 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);
}
command.CommandText = getUserStateCount;
object result = command.ExecuteScalar();
if ((result != null) && (result is Int32))
{
count = (Int32)result;
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
private int ResetUserStatePerUsers(string path, string[] usernames)
{
int count = 0;
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
bool beginTransCalled = false;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
int applicationID = GetApplicationID(connectionHolder);
if (applicationID != 0)
{
OleDbCommand command = new OleDbCommand(null, connection);
OleDbParameter userIdParam = command.Parameters.Add(new OleDbParameter("@UserId", OleDbType.Integer));
string fromAndWhereClause = " FROM aspnet_PagePersonalizationPerUser WHERE UserId = @UserId";
if (!String.IsNullOrEmpty(path))
{
int pathId = AccessConnectionHelper.GetPathID(connection, applicationID, path);
fromAndWhereClause += " AND PathId = @PathId";
command.Parameters.Add(new OleDbParameter("@PathId", pathId));
}
string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
string deleteCommandText = "DELETE" + fromAndWhereClause;
OleDbCommand transCommand = new OleDbCommand("BEGIN TRANSACTION", connection);
transCommand.ExecuteNonQuery();
beginTransCalled = true;
foreach (string username in usernames)
{
command.CommandText = selectCommandText;
userIdParam.Value = AccessConnectionHelper.GetUserID(connection, applicationID, username);
int numOfRecords = (int)command.ExecuteScalar();
if (numOfRecords > 0)
{
command.CommandText = deleteCommandText;
command.ExecuteNonQuery();
count += numOfRecords;
}
}
transCommand.CommandText = "COMMIT TRANSACTION";
transCommand.ExecuteNonQuery();
}
}
catch
{
try
{
if (beginTransCalled)
{
OleDbCommand rollbackCommand = new OleDbCommand("ROLLBACK TRANSACTION", connection);
rollbackCommand.ExecuteNonQuery();
}
}
catch
{
}
throw;
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
private int ResetStatePerPaths(string tableName, string[] paths)
{
if (paths == null || paths.Length == 0)
{
return(0);
}
int count = 0;
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
bool beginTransCalled = false;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
int applicationID = GetApplicationID(connectionHolder);
if (applicationID != 0)
{
string fromAndWhereClause = " FROM " + tableName + " WHERE PathId = @PathId";
string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
string deleteCommandText = "DELETE" + fromAndWhereClause;
OleDbCommand command = new OleDbCommand(null, connection);
OleDbParameter pathParam = command.Parameters.Add(new OleDbParameter("@PathId", OleDbType.Integer));
OleDbCommand transCommand = new OleDbCommand("BEGIN TRANSACTION", connection);
transCommand.ExecuteNonQuery();
beginTransCalled = true;
foreach (string path in paths)
{
command.CommandText = selectCommandText;
pathParam.Value = AccessConnectionHelper.GetPathID(connection, applicationID, path);
int numOfRecords = (int)command.ExecuteScalar();
if (numOfRecords > 0)
{
command.CommandText = deleteCommandText;
command.ExecuteNonQuery();
count += numOfRecords;
}
}
transCommand.CommandText = "COMMIT TRANSACTION";
transCommand.ExecuteNonQuery();
}
}
catch
{
try
{
if (beginTransCalled)
{
OleDbCommand rollbackCommand = new OleDbCommand("ROLLBACK TRANSACTION", connection);
rollbackCommand.ExecuteNonQuery();
}
}
catch
{
}
throw;
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
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;
}
}
public override int ResetUserState(string path, DateTime userInactiveSinceDate)
{
path = PersonalizationProviderHelper.CheckAndTrimString(path, "path", false, MaxStringLength);
AccessConnectionHolder connectionHolder = null;
OleDbConnection connection = null;
int count = 0;
try
{
try
{
connectionHolder = GetConnectionHolder();
connection = connectionHolder.Connection;
// Special note: OleDbProvider requires the parameters to be added
// in the same order as appearing in the query text.
string getDeleteUserStateCount =
"SELECT COUNT(*)" +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Users.LastActivityDate <= @InactiveSinceDate";
string deleteUserState =
"DELETE FROM aspnet_PagePersonalizationPerUser" +
" WHERE Id IN (SELECT PerUser.Id " +
" FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
" WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
" AND Paths.ApplicationId = @ApplicationId" +
" AND Users.LastActivityDate <= @InactiveSinceDate";
// Get the count of records that would be deleted
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
OleDbParameterCollection parameters = command.Parameters;
OleDbParameter parameter;
int appId = GetApplicationID(connectionHolder);
parameters.AddWithValue("ApplicationId", appId);
// 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(userInactiveSinceDate.Year, userInactiveSinceDate.Month, userInactiveSinceDate.Day,
userInactiveSinceDate.Hour, userInactiveSinceDate.Minute, userInactiveSinceDate.Second);
if (path != null)
{
const string pathParamQueryText = " AND Paths.Path = @Path";
getDeleteUserStateCount += pathParamQueryText;
deleteUserState += pathParamQueryText;
parameters.AddWithValue("Path", path);
}
deleteUserState += ")";
command.CommandText = getDeleteUserStateCount;
object lookupResult = command.ExecuteScalar();
if ((lookupResult != null) && (lookupResult is Int32))
{
count = (Int32)lookupResult;
if (count > 0)
{
// Do the actual deletion
command.CommandText = deleteUserState;
command.ExecuteNonQuery();
}
}
}
finally
{
if (connectionHolder != null)
{
connectionHolder.Close();
connectionHolder = null;
}
}
}
catch
{
throw;
}
return(count);
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
// Mangement APIs from ProfileProvider class
public override int DeleteProfiles(ProfileInfoCollection profiles)
{
if (profiles == null)
{
throw new ArgumentNullException("profiles");
}
if (profiles.Count < 1)
{
throw new ArgumentException("Profiles collection is empty", "profiles");
}
foreach (ProfileInfo pi in profiles)
{
string username = pi.UserName;
SecUtility.CheckParameter(ref username, true, true, true, 255, "UserName");
}
try
{
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
bool fBeginTransCalled = false;
int numDeleted = 0;
try
{
OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = true;
int appId = GetApplicationId(holder);
foreach (ProfileInfo profile in profiles)
{
if (DeleteProfile(holder, profile.UserName.Trim(), appId))
{
numDeleted++;
}
}
cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
cmd.ExecuteNonQuery();
fBeginTransCalled = false;
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (fBeginTransCalled)
{
try
{
OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
command.ExecuteNonQuery();
}
catch { }
}
holder.Close();
}
return(numDeleted);
}
catch
{
throw;
}
}