/// <summary>
/// Removes a user from the membership data source.
/// </summary>
/// <param name="username">The name of the user to delete.</param>
/// <param name="deleteAllRelatedData">true to delete data related to the user from the database; false to leave data related to the user in the database.</param>
/// <returns>
/// true if the user was successfully deleted; otherwise, false.
/// </returns>
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
int userId = GetUserId(conn, username);
if (-1 == userId) return false;
// if we are supposed to delete all related data, then delegate that to those providers
if (deleteAllRelatedData)
{
MySQLRoleProvider.DeleteUserData(conn, userId);
MySQLProfileProvider.DeleteUserData(conn, userId);
}
string sql = @"DELETE {0}m
FROM my_aspnet_Users u, my_aspnet_Membership m
WHERE u.id=m.userId AND u.id=@userId";
MySqlCommand cmd = new MySqlCommand(
String.Format(sql, deleteAllRelatedData ? "u," : ""), conn);
cmd.Parameters.AddWithValue("@appId", app.FetchId(conn));
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "DeleteUser");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Adds the users to roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (String.IsNullOrEmpty(rolename))
throw new ArgumentException(Resources.IllegalRoleName, "rolenames");
if (!RoleExists(rolename))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
if (String.IsNullOrEmpty(username))
throw new ArgumentException(Resources.IllegalUserName, "usernames");
if (username.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
throw new ProviderException(Resources.UserIsAlreadyInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand(
"INSERT INTO my_aspnet_UsersInRoles VALUES(@userId, @roleId)", connection);
cmd.Parameters.Add("@userId", MySqlDbType.Int32);
cmd.Parameters.Add("@roleId", MySqlDbType.Int32);
foreach (string username in usernames)
{
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection,
username, app.FetchId(connection), true);
foreach (string rolename in rolenames)
{
int roleId = GetRoleId(connection, rolename);
cmd.Parameters[0].Value = userId;
cmd.Parameters[1].Value = roleId;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "AddUsersToRoles");
throw;
}
}
}
protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection");
MySqlConnection conn = connection as MySqlConnection;
if (conn == null)
throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.ConnectionString = conn.ConnectionString;
string dbName = builder.Database;
builder.Database = null;
using (MySqlConnection c = new MySqlConnection(builder.ConnectionString))
{
c.Open();
DataTable table = c.GetSchema("Databases", new string[] { dbName });
if (table != null && table.Rows.Count == 1) return true;
return false;
}
}
/// <summary>
/// Gets the users in role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <returns>A string array containing the names of all the users
/// who are members of the specified role. </returns>
public override string[] GetUsersInRole(string rolename)
{
List<string> users = new List<string>();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
int roleId = GetRoleId(connection, rolename);
string sql = @"SELECT u.name FROM my_aspnet_Users u JOIN
my_aspnet_UsersInRoles uir ON uir.userId=u.id AND uir.roleId=@roleId
WHERE u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@roleId", roleId);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
users.Add(reader.GetString(0));
}
}
return users.ToArray();
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "GetUsersInRole");
throw;
}
}
/// <summary>
/// Removes the users from roles.
/// </summary>
/// <param name="usernames">The usernames.</param>
/// <param name="rolenames">The rolenames.</param>
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
if (rolenames == null || rolenames.Length == 0) return;
if (usernames == null || usernames.Length == 0) return;
foreach (string rolename in rolenames)
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!(IsUserInRole(username, rolename)))
throw new ProviderException(Resources.UserNotInRole);
}
}
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
connection.Open();
txn = connection.BeginTransaction();
string sql = @"DELETE uir FROM my_aspnet_UsersInRoles uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE u.name LIKE @username AND r.name LIKE @rolename
AND u.applicationId=@appId AND r.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.Add("@username", MySqlDbType.VarChar, 255);
cmd.Parameters.Add("@rolename", MySqlDbType.VarChar, 255);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
cmd.Parameters[0].Value = username;
cmd.Parameters[1].Value = rolename;
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (MySqlException e)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUsersFromRoles");
throw;
}
}
}
/// <summary>
/// Deletes the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <param name="throwOnPopulatedRole">if set to <c>true</c> [throw on populated role].</param>
/// <returns>true if the role was successfully deleted; otherwise, false. </returns>
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
MySqlTransaction txn = null;
try
{
if (!(RoleExists(rolename)))
throw new ProviderException(Resources.RoleNameNotFound);
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
throw new ProviderException(Resources.CannotDeleteAPopulatedRole);
connection.Open();
txn = connection.BeginTransaction();
// first delete all the user/role mappings with that roleid
MySqlCommand cmd = new MySqlCommand(
@"DELETE uir FROM my_aspnet_UsersInRoles uir JOIN
my_aspnet_Roles r ON uir.roleId=r.id
WHERE r.name LIKE @rolename AND r.applicationId=@appId", connection);
cmd.Parameters.AddWithValue("@rolename", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.ExecuteNonQuery();
// now delete the role itself
cmd.CommandText = @"DELETE FROM my_aspnet_Roles WHERE name=@rolename
AND applicationId=@appId";
cmd.ExecuteNonQuery();
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "DeleteRole");
throw;
}
}
return true;
}
/// <summary>
/// Gets a list of all the roles for the configured applicationName.
/// </summary>
/// <returns>
/// A string array containing the names of all the roles stored in the data source for the configured applicationName.
/// </returns>
public override string[] GetAllRoles()
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
return GetRolesByUserName(connection, null);
}
}
/// <summary>
/// Resets a user's password to a new, automatically generated password.
/// </summary>
/// <param name="username">The user to reset the password for.</param>
/// <param name="answer">The password answer for the specified user.</param>
/// <returns>The new password for the specified user.</returns>
public override string ResetPassword(string username, string answer)
{
if (!(EnablePasswordReset))
throw new NotSupportedException(Resources.PasswordResetNotEnabled);
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// fetch the userid first
int userId = GetUserId(connection, username);
if (-1 == userId)
throw new ProviderException(Resources.UsernameNotFound);
if (answer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(userId, "PasswordAnswer", connection);
throw new ProviderException(Resources.PasswordRequiredForReset);
}
string newPassword = Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs Args = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(Args);
if (Args.Cancel)
{
if (!(Args.FailureInformation == null))
throw Args.FailureInformation;
else
throw new MembershipPasswordException(Resources.PasswordResetCanceledNotValid);
}
MySqlCommand cmd = new MySqlCommand(@"SELECT PasswordAnswer,
PasswordKey, PasswordFormat, IsLockedOut
FROM my_aspnet_Membership WHERE userId=@userId", connection);
cmd.Parameters.AddWithValue("@userId", userId);
string passwordKey = String.Empty;
MembershipPasswordFormat format;
using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
if (reader.GetBoolean("IsLockedOut"))
throw new MembershipPasswordException(Resources.UserIsLockedOut);
object passwordAnswer = reader.GetValue(reader.GetOrdinal("PasswordAnswer"));
passwordKey = reader.GetString("PasswordKey");
format = (MembershipPasswordFormat)reader.GetByte("PasswordFormat");
reader.Close();
if (RequiresQuestionAndAnswer)
{
if (!CheckPassword(answer, (string)passwordAnswer, passwordKey, format))
{
UpdateFailureCount(userId, "PasswordAnswer", connection);
throw new MembershipPasswordException(Resources.IncorrectPasswordAnswer);
}
}
}
cmd.CommandText = @"UPDATE my_aspnet_Membership
SET Password = @pass, LastPasswordChangedDate = @lastPassChange
WHERE userId=@userId";
cmd.Parameters.AddWithValue("@pass",
EncodePassword(newPassword, passwordKey, format));
cmd.Parameters.AddWithValue("@lastPassChange", DateTime.Now);
int rowsAffected = cmd.ExecuteNonQuery();
if (rowsAffected != 1)
throw new MembershipPasswordException(Resources.ErrorResettingPassword);
return newPassword;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "ResetPassword");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Unlocks the user.
/// </summary>
/// <param name="username">The username.</param>
/// <returns>true if the membership user was successfully unlocked;
/// otherwise, false. A value of false is also returned if the user
/// does not exist in the database. </returns>
public override bool UnlockUser(string username)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
int userId = GetUserId(conn, username);
if (-1 == userId) return false;
string sql = @"UPDATE my_aspnet_Membership
SET IsLockedOut = false, LastLockedOutDate = @lastDate
WHERE userId=@userId";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@lastDate", DateTime.Now);
cmd.Parameters.AddWithValue("@userId", userId);
return cmd.ExecuteNonQuery() > 0;
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "UnlockUser");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Gets user information from the data source based on the unique identifier for the membership user. Provides an option to update the last-activity date/time stamp for the user.
/// </summary>
/// <param name="providerUserKey">The unique identifier for the membership user to get information for.</param>
/// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity date/time stamp for the user.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
/// </returns>
public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
MySqlTransaction txn = null;
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
txn = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand("", connection);
cmd.Parameters.AddWithValue("@userId", providerUserKey);
if (userIsOnline)
{
cmd.CommandText =
@"UPDATE my_aspnet_Users SET lastActivityDate = @date WHERE id=@userId";
cmd.Parameters.AddWithValue("@date", DateTime.Now);
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE my_aspnet_Membership SET LastActivityDate=@date WHERE userId=@userId";
cmd.ExecuteNonQuery();
}
cmd.CommandText = @"SELECT m.*,u.name
FROM my_aspnet_Membership m JOIN my_aspnet_Users u ON m.userId=u.id
WHERE u.id=@userId";
MembershipUser user;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read()) return null;
user = GetUserFromReader(reader);
}
txn.Commit();
return user;
}
}
catch (MySqlException e)
{
if (txn != null)
txn.Rollback();
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUser(Object, Boolean)");
throw new ProviderException(exceptionMessage);
}
}
/// <summary>
/// Gets the user name associated with the specified e-mail address.
/// </summary>
/// <param name="email">The e-mail address to search for.</param>
/// <returns>
/// The user name associated with the specified e-mail address. If no match is found, return null.
/// </returns>
public override string GetUserNameByEmail(string email)
{
try
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
string sql = @"SELECT u.name FROM my_aspnet_Users u
JOIN my_aspnet_Membership m ON m.userid=u.id
WHERE m.Email = @email AND u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@appId", app.FetchId(conn));
return (string)cmd.ExecuteScalar();
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUserNameByEmail");
throw new ProviderException(exceptionMessage);
}
}
/// <summary>
/// Gets information from the data source for a user. Provides an option to update the last-activity date/time stamp for the user.
/// </summary>
/// <param name="username">The name of the user to get information for.</param>
/// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity date/time stamp for the user.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
/// </returns>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
try
{
int userId = -1;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
userId = GetUserId(connection, username);
if (-1 == userId) return null;
}
return GetUser(userId, userIsOnline);
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUser(String, Boolean)");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Gets the password for the specified user name from the data source.
/// </summary>
/// <param name="username">The user to retrieve the password for.</param>
/// <param name="answer">The password answer for the user.</param>
/// <returns>
/// The password for the specified user name.
/// </returns>
public override string GetPassword(string username, string answer)
{
if (!EnablePasswordRetrieval)
throw new ProviderException(Resources.PasswordRetrievalNotEnabled);
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
int userId = GetUserId(connection, username);
if (-1 == userId)
throw new ProviderException("Username not found.");
string sql = @"SELECT Password, PasswordAnswer, PasswordKey, PasswordFormat,
IsLockedOut FROM my_aspnet_Membership WHERE userId=@userId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@userId", userId);
using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
reader.Read();
if (reader.GetBoolean("IsLockedOut"))
throw new MembershipPasswordException(Resources.UserIsLockedOut);
string password = reader.GetString("Password");
string passwordAnswer = reader.GetValue(reader.GetOrdinal("PasswordAnswer")).ToString();
string passwordKey = reader.GetString("PasswordKey");
MembershipPasswordFormat format = (MembershipPasswordFormat)reader.GetInt32(3);
reader.Close();
if (RequiresQuestionAndAnswer &&
!(CheckPassword(answer, passwordAnswer, passwordKey, format)))
{
UpdateFailureCount(userId, "PasswordAnswer", connection);
throw new MembershipPasswordException(Resources.IncorrectPasswordAnswer);
}
if (PasswordFormat == MembershipPasswordFormat.Encrypted)
{
password = UnEncodePassword(password, format);
}
return password;
}
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetPassword");
throw new ProviderException(exceptionMessage, e);
}
}
/// <summary>
/// Gets the number of users currently accessing the application.
/// </summary>
/// <returns>
/// The number of users currently accessing the application.
/// </returns>
public override int GetNumberOfUsersOnline()
{
TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT COUNT(*) FROM my_aspnet_Membership m JOIN my_aspnet_Users u
ON m.userId=u.id WHERE m.LastActivityDate > @date AND u.applicationId=@appId",
connection);
cmd.Parameters.AddWithValue("@date", compareTime);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetNumberOfUsersOnline");
throw new ProviderException(exceptionMessage, e);
}
}
private static void UpgradeToCurrent(string connectionString, int version)
{
ResourceManager r = new ResourceManager("MySql.Web.Properties.Resources",
typeof(SchemaManager).Assembly);
if (version == Version) return;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
for (int ver = version + 1; ver <= Version; ver++)
{
string schema = r.GetString(String.Format("schema{0}", ver));
MySqlScript script = new MySqlScript(connection);
script.Query = schema;
script.Execute();
}
}
}
/// <summary>
/// When overridden in a derived class, deletes profile properties
/// and information for profiles that match the supplied list of user names.
/// </summary>
/// <param name="usernames">A string array of user names for
/// profiles to be deleted.</param>
/// <returns>
/// The number of profiles deleted from the data source.
/// </returns>
public override int DeleteProfiles(string[] usernames)
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand queryCmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Users
WHERE applicationId=@appId AND name = @name", c);
queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c));
queryCmd.Parameters.Add("@name", MySqlDbType.VarChar);
MySqlCommand deleteCmd = new MySqlCommand(
"DELETE FROM my_aspnet_Profiles WHERE userId = @userId", c);
deleteCmd.Parameters.Add("@userId", MySqlDbType.UInt64);
int count = 0;
foreach (string name in usernames)
{
queryCmd.Parameters[1].Value = name;
ulong uid = (ulong)queryCmd.ExecuteScalar();
deleteCmd.Parameters[0].Value = uid;
count += deleteCmd.ExecuteNonQuery();
}
return count;
}
}
/// <summary>
/// Creates the role.
/// </summary>
/// <param name="rolename">The rolename.</param>
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') != -1)
throw new ArgumentException(Resources.InvalidCharactersInUserName);
if (RoleExists(rolename))
throw new ProviderException(Resources.RoleNameAlreadyExists);
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Roles Values(NULL, @appId, @name)", connection);
cmd.Parameters.AddWithValue("@appId", app.EnsureId(connection));
cmd.Parameters.AddWithValue("@name", rolename);
cmd.ExecuteNonQuery();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateRole");
throw;
}
}
}
/// <summary>
/// When overridden in a derived class, returns the number of profiles
/// in which the last activity date occurred on or before the specified
/// date.
/// </summary>
/// <param name="authenticationOption">One of the
/// <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values,
/// specifying whether anonymous, authenticated, or both types of profiles
/// are returned.</param>
/// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/>
/// that identifies which user profiles are considered inactive. If the
/// <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/> of
/// a user profile occurs on or before this date and time, the profile
/// is considered inactive.</param>
/// <returns>
/// The number of profiles in which the last activity date occurred on
/// or before the specified date.
/// </returns>
public override int GetNumberOfInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand queryCmd = new MySqlCommand(
@"SELECT COUNT(*) FROM my_aspnet_Users
WHERE applicationId = @appId AND
lastActivityDate < @lastActivityDate",
c);
queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c));
queryCmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate);
if (authenticationOption == ProfileAuthenticationOption.Anonymous)
queryCmd.CommandText += " AND isAnonymous = 1";
else if (authenticationOption == ProfileAuthenticationOption.Authenticated)
queryCmd.CommandText += " AND isAnonymous = 0";
return (int)queryCmd.ExecuteScalar();
}
}
/// <summary>
/// Finds the users in role.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <param name="usernameToMatch">The username to match.</param>
/// <returns>A string array containing the names of all the users where the
/// user name matches usernameToMatch and the user is a member of the specified role. </returns>
public override string[] FindUsersInRole(string rolename, string usernameToMatch)
{
if (!RoleExists(rolename))
throw new ProviderException(Resources.RoleNameNotFound);
List<string> users = new List<string>();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string sql = @"SELECT u.name FROM my_aspnet_UsersInRole uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE r.name LIKE @rolename AND
u.name LIKE @username AND
u.applicationId=@appId";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@username", usernameToMatch);
cmd.Parameters.AddWithValue("@rolename", rolename);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
users.Add(reader.GetString(0));
}
}
return users.ToArray();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "FindUsersInRole");
throw;
}
}
/// <summary>
/// Returns the collection of settings property values for the specified application instance and settings property group.
/// </summary>
/// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> describing the current application use.</param>
/// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyCollection"/> containing the settings property group whose values are to be retrieved.</param>
/// <returns>
/// A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> containing the values for the specified settings property group.
/// </returns>
public override SettingsPropertyValueCollection GetPropertyValues(
SettingsContext context, SettingsPropertyCollection collection)
{
SettingsPropertyValueCollection values = new SettingsPropertyValueCollection();
if (collection.Count < 1) return values;
string username = (string)context["UserName"];
foreach (SettingsProperty property in collection)
{
if (property.PropertyType.IsPrimitive || property.PropertyType == typeof(string))
property.SerializeAs = SettingsSerializeAs.String;
else
property.SerializeAs = SettingsSerializeAs.Xml;
values.Add(new SettingsPropertyValue(property));
}
if (String.IsNullOrEmpty(username))
return values;
// retrieve encoded profile data from the database
try
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Profiles p
JOIN my_aspnet_Users u ON u.id = p.userId
WHERE u.applicationId = @appId AND u.name = @name", c);
cmd.Parameters.AddWithValue("@appId", app.FetchId(c));
cmd.Parameters.AddWithValue("@name", username);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
DecodeProfileData(dt.Rows[0], values);
return values;
}
}
catch (Exception ex)
{
throw new ProviderException(Resources.UnableToRetrieveProfileData, ex);
}
}
/// <summary>
/// Gets a list of the roles that a specified user is in for the configured applicationName.
/// </summary>
/// <param name="username">The user to return a list of roles for.</param>
/// <returns>
/// A string array containing the names of all the roles that the specified user is in for the configured applicationName.
/// </returns>
public override string[] GetRolesForUser(string username)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
return GetRolesByUserName(connection, username);
}
}
/// <summary>
/// Sets the values of the specified group of property settings.
/// </summary>
/// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> describing the current application usage.</param>
/// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> representing the group of property settings to set.</param>
public override void SetPropertyValues(
SettingsContext context, SettingsPropertyValueCollection collection)
{
bool isAuthenticated = (bool)context["IsAuthenticated"];
string username = (string)context["UserName"];
if (String.IsNullOrEmpty(username)) return;
if (collection.Count < 1) return;
string index = String.Empty;
string stringData = String.Empty;
byte[] binaryData = null;
int count = EncodeProfileData(collection, isAuthenticated, ref index, ref stringData, ref binaryData);
if (count < 1) return;
MySqlTransaction txn = null;
// save the encoded profile data to the database
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
txn = connection.BeginTransaction();
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection, username,
app.EnsureId(connection), isAuthenticated);
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Profiles
VALUES (@userId, @index, @stringData, @binaryData, NULL) ON DUPLICATE KEY UPDATE
valueindex=VALUES(valueindex), stringdata=VALUES(stringdata),
binarydata=VALUES(binarydata)", connection);
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@index", index);
cmd.Parameters.AddWithValue("@stringData", stringData);
cmd.Parameters.AddWithValue("@binaryData", binaryData);
count = cmd.ExecuteNonQuery();
if (count == 0)
throw new Exception(Resources.ProfileUpdateFailed);
txn.Commit();
}
catch (Exception ex)
{
if (txn != null)
txn.Rollback();
throw new ProviderException(Resources.ProfileUpdateFailed, ex);
}
}
}
/// <summary>
/// Determines whether [is user in role] [the specified username].
/// </summary>
/// <param name="username">The username.</param>
/// <param name="rolename">The rolename.</param>
/// <returns>
/// <c>true</c> if [is user in role] [the specified username]; otherwise, <c>false</c>.
/// </returns>
public override bool IsUserInRole(string username, string rolename)
{
try
{
// this will refresh the app id if necessary
if (!RoleExists(rolename)) return false;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
string sql = @"SELECT COUNT(*) FROM my_aspnet_UsersInRoles uir
JOIN my_aspnet_Users u ON uir.userId=u.id
JOIN my_aspnet_Roles r ON uir.roleId=r.id
WHERE u.applicationId=@appId AND
u.name LIKE @userName AND r.name LIKE @roleName";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.Parameters.AddWithValue("@userName", username);
cmd.Parameters.AddWithValue("@roleName", rolename);
int count = Convert.ToInt32(cmd.ExecuteScalar());
return count > 0;
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "IsUserInRole");
throw;
}
}
private ProfileInfoCollection GetProfiles(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch, DateTime userInactiveSinceDate,
int pageIndex, int pageSize, out int totalRecords)
{
List<string> whereClauses = new List<string>();
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT p.*, u.name, u.isAnonymous, u.lastActivityDate,
LENGTH(p.stringdata) + LENGTH(p.binarydata) AS profilesize
FROM my_aspnet_Profiles p
JOIN my_aspnet_Users u ON u.id = p.userId
WHERE u.applicationId = @appId", c);
cmd.Parameters.AddWithValue("@appId", app.FetchId(c));
if (usernameToMatch != null)
{
cmd.CommandText += " AND u.name LIKE @userName";
cmd.Parameters.AddWithValue("@userName", usernameToMatch);
}
if (userInactiveSinceDate != DateTime.MinValue)
{
cmd.CommandText += " AND u.lastActivityDate < @lastActivityDate";
cmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate);
}
if (authenticationOption == ProfileAuthenticationOption.Anonymous)
cmd.CommandText += " AND u.isAnonymous = 1";
else if (authenticationOption == ProfileAuthenticationOption.Authenticated)
cmd.CommandText += " AND u.isAnonymous = 0";
cmd.CommandText += String.Format(" LIMIT {0},{1}", pageIndex * pageSize, pageSize);
ProfileInfoCollection pic = new ProfileInfoCollection();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ProfileInfo pi = new ProfileInfo(
reader.GetString("name"),
reader.GetBoolean("isAnonymous"),
reader.GetDateTime("lastActivityDate"),
reader.GetDateTime("lastUpdatedDate"),
reader.GetInt32("profilesize"));
pic.Add(pi);
}
}
cmd.CommandText = "SELECT FOUND_ROWS()";
totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
return pic;
}
}
/// <summary>
/// Roles the exists.
/// </summary>
/// <param name="rolename">The rolename.</param>
/// <returns>true if the role name already exists in the database; otherwise, false. </returns>
public override bool RoleExists(string rolename)
{
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand(
@"SELECT COUNT(*) FROM my_aspnet_Roles WHERE applicationId=@appId
AND name LIKE @name", connection);
cmd.Parameters.AddWithValue("@appId", app.FetchId(connection));
cmd.Parameters.AddWithValue("@name", rolename);
int count = Convert.ToInt32(cmd.ExecuteScalar());
return count != 0;
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(ex, "RoleExists");
throw;
}
}
/// <summary>
/// When overridden in a derived class, deletes all user-profile data
/// for profiles in which the last activity date occurred before the
/// specified date.
/// </summary>
/// <param name="authenticationOption">One of the
/// <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/>
/// values, specifying whether anonymous, authenticated, or both
/// types of profiles are deleted.</param>
/// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/>
/// that identifies which user profiles are considered inactive. If the
/// <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>
/// value of a user profile occurs on or before this date and time, the
/// profile is considered inactive.</param>
/// <returns>
/// The number of profiles deleted from the data source.
/// </returns>
public override int DeleteInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
using (MySqlConnection c = new MySqlConnection(connectionString))
{
c.Open();
MySqlCommand queryCmd = new MySqlCommand(
@"SELECT * FROM my_aspnet_Users
WHERE applicationId=@appId AND
lastActivityDate < @lastActivityDate",
c);
queryCmd.Parameters.AddWithValue("@appId", app.FetchId(c));
queryCmd.Parameters.AddWithValue("@lastActivityDate", userInactiveSinceDate);
if (authenticationOption == ProfileAuthenticationOption.Anonymous)
queryCmd.CommandText += " AND isAnonymous = 1";
else if (authenticationOption == ProfileAuthenticationOption.Authenticated)
queryCmd.CommandText += " AND isAnonymous = 0";
MySqlCommand deleteCmd = new MySqlCommand(
"DELETE FROM my_aspnet_Profiles WHERE userId = @userId", c);
deleteCmd.Parameters.Add("@userId", MySqlDbType.UInt64);
List<ulong> uidList = new List<ulong>();
using (MySqlDataReader reader = queryCmd.ExecuteReader())
{
while (reader.Read())
uidList.Add(reader.GetUInt64("userId"));
}
int count = 0;
foreach (ulong uid in uidList)
{
deleteCmd.Parameters[0].Value = uid;
count += deleteCmd.ExecuteNonQuery();
}
return count;
}
}
protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection");
MySqlConnection conn = connection as MySqlConnection;
if (conn == null)
throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");
string query = DbCreateDatabaseScript(null, storeItemCollection);
using (MySqlConnection c = new MySqlConnection())
{
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder(conn.ConnectionString);
string dbName = sb.Database;
sb.Database = null;
c.ConnectionString = sb.ConnectionString;
c.Open();
string fullQuery = String.Format("CREATE DATABASE `{0}`; USE `{0}`; {1}", dbName, query);
MySqlScript s = new MySqlScript(c, fullQuery);
s.Execute();
}
}
private static int GetSchemaVersion(string connectionString)
{
// retrieve the current schema version
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_SchemaVersion", conn);
try
{
object ver = cmd.ExecuteScalar();
if (ver != null)
return (int)ver;
}
catch (MySqlException ex)
{
if (ex.Number != (int)MySqlErrorCode.NoSuchTable)
throw;
string[] restrictions = new string[4];
restrictions[2] = "mysql_Membership";
DataTable dt = conn.GetSchema("Tables", restrictions);
if (dt.Rows.Count == 1)
return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]);
}
return 0;
}
}
protected override void DbDeleteDatabase(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
{
if (connection == null)
throw new ArgumentNullException("connection");
MySqlConnection conn = connection as MySqlConnection;
if (conn == null)
throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.ConnectionString = conn.ConnectionString;
string dbName = builder.Database;
builder.Database = null;
using (MySqlConnection c = new MySqlConnection(builder.ConnectionString))
{
c.Open();
MySqlCommand cmd = new MySqlCommand(String.Format("DROP DATABASE IF EXISTS `{0}`", dbName), c);
if (commandTimeout.HasValue)
cmd.CommandTimeout = commandTimeout.Value;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Adds a new membership user to the data source.
/// </summary>
/// <param name="username">The user name for the new user.</param>
/// <param name="password">The password for the new user.</param>
/// <param name="email">The e-mail address for the new user.</param>
/// <param name="passwordQuestion">The password question for the new user.</param>
/// <param name="passwordAnswer">The password answer for the new user</param>
/// <param name="isApproved">Whether or not the new user is approved to be validated.</param>
/// <param name="providerUserKey">The unique identifier from the membership data source for the user.</param>
/// <param name="status">A <see cref="T:System.Web.Security.MembershipCreateStatus"/> enumeration value indicating whether the user was created successfully.</param>
/// <returns>
/// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the information for the newly created user.
/// </returns>
public override MembershipUser CreateUser(string username, string password,
string email, string passwordQuestion, string passwordAnswer,
bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
ValidatePasswordEventArgs Args = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(Args);
if (Args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && !String.IsNullOrEmpty(GetUserNameByEmail(email)))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
ValidateQA(passwordQuestion, passwordAnswer);
// now try to validate the password
if (!ValidatePassword(password, "password", false))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// now check to see if we already have a member by this name
MembershipUser u = GetUser(username, false);
if (u != null)
{
status = MembershipCreateStatus.DuplicateUserName;
return null;
}
string passwordKey = GetPasswordKey();
DateTime createDate = DateTime.Now;
MySqlTransaction transaction = null;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
transaction = connection.BeginTransaction();
// either create a new user or fetch the existing user id
int userId = SchemaManager.CreateOrFetchUserId(connection, username,
app.EnsureId(connection), true);
MySqlCommand cmd = new MySqlCommand(
@"INSERT INTO my_aspnet_Membership
VALUES(@userId, @email, @comment, @password, @passwordKey,
@passwordFormat, @passwordQuestion, @passwordAnswer,
@isApproved, @lastActivityDate, @lastLoginDate,
@lastPasswordChangedDate, @creationDate,
@isLockedOut, @lastLockedOutDate, @failedPasswordAttemptCount,
@failedPasswordAttemptWindowStart, @failedPasswordAnswerAttemptCount,
@failedPasswordAnswerAttemptWindowStart)",
connection);
cmd.Parameters.AddWithValue("@userId", userId);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@comment", "");
cmd.Parameters.AddWithValue("@password",
EncodePassword(password, passwordKey, PasswordFormat));
cmd.Parameters.AddWithValue("@passwordKey", passwordKey);
cmd.Parameters.AddWithValue("@passwordFormat", PasswordFormat);
cmd.Parameters.AddWithValue("@passwordQuestion", passwordQuestion);
cmd.Parameters.AddWithValue("@passwordAnswer",
EncodePassword(passwordAnswer, passwordKey, PasswordFormat));
cmd.Parameters.AddWithValue("@isApproved", isApproved);
cmd.Parameters.AddWithValue("@lastActivityDate", createDate);
cmd.Parameters.AddWithValue("@lastLoginDate", createDate);
cmd.Parameters.AddWithValue("@lastPasswordChangedDate", createDate);
cmd.Parameters.AddWithValue("@creationDate", createDate);
cmd.Parameters.AddWithValue("@isLockedOut", false);
cmd.Parameters.AddWithValue("@lastLockedOutDate", createDate);
cmd.Parameters.AddWithValue("@failedPasswordAttemptCount", 0);
cmd.Parameters.AddWithValue("@failedPasswordAttemptWindowStart", createDate);
cmd.Parameters.AddWithValue("@failedPasswordAnswerAttemptCount", 0);
cmd.Parameters.AddWithValue("@failedPasswordAnswerAttemptWindowStart", createDate);
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
status = MembershipCreateStatus.Success;
else
status = MembershipCreateStatus.UserRejected;
transaction.Commit();
}
catch (MySqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateUser");
status = MembershipCreateStatus.ProviderError;
if (transaction != null)
transaction.Rollback();
return null;
}
}
return GetUser(username, false);
}