/// <devdoc>
/// </devdoc>
internal static SqlConnectionHolder GetConnection(string connectionString, bool revertImpersonation)
{
string strTempConnection = connectionString.ToUpperInvariant();
//Commented out for source code release.
//if (strTempConnection.Contains(s_strUpperDataDirWithToken))
// EnsureSqlExpressDBFile( connectionString );
SqlConnectionHolder holder = new SqlConnectionHolder(connectionString);
bool closeConn = true;
try
{
try
{
holder.Open(null, revertImpersonation);
closeConn = false;
}
finally
{
if (closeConn)
{
holder.Close();
holder = null;
}
}
}
catch
{
throw;
}
return holder;
}
internal static int aspnet_Membership_CreateUser(String ApplicationName, String UserName, String Password,
String PasswordSalt, String Email, String PasswordQuestion, String PasswordAnswer, Boolean IsApproved,
DateTime CurrentTimeUtc, DateTime CreateDate, Boolean UniqueEmail, int PasswordFormat, ref Object UserId,
SqlConnectionHolder holder)
{
MySqlTransaction sqlTrans = null;
Guid ApplicationId = Guid.Empty;
Guid NewUserId = Guid.Empty;
Boolean IsLockedOut = false;
DateTime LastLockoutDate = new DateTime(1754, 1, 1, 0, 0, 0);
int FailedPasswordAttemptCount = 0;
DateTime FailedPasswordAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0);
int FailedPasswordAnswerAttemptCount = 0;
DateTime FailedPasswordAnswerAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0);
ApplicationId = aspnet_Applications_CreateApplication(holder, ApplicationName);
CreateDate = CurrentTimeUtc;
String sQuery = "SELECT UserId FROM aspnet_Users WHERE @UserName = LoweredUserName AND @ApplicationId = ApplicationId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
NewUserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (!NewUserId.Equals(Guid.Empty))
{
return 6;
}
else
{
NewUserId = aspnet_Users_CreateUser(ApplicationId, UserName, false, CreateDate, holder);
}
if (UniqueEmail == true)
{
sQuery = "SELECT Count(UserId) FROM aspnet_Membership WHERE ApplicationId = @ApplicationId AND LoweredEmail = @Email";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Email", MySqlDbType.VarChar, Email.ToLower()));
if ((long)cmd.ExecuteScalar() > 0)
{
return 7;
}
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "INSERT INTO aspnet_Membership " +
"( ApplicationId," +
"UserId," +
"Password," +
"PasswordSalt," +
"Email," +
"LoweredEmail," +
"PasswordQuestion," +
"PasswordAnswer," +
"PasswordFormat," +
"IsApproved," +
"IsLockedOut," +
"CreateDate," +
"LastLoginDate," +
"LastPasswordChangedDate," +
"LastLockoutDate," +
"FailedPasswordAttemptCount," +
"FailedPasswordAttemptWindowStart," +
"FailedPasswordAnswerAttemptCount," +
"FailedPasswordAnswerAttemptWindowStart ) " +
"VALUES ( @ApplicationId," +
"@UserId," +
"@Password," +
"@PasswordSalt," +
"@Email," +
"@LowerEmail," +
"@PasswordQuestion," +
"@PasswordAnswer," +
"@PasswordFormat," +
"@IsApproved," +
"@IsLockedOut," +
"@CreateDate," +
"@CreateDate," +
"@CreateDate," +
"@LastLockoutDate," +
"@FailedPasswordAttemptCount," +
"@FailedPasswordAttemptWindowStart," +
"@FailedPasswordAnswerAttemptCount," +
"@FailedPasswordAnswerAttemptWindowStart )";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, NewUserId));
cmd.Parameters.Add(CreateInputParam("@Password", MySqlDbType.VarChar, Password));
cmd.Parameters.Add(CreateInputParam("@PasswordSalt", MySqlDbType.VarChar, PasswordSalt));
cmd.Parameters.Add(CreateInputParam("@Email", MySqlDbType.VarChar, Email));
cmd.Parameters.Add(CreateInputParam("@LowerEmail", MySqlDbType.VarChar, Email.ToLower()));
cmd.Parameters.Add(CreateInputParam("@PasswordQuestion", MySqlDbType.VarChar, PasswordQuestion));
cmd.Parameters.Add(CreateInputParam("@PasswordAnswer", MySqlDbType.VarChar, PasswordAnswer));
cmd.Parameters.Add(CreateInputParam("@PasswordFormat", MySqlDbType.Int16, PasswordFormat));
cmd.Parameters.Add(CreateInputParam("@IsApproved", MySqlDbType.Bit, IsApproved));
cmd.Parameters.Add(CreateInputParam("@IsLockedOut", MySqlDbType.Bit, IsLockedOut));
cmd.Parameters.Add(CreateInputParam("@CreateDate", MySqlDbType.DateTime, CreateDate));
cmd.Parameters.Add(CreateInputParam("@LastLockoutDate", MySqlDbType.DateTime, LastLockoutDate));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptCount", MySqlDbType.Int16, FailedPasswordAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAttemptWindowStart));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptCount", MySqlDbType.Int16, FailedPasswordAnswerAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAnswerAttemptWindowStart));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
UserId = NewUserId;
return 0;
}
internal static int aspnet_Membership_ResetPassword(String ApplicationName, String UserName, String NewPassword,
int MaxInvalidPasswordAttempts, int PasswordAttemptWindow, String PasswordSalt, DateTime CurrentTimeUtc,
int PasswordFormat, String PasswordAnswer, SqlConnectionHolder holder)
{
Boolean IsLockedOut = false;
DateTime LastLockoutDate = new DateTime();
int FailedPasswordAttemptCount = 0;
DateTime FailedPasswordAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0); ;
int FailedPasswordAnswerAttemptCount = 0;
DateTime FailedPasswordAnswerAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0); ;
Guid UserId = Guid.Empty;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT aspnet_Users.userid FROM aspnet_Membership, aspnet_Users, aspnet_Applications " +
"WHERE aspnet_Users.LoweredUserName = @UserName AND aspnet_users.ApplicationId = " +
"aspnet_applications.ApplicationId AND @ApplicationName = aspnet_Applications.LoweredApplicationName " +
"AND aspnet_Users.UserId = aspnet_Membership.UserId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT IsLockedOut, " +
"LastLockoutDate, " +
"FailedPasswordAttemptCount, " +
"FailedPasswordAttemptWindowStart, " +
"FailedPasswordAnswerAttemptCount, " +
"FailedPasswordAnswerAttemptWindowStart " +
"FROM aspnet_Membership " +
"WHERE @UserId = UserId ";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
IsLockedOut = reader.GetBoolean(0);
LastLockoutDate = reader.GetDateTime(1);
FailedPasswordAttemptCount = reader.GetInt32(2);
FailedPasswordAttemptWindowStart = reader.GetDateTime(3);
FailedPasswordAnswerAttemptCount = reader.GetInt32(4);
FailedPasswordAnswerAttemptWindowStart = reader.GetDateTime(5);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (IsLockedOut == true)
{
return 99;
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "UPDATE aspnet_Membership SET Password = @NewPassword, LastPasswordChangedDate = @CurrentTimeUtc, " +
"PasswordFormat = @PasswordFormat,PasswordSalt = @PasswordSalt WHERE @UserId = UserId AND " +
"( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@NewPassword", MySqlDbType.VarChar, NewPassword));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@PasswordFormat", MySqlDbType.Int32, PasswordFormat));
cmd.Parameters.Add(CreateInputParam("@PasswordSalt", MySqlDbType.VarChar, PasswordSalt));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@PasswordAnswer", MySqlDbType.VarChar, PasswordAnswer));
int rowCount = cmd.ExecuteNonQuery();
sqlTrans.Commit();
if (rowCount == 0)
{
if (CurrentTimeUtc > FailedPasswordAnswerAttemptWindowStart.AddMinutes(PasswordAttemptWindow))
{
FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc;
FailedPasswordAnswerAttemptCount = 1;
}
else
{
FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc;
FailedPasswordAnswerAttemptCount++;
}
if (FailedPasswordAnswerAttemptCount > MaxInvalidPasswordAttempts)
{
IsLockedOut = true;
LastLockoutDate = CurrentTimeUtc;
}
return 3;
}
else
{
if (FailedPasswordAnswerAttemptCount > 0)
{
FailedPasswordAnswerAttemptCount = 0;
FailedPasswordAnswerAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0);
}
}
if (PasswordAnswer != null)
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "UPDATE aspnet_Membership SET IsLockedOut = @IsLockedOut, " +
"LastLockoutDate = @LastLockoutDate, FailedPasswordAttemptCount = " +
"@FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart = " +
"@FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount = " +
"@FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart = " +
"@FailedPasswordAnswerAttemptWindowStart WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@IsLockedOut", MySqlDbType.Bit, IsLockedOut));
cmd.Parameters.Add(CreateInputParam("@LastLockoutDate", MySqlDbType.DateTime, LastLockoutDate));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptCount", MySqlDbType.Int32, FailedPasswordAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAttemptWindowStart));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptCount", MySqlDbType.Int32, FailedPasswordAnswerAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAnswerAttemptWindowStart));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_Membership_ChangePasswordQuestionAndAnswer(String ApplicationName, String UserName,
String NewPasswordQuestion, String NewPasswordAnswer, SqlConnectionHolder holder)
{
Guid UserId = Guid.Empty;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT aspnet_Users.UserId FROM aspnet_Membership, aspnet_Users, aspnet_Applications " +
"WHERE aspnet_Users.LoweredUserName = @UserName AND aspnet_users.ApplicationId = " +
"aspnet_applications.ApplicationId AND @ApplicationName = aspnet_Applications.LoweredApplicationName " +
"AND aspnet_Users.UserId = aspnet_Membership.UserId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "UPDATE aspnet_Membership SET PasswordQuestion = @NewPasswordQuestion, " +
"PasswordAnswer = @NewPasswordAnswer WHERE UserId=@UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@NewPasswordQuestion", MySqlDbType.VarChar, NewPasswordQuestion));
cmd.Parameters.Add(CreateInputParam("@NewPasswordAnswer", MySqlDbType.VarChar, NewPasswordAnswer));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_Membership_GetUserByEmail(String ApplicationName, String Email,
SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT UserId FROM aspnet_Membership WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
if (String.IsNullOrEmpty(Email))
{
sQuery = "SELECT aspnet_Users.UserName " +
"FROM aspnet_Applications, aspnet_Users, aspnet_Membership " +
"WHERE @ApplicationName = aspnet_Applications.LoweredApplicationName AND " +
"aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND " +
"aspnet_Users.UserId = aspnet_Membership.UserId AND " +
"aspnet_Membership.LoweredEmail IS NULL";
}
else
{
sQuery = "SELECT u.UserName " +
"FROM aspnet_Applications, aspnet_Users, aspnet_Membership " +
"WHERE @ApplicationName = aspnet_Applications.LoweredApplicationName AND " +
"aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND " +
"aspnet_Users.UserId = aspnet_Membership.UserId AND " +
"@Email = aspnet_Membership.LoweredEmail";
}
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
outcmd.Parameters.Add(CreateInputParam("@Email", MySqlDbType.VarChar, Email.ToLower()));
return 0;
}
internal static int aspnet_Membership_GetPassword(String ApplicationName, String UserName,
int MaxInvalidPasswordAttempts, int PasswordAttemptWindow, DateTime CurrentTimeUtc,
String PasswordAnswer, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
outcmd = new MySqlCommand("SELECT * FROM aspnet_Membership WHERE 1=0", holder.Connection);
Guid UserId = Guid.Empty;
int PasswordFormat = 0;
String Password = "";
String passAns = "";
Boolean IsLockedOut = false;
DateTime LastLockoutDate = new DateTime(1754, 1, 1, 0, 0, 0); ;
int FailedPasswordAttemptCount = 0;
DateTime FailedPasswordAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0); ;
int FailedPasswordAnswerAttemptCount = 0;
DateTime FailedPasswordAnswerAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0); ;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT aspnet_Users.UserId, " +
"aspnet_Membership.Password, " +
"aspnet_Membership.PasswordAnswer, " +
"aspnet_Membership.PasswordFormat, " +
"aspnet_Membership.IsLockedOut, " +
"aspnet_Membership.LastLockoutDate, " +
"aspnet_Membership.FailedPasswordAttemptCount, " +
"aspnet_Membership.FailedPasswordAttemptWindowStart, " +
"aspnet_Membership.FailedPasswordAnswerAttemptCount, " +
"aspnet_Membership.FailedPasswordAnswerAttemptWindowStart " +
"FROM aspnet_Applications, aspnet_Users, aspnet_Membership " +
"WHERE @ApplicationName = aspnet_Applications.LoweredApplicationName AND " +
"aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND " +
"aspnet_Users.UserId = aspnet_Membership.UserId AND " +
"@UserName = aspnet_Users.LoweredUserName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
Password = reader.GetString(1);
passAns = reader.GetString(2);
PasswordFormat = reader.GetInt32(3);
IsLockedOut = reader.GetBoolean(4);
LastLockoutDate = reader.GetDateTime(5);
FailedPasswordAttemptCount = reader.GetInt32(6);
FailedPasswordAttemptWindowStart = reader.GetDateTime(7);
FailedPasswordAnswerAttemptCount = reader.GetInt32(8);
FailedPasswordAnswerAttemptWindowStart = reader.GetDateTime(9);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
if (!String.IsNullOrEmpty(PasswordAnswer))
{
if (!String.IsNullOrEmpty(passAns) || passAns.ToLower() != PasswordAnswer.ToLower())
{
if (CurrentTimeUtc > FailedPasswordAnswerAttemptWindowStart.AddMinutes(PasswordAttemptWindow))
{
FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc;
FailedPasswordAnswerAttemptCount = 1;
}
else
{
FailedPasswordAnswerAttemptWindowStart = CurrentTimeUtc;
FailedPasswordAnswerAttemptCount++;
}
if (FailedPasswordAnswerAttemptCount >= MaxInvalidPasswordAttempts)
{
IsLockedOut = true;
LastLockoutDate = CurrentTimeUtc;
}
return 3;
}
else
{
if (FailedPasswordAnswerAttemptCount > 0)
{
FailedPasswordAnswerAttemptCount = 0;
FailedPasswordAnswerAttemptWindowStart = new DateTime(1754, 1, 1, 0, 0, 0);
}
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "UPDATE aspnet_Membership " +
"SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate, " +
"FailedPasswordAttemptCount = @FailedPasswordAttemptCount, " +
"FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart, " +
"FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount, " +
"FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart " +
"WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@IsLockedOut", MySqlDbType.Bit, IsLockedOut));
cmd.Parameters.Add(CreateInputParam("@LastLockoutDate", MySqlDbType.DateTime, LastLockoutDate));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptCount", MySqlDbType.Int32, FailedPasswordAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAttemptWindowStart));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptCount", MySqlDbType.Int32, FailedPasswordAnswerAttemptCount));
cmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptWindowStart", MySqlDbType.DateTime, FailedPasswordAnswerAttemptWindowStart));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
}
sQuery = "SELECT @Password, @PasswordFormat";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@Password", MySqlDbType.VarChar, Password));
outcmd.Parameters.Add(CreateInputParam("@PasswordFormat", MySqlDbType.Int32, PasswordFormat));
return 0;
}
internal static int aspnet_Users_DeleteUser(String ApplicationName, String UserName,
int TablesToDeleteFrom, ref int NumTablesDeletedFrom, SqlConnectionHolder holder)
{
Guid UserId = Guid.Empty;
NumTablesDeletedFrom = 0;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT aspnet_Users.userid FROM aspnet_Membership, aspnet_Users, aspnet_Applications " +
"WHERE aspnet_Users.LoweredUserName = @UserName AND aspnet_users.ApplicationId = " +
"aspnet_applications.ApplicationId AND @ApplicationName = aspnet_Applications.LoweredApplicationName " +
"AND aspnet_Users.UserId = aspnet_Membership.UserId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
// Delete from Membership table if (@TablesToDeleteFrom & 1) is set
if ((TablesToDeleteFrom & 1) > 0)
{
try
{
sQuery = "DELETE FROM aspnet_Membership WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if (cmd.ExecuteNonQuery() > 0)
{
NumTablesDeletedFrom++;
}
}
catch
{
}
}
//Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
if ((TablesToDeleteFrom & 2) > 0)
{
try
{
sQuery = "DELETE FROM aspnet_UsersInRoles WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if (cmd.ExecuteNonQuery() > 0)
{
NumTablesDeletedFrom++;
}
}
catch
{
}
}
//Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
if ((TablesToDeleteFrom & 4) > 0)
{
try
{
sQuery = "DELETE FROM aspnet_Profile WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if (cmd.ExecuteNonQuery() > 0)
{
NumTablesDeletedFrom++;
}
}
catch
{
}
}
//Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
if ((TablesToDeleteFrom & 8) > 0)
{
try
{
sQuery = "DELETE FROM aspnet_PersonalizationPerUser WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if (cmd.ExecuteNonQuery() > 0)
{
NumTablesDeletedFrom++;
}
}
catch
{
}
}
//Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
if ((TablesToDeleteFrom & 1) > 0 &&
(TablesToDeleteFrom & 2) > 0 &&
(TablesToDeleteFrom & 4) > 0 &&
(TablesToDeleteFrom & 8) > 0)
{
try
{
sQuery = "DELETE FROM aspnet_Users WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if (cmd.ExecuteNonQuery() > 0)
{
NumTablesDeletedFrom++;
}
}
catch
{
}
}
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_UsersInRoles_RemoveUsersFromRoles(String ApplicationName, String[] UserNames,
String[] RoleNames, DateTime CurrentTimeUtc, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 2;
}
sqlTrans = holder.Connection.BeginTransaction();
try
{
foreach (String u in UserNames)
{
Guid UserId = Guid.Empty;
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, u.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
foreach (String r in RoleNames)
{
Guid RoleId = Guid.Empty;
sQuery = "SELECT RoleId FROM aspnet_Roles WHERE LoweredRoleName = @RoleName AND " +
"ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, r.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
RoleId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (RoleId.Equals(Guid.Empty))
{
return 2;
}
sQuery = "SELECT COUNT(UserId) FROM aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
if ((long)cmd.ExecuteScalar() <= 0)
{
return 3;
}
sQuery = "DELETE FROM aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
cmd.ExecuteNonQuery();
}
}
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_PersonalizationPerUser_SetPageSettings(String ApplicationName,
String UserName, String Path, Byte[] PageSettings, DateTime CurrentTimeUtc,
SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
Guid PathId = Guid.Empty;
Guid UserId = Guid.Empty;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
ApplicationId = aspnet_Applications_CreateApplication(holder, ApplicationName);
}
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
UserId = aspnet_Users_CreateUser(ApplicationId, UserName, false, CurrentTimeUtc, holder);
}
sQuery = "SELECT aspnet_Paths.PathId FROM aspnet_Paths WHERE aspnet_Paths.ApplicationId = @ApplicationId AND aspnet_Paths.LoweredPath = @Path";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
PathId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (PathId.Equals(Guid.Empty))
{
PathId = aspnet_Paths_CreatePath(ApplicationId, Path, holder);
}
sQuery = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sQuery = "SELECT COUNT(PathId) FROM aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@PathId", MySqlDbType.Binary, PathId));
if ((long)cmd.ExecuteScalar() > 0)
{
sQuery = "UPDATE aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, " +
"LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId";
}
else
{
sQuery = "INSERT INTO aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, " +
"LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)";
}
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@PageSettings", MySqlDbType.Blob, PageSettings));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@PathId", MySqlDbType.Binary, PathId));
cmd.ExecuteNonQuery();
return 0;
}
internal static int aspnet_PersonalizationPerUser_ResetPageSettings(String ApplicationName,
String UserName, String Path, DateTime CurrentTimeUtc, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
Guid PathId = Guid.Empty;
Guid UserId = Guid.Empty;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT aspnet_Paths.PathId FROM aspnet_Paths WHERE aspnet_Paths.ApplicationId = @ApplicationId AND aspnet_Paths.LoweredPath = @Path";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
PathId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (PathId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sQuery = "DELETE FROM aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@PathId", MySqlDbType.Binary, PathId));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
return 0;
}
internal static int aspnet_PersonalizationAllUsers_GetPageSettings(String ApplicationName,
String Path, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT PathId FROM aspnet_PersonalizationPerUser WHERE 1=0";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
Guid ApplicationId = Guid.Empty;
Guid PathId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
sQuery = "SELECT aspnet_Paths.PathId FROM aspnet_Paths WHERE aspnet_Paths.ApplicationId = @ApplicationId AND aspnet_Paths.LoweredPath = @Path";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
PathId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (PathId.Equals(Guid.Empty))
{
return 0;
}
sQuery = "SELECT aspnet_PersonalizationAllUsers.PageSettings FROM aspnet_PersonalizationAllUsers " +
"WHERE aspnet_PersonalizationAllUsers.PathId = @PathId";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@PathId", MySqlDbType.Binary, PathId));
return 0;
}
internal static int aspnet_PersonalizationAdministration_ResetUserState(String ApplicationName,
DateTime InactiveSinceDate, String[] Users, String[] Paths, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
int totalRecords = 0;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
foreach (String UserName in Users)
{
foreach (String Path in Paths)
{
sQuery = "DELETE FROM aspnet_PersonalizationPerUser " +
"WHERE Id IN (SELECT aspnet_PersonalizationPerUser.Id " +
"FROM aspnet_PersonalizationPerUser, aspnet_Users, aspnet_Paths " +
"WHERE Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationPerUser.UserId = aspnet_Users.UserId " +
"AND aspnet_PersonalizationPerUser.PathId = aspnet_Paths.PathId " +
"AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate) " +
"AND (@UserName IS NULL OR aspnet_Users.LoweredUserName = @UserName) " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath = @Path)) ";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", MySqlDbType.DateTime, InactiveSinceDate));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
totalRecords += cmd.ExecuteNonQuery();
}
}
return totalRecords;
}
internal static int aspnet_PersonalizationAdministration_ResetSharedState(String ApplicationName,
String[] Paths, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
int totalRecords = 0;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
foreach (String Path in Paths)
{
sQuery = "DELETE FROM aspnet_PersonalizationAllUsers WHERE PathId IN " +
"(SELECT aspnet_PersonalizationAllUsers.PathId " +
"FROM aspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE aspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
"AND aspnet_Paths.LoweredPath = @Path) ";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
totalRecords += cmd.ExecuteNonQuery();
}
return totalRecords;
}
internal static int aspnet_PersonalizationAdministration_GetCountOfState(Boolean AllUsersScope,
String ApplicationName, String Path, String UserName, DateTime InactiveSinceDate,
SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
int totalRecords = 0;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
if (AllUsersScope)
{
sQuery = "SELECT COUNT(*) FROM aspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE aspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) ";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
totalRecords = reader.GetInt32(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
}
else
{
sQuery = "SELECT COUNT(*) " +
"FROM aspnet_PersonalizationPerUser, aspnet_Users, aspnet_Paths " +
"WHERE aspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationPerUser.UserId = aspnet_Users.UserId " +
"AND aspnet_PersonalizationPerUser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
"AND (@UserName IS NULL OR aspnet_Users.LoweredUserName LIKE @UserName) " +
"AND (@InactiveSinceDate IS NULL OR aspnet_Users.LastActivityDate <= @InactiveSinceDate) ";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", MySqlDbType.DateTime, InactiveSinceDate));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
totalRecords = reader.GetInt32(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
}
return totalRecords;
}
internal static int aspnet_PersonalizationAdministration_FindState(Boolean AllUsersScope,
String ApplicationName, int PageIndex, int PageSize, String Path, String UserName,
DateTime InactiveSinceDate, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT * FROM aspnet_Paths WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
Guid ApplicationId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
int PageLowerBound = PageSize * PageIndex;
int PageUpperBound = PageSize - 1 + PageLowerBound;
sQuery = "CREATE TEMPORARY TABLE IF NOT EXISTS PageIndex (" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"ItemId binary(255) not null)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
sQuery = "TRUNCATE TABLE PageIndex";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
int totalRecords = 0;
if (AllUsersScope)
{
sQuery = "INSERT INTO PageIndex (ItemId) (" +
"SELECT aspnet_Paths.PathId " +
"FROM aspnet_Paths, " +
"((SELECT aspnet_Paths.PathId " +
"FROM aspnet_PersonalizationAllUsers, aspnet_Paths " +
"WHERE aspnet_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 aspnet_PersonalizationPerUser, aspnet_Paths " +
"WHERE aspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationPerUser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY aspnet_Paths.Path ASC)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
totalRecords = cmd.ExecuteNonQuery();
sQuery = "SELECT aspnet_Paths.Path, " +
"SharedDataPerPath.LastUpdatedDate, " +
"SharedDataPerPath.SharedDataLength, " +
"UserDataPerPath.UserDataLength, " +
"UserDataPerPath.UserCount " +
"FROM aspnet_Paths, " +
"((SELECT PageIndex.ItemId AS PathId, " +
"aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
"LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
"FROM aspnet_PersonalizationAllUsers, PageIndex " +
"WHERE aspnet_PersonalizationAllUsers.PathId = PageIndex.ItemId " +
"AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound " +
") AS SharedDataPerPath " +
"FULL OUTER JOIN " +
"(SELECT PageIndex.ItemId AS PathId, " +
"SUM(LENGTH(aspnet_PersonalizationPerUser.PageSettings)) AS UserDataLength, " +
"COUNT(*) AS UserCount " +
"FROM aspnet_PersonalizationPerUser, PageIndex " +
"WHERE aspnet_PersonalizationPerUser.PathId = PageIndex.ItemId " +
"AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound " +
"GROUP BY PageIndex.ItemId " +
") AS UserDataPerPath " +
"ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
") " +
"WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
"ORDER BY aspnet_Paths.Path ASC ";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@PageLowerBound", MySqlDbType.Int32, PageLowerBound));
outcmd.Parameters.Add(CreateInputParam("@PageUpperBound", MySqlDbType.Int32, PageUpperBound));
return totalRecords;
}
else
{
sQuery = "INSERT INTO PageIndex (ItemId) (" +
"SELECT aspnet_PersonalizationPerUser.Id " +
"FROM aspnet_PersonalizationPerUser, aspnet_Users, aspnet_Paths " +
"WHERE aspnet_Paths.ApplicationId = @ApplicationId " +
"AND aspnet_PersonalizationPerUser.UserId = aspnet_Users.UserId " +
"AND aspnet_PersonalizationPerUser.PathId = aspnet_Paths.PathId " +
"AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
"AND (@UserName IS NULL OR aspnet_Users.LoweredUserName LIKE @UserName) " +
"AND (@InactiveSinceDate IS NULL OR aspnet_Users.LastActivityDate <= @InactiveSinceDate) " +
"ORDER BY aspnet_Paths.Path ASC, aspnet_Users.UserName ASC )";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", MySqlDbType.DateTime, InactiveSinceDate));
totalRecords = cmd.ExecuteNonQuery();
sQuery = "SELECT aspnet_Paths.Path, aspnet_PersonalizationPerUser.LastUpdatedDate, LENGTH(aspnet_PersonalizationPerUser.PageSettings), aspnet_Users.UserName, aspnet_Users.LastActivityDate " +
"FROM aspnet_PersonalizationPerUser, aspnet_Users, aspnet_Paths, #PageIndex PageIndex " +
"WHERE aspnet_PersonalizationPerUser.Id = PageIndex.ItemId " +
"AND aspnet_PersonalizationPerUser.UserId = aspnet_Users.UserId " +
"AND aspnet_PersonalizationPerUser.PathId = aspnet_Paths.PathId " +
"AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound " +
"ORDER BY aspnet_Paths.Path ASC, aspnet_Users.UserName ASC ";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@PageLowerBound", MySqlDbType.Int32, PageLowerBound));
outcmd.Parameters.Add(CreateInputParam("@PageUpperBound", MySqlDbType.Int32, PageUpperBound));
return totalRecords;
}
}
internal static int aspnet_UsersInRoles_GetUsersInRoles(String ApplicationName, String RoleName,
SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT * FROM aspnet_Roles WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
Guid ApplicationId = Guid.Empty;
Guid RoleId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT RoleId FROM aspnet_Roles WHERE LoweredRoleName = @RoleName AND " +
"ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, RoleName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
RoleId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (RoleId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT aspnet_Users.UserName " +
"FROM aspnet_Users, aspnet_UsersInRoles " +
"WHERE aspnet_Users.UserId = aspnet_UsersInRoles.UserId AND @RoleId = " +
"aspnet_UsersInRoles.RoleId AND aspnet_Users.ApplicationId = @ApplicationId " +
"ORDER BY aspnet_Users.UserName";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
outcmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
return 0;
}
internal static int aspnet_UsersInRoles_IsUserInRole(String ApplicationName, String UserName, String RoleName,
SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
Guid UserId = Guid.Empty;
Guid RoleId = Guid.Empty;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 2;
}
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName " +
"AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 2;
}
sQuery = "SELECT RoleId FROM aspnet_Roles WHERE LoweredRoleName = @RoleName AND " +
"ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, RoleName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
RoleId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (RoleId.Equals(Guid.Empty))
{
return 3;
}
sQuery = "SELECT COUNT(UserId) FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
if ((long)cmd.ExecuteScalar() > 0)
{
return 1;
}
else
{
return 0;
}
}
internal static int aspnet_Profile_DeleteProfiles(String ApplicationName, String[] UserNames,
SqlConnectionHolder holder)
{
int UsersDeleted = 0;
foreach (String u in UserNames)
{
int ret = 0;
int status = MySqlStoredProcedures.aspnet_Users_DeleteUser(ApplicationName, u, 4, ref ret, holder);
if (ret != 0)
{
UsersDeleted++;
}
}
return UsersDeleted;
}
internal static Guid aspnet_Users_CreateUser(Guid ApplicationId, String UserName, Boolean IsUserAnonymous,
DateTime LastActivityDate, SqlConnectionHolder holder)
{
MySqlTransaction sqlTrans = null;
Guid NewUserId = Guid.NewGuid();
try
{
sqlTrans = holder.Connection.BeginTransaction();
String sQuery = "INSERT aspnet_Users " +
"( ApplicationId, " +
"UserId, " +
"UserName, " +
"LoweredUserName, " +
"IsAnonymous, " +
"LastActivityDate)" +
"VALUES (@ApplicationId, " +
"@UserId, " +
"@UserName, " +
"@LowerUserName, " +
"@IsUserAnonymous, " +
"@LastActivityDate)";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, NewUserId));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName));
cmd.Parameters.Add(CreateInputParam("@LowerUserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@IsUserAnonymous", MySqlDbType.Bit, IsUserAnonymous));
cmd.Parameters.Add(CreateInputParam("@LastActivityDate", MySqlDbType.DateTime, LastActivityDate));
cmd.ExecuteScalar();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
}
catch
{
throw;
}
}
return NewUserId;
}
internal static int aspnet_Profile_GetNumberOfInactiveProfiles(String ApplicationName, int ProfileAuthOptions,
DateTime InactiveSinceDate, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
sQuery = "SELECT COUNT(*) FROM aspnet_Users, aspnet_Profile WHERE ApplicationId = @ApplicationId " +
"AND aspnet_Users.UserId = aspnet_Profile.UserId AND (LastActivityDate <= @InactiveSinceDate) AND ( " +
"(@ProfileAuthOptions = 2) OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) " +
"OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0))";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", MySqlDbType.DateTime, InactiveSinceDate));
cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", MySqlDbType.Int32, ProfileAuthOptions));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
int NumberInactiveProfiles = 0;
try
{
if (reader.Read())
{
NumberInactiveProfiles = reader.GetInt32(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
return NumberInactiveProfiles;
}
internal static int aspnet_Membership_GetAllUsers(String ApplicationName, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
String sQuery = "CREATE TEMPORARY TABLE IF NOT EXISTS PageIndexForUsers (" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"UserId binary(255) not null)";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
sQuery = "TRUNCATE TABLE PageIndexForUsers";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
sQuery = "INSERT INTO PageIndexForUsers (UserId) (" +
"SELECT aspnet_Users.UserId " +
"FROM aspnet_Membership, aspnet_Users " +
"WHERE aspnet_Users.ApplicationId = @ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId " +
"ORDER BY aspnet_Users.UserName)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
int rowcount = cmd.ExecuteNonQuery();
return rowcount;
}
internal static int aspnet_Profile_GetProfiles(String ApplicationName, int ProfileAuthOptions,
int PageIndex, int PageSize, MySqlParameter[] args, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT * FROM aspnet_Profile WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
String UserNameToMatch = "";
DateTime InactiveSinceDate = new DateTime(1754, 1, 1, 0, 0, 0);
foreach (MySqlParameter arg in args)
{
if (arg.ParameterName == "@UserNameToMatch")
{
UserNameToMatch = (String)arg.Value;
}
else if (arg.ParameterName == "InactiveSinceDate")
{
InactiveSinceDate = (DateTime)arg.Value;
}
}
Guid ApplicationId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 0;
}
int PageLowerBound = PageSize * PageIndex;
int PageUpperBound = PageSize - 1 + PageLowerBound;
sQuery = "CREATE TEMPORARY TABLE IF NOT EXISTS PageIndexForUsers (" +
"IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
"UserId binary(255) not null)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
sQuery = "TRUNCATE TABLE PageIndexForUsers";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.ExecuteNonQuery();
sQuery = "INSERT INTO PageIndexForUsers (UserId) ( " +
"SELECT aspnet_Users.UserId " +
"FROM aspnet_Users, aspnet_Profile " +
"WHERE ApplicationId = @ApplicationId " +
"AND aspnet_Users.UserId = aspnet_Profile.UserId " +
"AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate) " +
"AND ( (@ProfileAuthOptions = 2) " +
"OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) " +
"OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0) " +
") " +
"AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE @UserNameToMatch) " +
"ORDER BY UserName )";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", MySqlDbType.DateTime, InactiveSinceDate));
cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", MySqlDbType.Int32, ProfileAuthOptions));
cmd.Parameters.Add(CreateInputParam("@UserNameToMatch", MySqlDbType.VarChar, UserNameToMatch.ToLower()));
cmd.ExecuteNonQuery();
sQuery = "SELECT aspnet_Users.UserName, aspnet_Users.IsAnonymous, aspnet_Users.LastActivityDate, " +
"aspnet_Profile.LastUpdatedDate, LENGTH(aspnet_Profile.PropertyNames) + " +
"LENGTH(aspnet_Profile.PropertyValuesString) + LENGTH(aspnet_Profile.PropertyValuesBinary) " +
"FROM aspnet_Users, aspnet_Profile, PageIndexForUsers WHERE aspnet_Users.UserId = " +
"aspnet_Profile.UserId AND aspnet_Profile.UserId = PageIndexForUsers.UserId AND " +
"PageIndexForUsers.IndexId >= @PageLowerBound AND PageIndexForUsers.IndexId <= @PageUpperBound ";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@PageLowerBound", MySqlDbType.Int32, PageLowerBound));
outcmd.Parameters.Add(CreateInputParam("@PageUpperBound", MySqlDbType.Int32, PageUpperBound));
return 0;
}
internal static int aspnet_Membership_GetPasswordWithFormat(String ApplicationName, String UserName,
Boolean UpdateLastLoginActivityDate, DateTime CurrentTimeUtc, SqlConnectionHolder holder,
ref MySqlCommand outcmd)
{
outcmd = new MySqlCommand("SELECT * FROM aspnet_Membership WHERE 1=0", holder.Connection);
Boolean IsLockedOut = false;
Guid UserId = Guid.Empty;
String Password = "";
String PasswordSalt = "";
int PasswordFormat = 0;
int FailedPasswordAttemptCount = 0;
int FailedPasswordAnswerAttemptCount = 0;
Boolean IsApproved = true;
DateTime LastActivityDate = new DateTime(1754, 1, 1, 0, 0, 0); ;
DateTime LastLoginDate = new DateTime(1754, 1, 1, 0, 0, 0); ;
String sQuery = "SELECT aspnet_Users.UserId, " +
"aspnet_Membership.IsLockedOut, " +
"Password, " +
"PasswordFormat, " +
"PasswordSalt, " +
"aspnet_Membership.FailedPasswordAttemptCount, " +
"aspnet_Membership.FailedPasswordAnswerAttemptCount, " +
"aspnet_Membership.IsApproved, " +
"LastActivityDate, " +
"LastLoginDate " +
"FROM aspnet_Applications, aspnet_Users, aspnet_Membership " +
"WHERE @ApplicationName = aspnet_Applications.LoweredApplicationName AND " +
"aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND " +
"aspnet_Users.UserId = aspnet_Membership.UserId AND " +
"@UserName = aspnet_Users.LoweredUserName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
IsLockedOut = reader.GetBoolean(1);
Password = reader.GetString(2);
PasswordFormat = reader.GetInt32(3);
PasswordSalt = reader.GetString(4);
FailedPasswordAttemptCount = reader.GetInt32(5);
FailedPasswordAnswerAttemptCount = reader.GetInt32(6);
IsApproved = reader.GetBoolean(7);
LastActivityDate = reader.GetDateTime(8);
LastLoginDate = reader.GetDateTime(9);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
if (IsLockedOut == true)
{
return 99;
}
if (UpdateLastLoginActivityDate == true && IsApproved == true)
{
sQuery = "UPDATE aspnet_Users SET LastActivityDate = @LastActivityDate WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@LastActivityDate", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sQuery = "UPDATE aspnet_Membership SET LastLoginDate = @LastLoginDate WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@LastLoginDate", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
LastActivityDate = CurrentTimeUtc;
LastLoginDate = CurrentTimeUtc;
}
sQuery = "SELECT @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount, " +
"@FailedPasswordAnswerAttemptCount, @IsApproved, CAST(@LastLoginDate AS DATETIME), CAST(@LastActivityDate AS DATETIME)";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@Password", MySqlDbType.VarChar, Password));
outcmd.Parameters.Add(CreateInputParam("@PasswordFormat", MySqlDbType.Int32, PasswordFormat));
outcmd.Parameters.Add(CreateInputParam("@PasswordSalt", MySqlDbType.VarChar, PasswordSalt));
outcmd.Parameters.Add(CreateInputParam("@FailedPasswordAttemptCount", MySqlDbType.Int32, FailedPasswordAttemptCount));
outcmd.Parameters.Add(CreateInputParam("@FailedPasswordAnswerAttemptCount", MySqlDbType.Int32, FailedPasswordAnswerAttemptCount));
outcmd.Parameters.Add(CreateInputParam("@IsApproved", MySqlDbType.Bit, IsApproved));
outcmd.Parameters.Add(CreateInputParam("@LastLoginDate", MySqlDbType.DateTime, LastLoginDate));
outcmd.Parameters.Add(CreateInputParam("@LastActivityDate", MySqlDbType.DateTime, LastActivityDate));
return 0;
}
internal static int aspnet_Profile_GetProperties(String ApplicationName, String UserName,
DateTime CurrentTimeUtc, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT UserId FROM aspnet_Users WHERE 1=0";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
Guid ApplicationId = Guid.Empty;
Guid UserId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary " +
"FROM aspnet_Profile " +
"WHERE UserId = @UserId LIMIT 0 , 1";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
sQuery = "UPDATE aspnet_Users SET LastActivityDate=@CurrentTimeUtc WHERE UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
return 0;
}
internal static int aspnet_Membership_GetUserByName(String ApplicationName, String UserName,
DateTime CurrentTimeUtc, Boolean UpdateLastActivity, SqlConnectionHolder holder,
ref MySqlCommand outcmd)
{
String sQuery = "SELECT UserId FROM aspnet_Membership WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
Guid UserId = Guid.Empty;
Guid ApplicationId = Guid.Empty;
sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return -1;
}
sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
return -1;
}
if (UpdateLastActivity)
{
sQuery = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc " +
"FROM aspnet_Users WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if ((long)cmd.ExecuteNonQuery() == 0)
{
return -1;
}
}
sQuery = "SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, " +
"aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, " +
"aspnet_Users.LastActivityDate, aspnet_Membership.LastPasswordChangedDate, " +
"aspnet_Users.UserId, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate " +
"FROM aspnet_Applications, aspnet_Users, aspnet_Membership " +
"WHERE @ApplicationName = aspnet_Applications.LoweredApplicationName AND " +
"aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND " +
"@UserName = aspnet_Users.LoweredUserName AND aspnet_Users.UserId = aspnet_Membership.UserId LIMIT 0,1 ";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
outcmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
return 0;
}
internal static int aspnet_Profile_SetProperties(String ApplicationName, String PropertyNames,
String PropertyValuesString, Byte[] PropertyValuesBinary, String UserName, Boolean IsUserAnonymous,
DateTime CurrentTimeUtc, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
Guid UserId = Guid.Empty;
DateTime LastActivityDate = CurrentTimeUtc;
MySqlTransaction sqlTrans = null;
ApplicationId = MySqlStoredProcedures.aspnet_Applications_CreateApplication(holder, ApplicationName);
String sQuery = "SELECT UserId FROM aspnet_Users WHERE LoweredUserName = @UserName AND ApplicationId = @ApplicationId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserName", MySqlDbType.VarChar, UserName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
UserId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (UserId.Equals(Guid.Empty))
{
UserId = MySqlStoredProcedures.aspnet_Users_CreateUser(ApplicationId, UserName, IsUserAnonymous,
LastActivityDate, holder);
}
sQuery = "UPDATE aspnet_Users SET LastActivityDate=@CurrentTimeUtc WHERE UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sQuery = "SELECT COUNT(UserId) FROM aspnet_Profile WHERE UserId = @UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if ((long)cmd.ExecuteScalar() > 0)
{
sQuery = "UPDATE aspnet_Profile SET PropertyNames = @PropertyNames, " +
"PropertyValuesString = @PropertyValuesString, " +
"PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate = @CurrentTimeUtc " +
"WHERE UserId = @UserId";
}
else
{
sQuery = "INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, " +
"PropertyValuesBinary, LastUpdatedDate) VALUES (@UserId, @PropertyNames, " +
"@PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)";
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@PropertyName", MySqlDbType.Text, PropertyNames));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesString", MySqlDbType.Text, PropertyValuesString));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesBinary", MySqlDbType.Blob, PropertyValuesBinary));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_Membership_GetUserByUserId(Guid UserId, DateTime CurrentTimeUtc,
Boolean UpdateLastActivity, SqlConnectionHolder holder, ref MySqlCommand outcmd)
{
String sQuery = "SELECT UserId FROM aspnet_Membership WHERE 1=0";
outcmd = new MySqlCommand(sQuery, holder.Connection);
MySqlCommand cmd;
if (UpdateLastActivity)
{
sQuery = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc " +
"FROM aspnet_Users WHERE @UserId = UserId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", MySqlDbType.DateTime, CurrentTimeUtc));
cmd.Parameters.Add(CreateInputParam("@UserId", MySqlDbType.Binary, UserId));
if ((long)cmd.ExecuteNonQuery() == 0)
{
return -1;
}
}
sQuery = "SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, " +
"aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, " +
"aspnet_Users.LastActivityDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserName, " +
"aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate " +
"FROM aspnet_Users, aspnet_Membership " +
"WHERE @UserId = aspnet_Users.UserId AND aspnet_Users.UserId = aspnet_Membership.UserId ";
outcmd = new MySqlCommand(sQuery, holder.Connection);
outcmd.Parameters.Add(CreateInputParam("UserId", MySqlDbType.Binary, UserId));
return 0;
}
internal static int aspnet_Roles_CreateRole(String ApplicationName, String RoleName, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
MySqlTransaction sqlTrans = null;
ApplicationId = aspnet_Applications_CreateApplication(holder, ApplicationName);
String sQuery = "SELECT COUNT(RoleId) FROM aspnet_Roles WHERE LoweredRoleName = @RoleName AND ApplicationId = @ApplicationId";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, RoleName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
if ((long)cmd.ExecuteScalar() > 0)
{
return 1;
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
Guid RoleId = Guid.NewGuid();
sQuery = "INSERT INTO aspnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName) " +
"VALUES (@ApplicationId, @RoleId, @RoleName, @LoweredRoleName)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, RoleName));
cmd.Parameters.Add(CreateInputParam("@LoweredRoleName", MySqlDbType.VarChar, RoleName.ToLower()));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static int aspnet_Roles_DeleteRole(String ApplicationName, String RoleName,
Boolean DeleteOnlyIfRoleIsEmpty, SqlConnectionHolder holder)
{
Guid ApplicationId = Guid.Empty;
Guid RoleId = Guid.Empty;
MySqlTransaction sqlTrans = null;
String sQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE @ApplicationName = LoweredApplicationName";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@ApplicationName", MySqlDbType.VarChar, ApplicationName.ToLower()));
MySqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
ApplicationId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (ApplicationId.Equals(Guid.Empty))
{
return 1;
}
sQuery = "SELECT RoleId FROM aspnet_Roles WHERE LoweredRoleName = @RoleName AND " +
"ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleName", MySqlDbType.VarChar, RoleName.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
RoleId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (RoleId.Equals(Guid.Empty))
{
return 1;
}
if (DeleteOnlyIfRoleIsEmpty == true)
{
sQuery = "SELECT Count(RoleId) FROM aspnet_UsersInRoles WHERE @RoleId = RoleId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
if ((long)cmd.ExecuteScalar() > 0)
{
return 2;
}
}
try
{
sqlTrans = holder.Connection.BeginTransaction();
sQuery = "DELETE FROM aspnet_UsersInRoles WHERE @RoleId = RoleId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
cmd.ExecuteNonQuery();
sQuery = "DELETE FROM aspnet_Roles WHERE @RoleId = RoleId AND ApplicationId = @ApplicationId";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Transaction = sqlTrans;
cmd.Parameters.Add(CreateInputParam("@RoleId", MySqlDbType.Binary, RoleId));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.ExecuteNonQuery();
sqlTrans.Commit();
}
catch
{
try
{
sqlTrans.Rollback();
return -1;
}
catch
{
throw;
}
}
return 0;
}
internal static Guid aspnet_Paths_CreatePath(Guid ApplicationId, String Path, SqlConnectionHolder holder)
{
Guid PathId = Guid.Empty;
String sQuery = "SELECT aspnet_Paths.PathId FROM aspnet_Paths WHERE aspnet_Paths.ApplicationId = @ApplicationId AND aspnet_Paths.LoweredPath = @Path";
MySqlCommand cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path.ToLower()));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
try
{
if (reader.Read())
{
PathId = reader.GetGuid(0);
}
}
catch
{
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
if (PathId.Equals(Guid.Empty))
{
PathId = Guid.NewGuid();
sQuery = "INSERT aspnet_Paths (PathId, ApplicationId, Path, LoweredPath) VALUES " +
"(@PathId, @ApplicationId, @Path, @LoweredPath)";
cmd = new MySqlCommand(sQuery, holder.Connection);
cmd.Parameters.Add(CreateInputParam("@PathId", MySqlDbType.Binary, PathId));
cmd.Parameters.Add(CreateInputParam("@ApplicationId", MySqlDbType.Binary, ApplicationId));
cmd.Parameters.Add(CreateInputParam("@Path", MySqlDbType.VarChar, Path));
cmd.Parameters.Add(CreateInputParam("@LoweredPath", MySqlDbType.VarChar, Path.ToLower()));
cmd.ExecuteNonQuery();
}
return PathId;
}
