/// <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;
                }
            }
        }
        /// <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>
        /// 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>
        /// 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>
        /// 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);
        }