//*****************************************************************************
// This code file is part of the Universal Provider Framework for SharePoint.
// This file was written by Adam Buenz [WSS MVP] of ARB Security Solutions, LLC
//                  http://www.sharepointsecurity.com
//
// This file and its parts is free for re-distribution, for use in both free 
// and commercial applications, however this header must remain intact for legal 
// use. The Universal Provider Framework is distributed in the hope that it will
// be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
//*****************************************************************************

//**************************************
// Current Version: 1.0.0.0 (Beta)
//**************************************

// namespace references
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Web.Hosting;
using System.Web.Security;

namespace Universal.SharePointProvider.Framework
{
    /// <summary>
    /// Inherit out of the class that provides role management services
    /// </summary>
    public abstract class SharePointRoleProvider : RoleProvider
    {
        /// <summary>
        /// Declare constructor for the SharePointRoleProvider class
        /// </summary>
        /// <param name="defaultName"></param>
        /// <param name="defaultDescription"></param>
        /// <param name="provider"></param>
        protected SharePointRoleProvider(string defaultName, string defaultDescription, DbProviderFactory provider)
        {
            this.eventLog = "Application";
            this.exceptionMessage = "An exception occurred with the SharePoint role provider. Please contact your SharePoint administrator.";
            this.defaultName = defaultName;
            this.defaultDescription = defaultDescription;
            this.provider = provider;
        }

        /// <summary>
        /// Declare variables
        /// </summary>
        private string connectionString;
        private readonly string defaultDescription;
        private readonly string defaultName;
        private string eventLog;
        private string exceptionMessage;
        private string pApplicationName;
        private ConnectionStringSettings pConnectionStringSettings;
        private readonly DbProviderFactory provider;
        private bool pWriteExceptionsToEventLog;
        private const string RoleMustBeDefined = "SharePoint role name requires definition.";
        private const string UserMustBeDefined = "SharePoint user name requires definition.";
        private SharePointUsersProvider usersProvider;
        
        public override string ApplicationName
        {
            get
            {
                return this.pApplicationName;
            }
            set
            {
                this.pApplicationName = value;
            }
        }

        protected virtual string SqlDeleteRole
        {
            get
            {
                return "DELETE FROM aspnet_usersinroles WHERE roleid = (SELECT roleid FROM aspnet_roles WHERE LOWER(applicationname) = :applicationname AND LOWER(rolename) = :rolename)";
            }
        }

        protected virtual string SqlRemoveUsersFromRoles
        {
            get
            {
                return "DELETE FROM aspnet_usersinroles WHERE roleid = (SELECT roleid FROM aspnet_roles WHERE LOWER(applicationname) = :applicationname1 AND LOWER(rolename) = :rolename)  AND userid = (SELECT userid FROM aspnet_users WHERE LOWER(applicationname) = :applicationname2 AND LOWER(username) = :username)";
            }
        }

        public bool WriteExceptionsToEventLog
        {
            get
            {
                return this.pWriteExceptionsToEventLog;
            }
            set
            {
                this.pWriteExceptionsToEventLog = value;
            }
        }

        /// <summary>
        /// Add users to a pluggable role provider data store
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            DbConnection connectionDB = this.provider.CreateConnection();
            try
            {
                connectionDB.ConnectionString = this.connectionString;
                connectionDB.Open();
                DbTransaction transactionAddUserDB = connectionDB.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    IDbCommand commandSelectDB = this.CreateCommand("SELECT u.userid, r.roleid FROM aspnet_users u, aspnet_roles r WHERE LOWER(u.applicationname) = :applicationname and LOWER(u.applicationname) = LOWER(r.applicationname) AND LOWER(u.username) = :username AND LOWER(r.rolename) = :rolename", connectionDB);
                    DbParameter parameterAppName = this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower());
                    DbParameter parameterUserName = this.CreateParameter("username", DbType.String, 0xff, "");
                    DbParameter parameterRoleName = this.CreateParameter("rolename", DbType.String, 0xff, "");
                    commandSelectDB.Parameters.Add(parameterAppName);
                    commandSelectDB.Parameters.Add(parameterUserName);
                    commandSelectDB.Parameters.Add(parameterRoleName);
                    IDbCommand commandInsertDB = this.CreateCommand("INSERT INTO aspnet_usersinroles (userid, roleid) VALUES (:userid, :roleid)", connectionDB);
                    DbParameter parameterUserID = this.CreateParameter("userid", DbType.Guid, 0xff, null);
                    DbParameter parameterRoleID = this.CreateParameter("roleid", DbType.Guid, 0xff, null);
                    commandInsertDB.Parameters.Add(parameterUserID);
                    commandInsertDB.Parameters.Add(parameterRoleID);
                    for (int iUser = 0; iUser < usernames.Length; iUser++)
                    {
                        string user = usernames[iUser];
                        if (string.IsNullOrEmpty(user))
                        {
                            throw new ArgumentException("The SharePoint user name requires definition.");
                        }
                        for (int iRole = 0; iRole < roleNames.Length; iRole++)
                        {
                            string role = roleNames[iRole];
                            if (string.IsNullOrEmpty(role))
                            {
                                throw new ArgumentException("The SharePoint role name requires definition.");
                            }
                            parameterUserName.Value = user.ToLower();
                            parameterRoleName.Value = role.ToLower();
                            IDataReader readerDBRole = commandSelectDB.ExecuteReader();
                            if (!readerDBRole.Read())
                            {
                                throw new ProviderException(string.Format("SharePoint user {0} or SharePoint role {1} was not located", user, role));
                            }
                            Guid userGUID = (Guid) this.ReadField(readerDBRole, 0, DbType.Guid);
                            Guid roleGUID = (Guid) this.ReadField(readerDBRole, 1, DbType.Guid);
                            this.ApplyParameterInfo(parameterUserID, DbType.Guid, userGUID);
                            this.ApplyParameterInfo(parameterRoleID, DbType.Guid, roleGUID);
                            readerDBRole.Close();
                            commandInsertDB.ExecuteNonQuery();
                        }
                    }
                    transactionAddUserDB.Commit();
                }
                catch (Exception exception)
                {
                    transactionAddUserDB.Rollback();
                    throw exception;
                }
            }
            catch (ProviderException exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "AddUsersToRoles");
                }
                throw exception;
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "AddUsersToRoles");
                    throw new ProviderException(this.exceptionMessage, exception);
                }
                throw exception;
            }
            finally
            {
                connectionDB.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        protected virtual void ApplyParameterInfo(DbParameter parameter, DbType dbType, object value)
        {
            // Set the DBtype parameter
            parameter.DbType = dbType;
            // Set the value of the parameter
            parameter.Value = value;
        }

        private DbCommand CreateCommand(string commandText, DbConnection connection)
        {
            DbCommand roleDBCommand = this.provider.CreateCommand();
            roleDBCommand.Connection = connection;
            roleDBCommand.CommandText = commandText;
            return roleDBCommand;
        }

        private DbParameter CreateParameter(string name, DbType dbType, object value)
        {
            DbParameter roleDBParameter = this.provider.CreateParameter();
            roleDBParameter.ParameterName = name;
            this.ApplyParameterInfo(roleDBParameter, dbType, value);
            return roleDBParameter;
        }

        private DbParameter CreateParameter(string name, DbType dbType, int size, object value)
        {
            DbParameter roleDBParameter = this.provider.CreateParameter();
            roleDBParameter.ParameterName = name;
            roleDBParameter.Size = size;
            this.ApplyParameterInfo(roleDBParameter, dbType, value);
            return roleDBParameter;
        }

        /// <summary>
        /// Create a role in the custom role data store
        /// </summary>
        /// <param name="roleName"></param>
        public override void CreateRole(string roleName)
        {
            if (string.IsNullOrEmpty(roleName) || (roleName.IndexOf(',') >= 0))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            if (this.RoleExists(roleName))
            {
                throw new ProviderException("The SharePoint role already exists.");
            }
            DbConnection createRoleDbConnection = this.provider.CreateConnection();
            try
            {
                createRoleDbConnection.ConnectionString = this.connectionString;
                Guid guid1 = Guid.NewGuid();
                IDbCommand command1 = this.CreateCommand("INSERT INTO aspnet_roles (applicationname, roleid, rolename) VALUES (:applicationname, :roleid, :rolename)", createRoleDbConnection);
                command1.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName));
                command1.Parameters.Add(this.CreateParameter("roleid", DbType.Guid, 0xff, guid1));
                command1.Parameters.Add(this.CreateParameter("rolename", DbType.String, 0xff, roleName));
                createRoleDbConnection.Open();
                command1.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "CreateRole");
                    throw new ProviderException(this.exceptionMessage, exception);
                }
                throw exception;
            }
            finally
            {
                createRoleDbConnection.Close();
            }
        }

        /// <summary>
        /// Delete a role in the custom role data store
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="throwOnPopulatedRole"></param>
        /// <returns></returns>
        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            DbConnection delRolesDbConnection = this.provider.CreateConnection();
            delRolesDbConnection.ConnectionString = this.connectionString;
            bool flagValid = false;
            try
            {
                if (this.HasUsersRoles("rolename", roleName.ToLower(), false, false) && throwOnPopulatedRole)
                {
                    throw new ProviderException("There are SharePoint users currently associated with the specified SharePoint role");
                }
                IDbCommand delRolesDbCommand = this.CreateCommand(this.SqlDeleteRole, delRolesDbConnection);
                delRolesDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                delRolesDbCommand.Parameters.Add(this.CreateParameter("rolename", DbType.String, 0xff, roleName.ToLower()));
                delRolesDbConnection.Open();
                delRolesDbCommand.ExecuteNonQuery();
                Guid.NewGuid();
                delRolesDbCommand.Parameters.Clear();
                delRolesDbCommand.CommandText = "DELETE FROM aspnet_roles WHERE LOWER(applicationname) = :applicationname AND LOWER(rolename) = :rolename";
                delRolesDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                delRolesDbCommand.Parameters.Add(this.CreateParameter("rolename", DbType.String, 0xff, roleName.ToLower()));
                flagValid = delRolesDbCommand.ExecuteNonQuery() > 0;
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "DeleteRole");
                    throw new ProviderException(this.exceptionMessage, exception);
                }
                throw exception;
            }
            finally
            {
                delRolesDbConnection.Close();
            }
            return flagValid;
        }

        /// <summary>
        /// Find all of the users in the passed in role in the custom role data store
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="usernameToMatch"></param>
        /// <returns></returns>
        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            if (string.IsNullOrEmpty(usernameToMatch))
            {
                throw new ArgumentException("The SharePoint user name requires definition.");
            }
            string[] roleArray = this.QueryUsersRoles("username", "rolename", roleName.ToLower(), usernameToMatch.ToLower());
            if (((roleArray == null) || (roleArray.Length < 0)) && !this.RoleExists(roleName))
            {
                throw new ProviderException("The provided SharePoint role name is not valid.");
            }
            return roleArray;
        }

        /// <summary>
        /// Get all of the roles in the custom role provider data store
        /// </summary>
        /// <returns></returns>
        public override string[] GetAllRoles()
        {
            ArrayList roleList = new ArrayList();
            DbConnection allRolesDbConnection = this.provider.CreateConnection();
            allRolesDbConnection.ConnectionString = this.connectionString;
            string textQuery = "SELECT rolename FROM aspnet_roles  WHERE LOWER(applicationname) = :applicationname";
            IDbCommand allRolesDbCommand = this.CreateCommand(textQuery, allRolesDbConnection);
            allRolesDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            try
            {
                allRolesDbConnection.Open();
                using (IDataReader allRolesDbReader = allRolesDbCommand.ExecuteReader())
                {
                    while (allRolesDbReader.Read())
                    {
                        roleList.Add(allRolesDbReader.GetString(0));
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "QueryRoles");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                allRolesDbConnection.Close();
            }
            return (string[]) roleList.ToArray(typeof(string));
        }

        /// <summary>
        /// Get the roles for a specified username
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public override string[] GetRolesForUser(string username)
        {
            if (string.IsNullOrEmpty(username))
            {
                throw new ArgumentException("The SharePoint user name requires definition.");
            }
            string[] userArray = this.QueryUsersRoles("rolename", "username", username.ToLower(), null);
            if (((userArray == null) || (userArray.Length < 0)) && !this.UserExists(username))
            {
                throw new ProviderException("The provided SharePoint username is not valid.");
            }
            return userArray;
        }

        /// <summary>
        /// Get the Users in a specified role name
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public override string[] GetUsersInRole(string roleName)
        {
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            string[] roleArray = this.QueryUsersRoles("username", "rolename", roleName.ToLower(), null);
            if (((roleArray == null) || (roleArray.Length < 0)) && !this.RoleExists(roleName))
            {
                throw new ProviderException("The provided SharePoint role name is not valid.");
            }
            return roleArray;
        }

        internal abstract SharePointUsersProvider GetUsersProvider();


        private bool HasUsersRoles(string queryParameterName, string expression, bool useLike, bool caseSensitive)
        {
            DbConnection hasUserDbConnection = this.provider.CreateConnection();
            hasUserDbConnection.ConnectionString = this.connectionString;
            string textQuery = "SELECT COUNT(*) FROM aspnet_users u, aspnet_roles r, aspnet_usersinroles ur WHERE LOWER(u.applicationname) = :applicationname AND LOWER(r.applicationname) = LOWER(u.applicationname) AND u.userid = ur.userid AND r.roleid = ur.roleid AND ";
            if (caseSensitive)
            {
                if (!useLike)
                {
                    textQuery = textQuery + queryParameterName + " = :" + queryParameterName;
                }
                else
                {
                    textQuery = textQuery + queryParameterName + " LIKE :" + queryParameterName;
                }
            }
            else if (!useLike)
            {
                string textLowQuery = textQuery;
                textQuery = textLowQuery + "LOWER(" + queryParameterName + ") = :" + queryParameterName;
            }
            else
            {
                string textLowLikeQuery = textQuery;
                textQuery = textLowLikeQuery + "LOWER(" + queryParameterName + ") LIKE :" + queryParameterName;
            }
            IDbCommand hasUserDbCommand = this.CreateCommand(textQuery, hasUserDbConnection);
            hasUserDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            hasUserDbCommand.Parameters.Add(this.CreateParameter(queryParameterName, DbType.String, expression));
            int intReader = 0;
            try
            {
                hasUserDbConnection.Open();
                using (IDataReader hasUserDbReader = hasUserDbCommand.ExecuteReader())
                {
                    if (hasUserDbReader.Read())
                    {
                        intReader = hasUserDbReader.GetInt32(0);
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "HasUsersRoles");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                hasUserDbConnection.Close();
            }
            return (intReader > 0);
        }

        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }
            if (string.IsNullOrEmpty(name))
            {
                name = this.defaultName;
            }
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", this.defaultDescription);
            }
            base.Initialize(name, config);
            this.pApplicationName = config["ApplicationName"];
            if (string.IsNullOrEmpty(this.pApplicationName))
            {
                this.pApplicationName = HostingEnvironment.ApplicationVirtualPath;
            }
            config.Remove("ApplicationName");
            this.pConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
            config.Remove("connectionStringName");
            if (this.pConnectionStringSettings == null)
            {
                this.connectionString = "";
            }
            else
            {
                this.connectionString = this.pConnectionStringSettings.ConnectionString.Trim();
            }
            if (config.Count > 0)
            {
                string configuration = config.GetKey(0);
                if (!string.IsNullOrEmpty(configuration))
                {
                    throw new ProviderException("Arbitrary unrecognizable attribute related to the SharePoint role provider:" + configuration);
                }
            }
            this.usersProvider = this.GetUsersProvider();
        }

        public override bool IsUserInRole(string username, string roleName)
        {
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            if (string.IsNullOrEmpty(username))
            {
                throw new ArgumentException("The SharePoint user name requires definition.");
            }
            DbConnection userRoleDbConnection = this.provider.CreateConnection();
            userRoleDbConnection.ConnectionString = this.connectionString;
            string queryText = "SELECT COUNT(*) FROM aspnet_users u, aspnet_roles r, aspnet_usersinroles ur WHERE LOWER(u.applicationname) = :applicationname AND LOWER(r.applicationname) = LOWER(u.applicationname) AND u.userid = ur.userid AND r.roleid = ur.roleid AND LOWER(username) = :username AND LOWER(rolename) = :rolename";
            IDbCommand userRoleDbcommand = this.CreateCommand(queryText, userRoleDbConnection);
            userRoleDbcommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            userRoleDbcommand.Parameters.Add(this.CreateParameter("username", DbType.String, username.ToLower()));
            userRoleDbcommand.Parameters.Add(this.CreateParameter("rolename", DbType.String, roleName.ToLower()));
            int intReader = 0;
            try
            {
                userRoleDbConnection.Open();
                using (IDataReader userRoleDbReader = userRoleDbcommand.ExecuteReader())
                {
                    if (userRoleDbReader.Read())
                    {
                        intReader = userRoleDbReader.GetInt32(0);
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "GetAllUsers");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                userRoleDbConnection.Close();
            }
            if (intReader > 0)
            {
                return true;
            }
            if (!this.UserExists(username))
            {
                throw new ProviderException("The provided SharePoint user name is invalid.");
            }
            if (!this.RoleExists(roleName))
            {
                throw new ProviderException("The provided SharePoint role name is invalid.");
            }
            return false;
        }

        /// <summary>
        /// Query the users for a specified role
        /// </summary>
        /// <param name="resultParameterName"></param>
        /// <param name="queryParameterName"></param>
        /// <param name="expression"></param>
        /// <param name="likeExpression"></param>
        /// <returns></returns>
        private string[] QueryUsersRoles(string resultParameterName, string queryParameterName, string expression, string likeExpression)
        {
            ArrayList stringReader = new ArrayList();
            DbConnection queryUseRoleConnection = this.provider.CreateConnection();
            queryUseRoleConnection.ConnectionString = this.connectionString;
            string queryText = "SELECT " + resultParameterName + " from aspnet_users u, aspnet_roles r, aspnet_usersinroles ur WHERE LOWER(u.applicationname) = :applicationname AND LOWER(r.applicationname) = LOWER(u.applicationname) AND u.userid = ur.userid AND r.roleid = ur.roleid AND LOWER(" + queryParameterName + ") = :" + queryParameterName;
            if (likeExpression != null)
            {
                string query = queryText;
                queryText = query + " AND LOWER(" + resultParameterName + ") LIKE :" + resultParameterName;
            }
            IDbCommand queryUserRoleCommand = this.CreateCommand(queryText, queryUseRoleConnection);
            queryUserRoleCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            queryUserRoleCommand.Parameters.Add(this.CreateParameter(queryParameterName, DbType.String, expression));
            if (likeExpression != null)
            {
                queryUserRoleCommand.Parameters.Add(this.CreateParameter(resultParameterName, DbType.String, likeExpression));
            }
            try
            {
                queryUseRoleConnection.Open();
                using (IDataReader queryUserRoleReader = queryUserRoleCommand.ExecuteReader())
                {
                    while (queryUserRoleReader.Read())
                    {
                        stringReader.Add(queryUserRoleReader.GetString(0));
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "GetAllUsers");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                queryUseRoleConnection.Close();
            }
            return (string[]) stringReader.ToArray(typeof(string));
        }

        /// <summary>
        /// Read the database field
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="i"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        protected virtual object ReadField(IDataReader reader, int i, DbType dbType)
        {
            return reader.GetValue(i);
        }

        /// <summary>
        /// Remove usernames from specified roles
        /// </summary>
        /// <param name="usernames"></param>
        /// <param name="roleNames"></param>
        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            DbConnection remUserFromRoleDbConnection = this.provider.CreateConnection();
            try
            {
                remUserFromRoleDbConnection.ConnectionString = this.connectionString;
                remUserFromRoleDbConnection.Open();
                DbTransaction remUserFromRoleTransaction = remUserFromRoleDbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    IDbCommand remUserFromRoleCommand = this.CreateCommand(this.SqlRemoveUsersFromRoles, remUserFromRoleDbConnection);
                    DbParameter applicationNamePrimary = this.CreateParameter("applicationname1", DbType.String, 0xff, this.ApplicationName.ToLower());
                    DbParameter roleName = this.CreateParameter("rolename", DbType.String, 0xff, "");
                    DbParameter applicationNameSecondary = this.CreateParameter("applicationname2", DbType.String, 0xff, this.ApplicationName.ToLower());
                    DbParameter userName = this.CreateParameter("username", DbType.String, 0xff, "");
                    remUserFromRoleCommand.Parameters.Add(applicationNamePrimary);
                    remUserFromRoleCommand.Parameters.Add(roleName);
                    remUserFromRoleCommand.Parameters.Add(applicationNameSecondary);
                    remUserFromRoleCommand.Parameters.Add(userName);
                    for (int iUser = 0; iUser < usernames.Length; iUser++)
                    {
                        string user = usernames[iUser];
                        if (string.IsNullOrEmpty(user))
                        {
                            throw new ArgumentException("The SharePoint user name requires definition.");
                        }
                        for (int iRole = 0; iRole < roleNames.Length; iRole++)
                        {
                            string role = roleNames[iRole];
                            if (string.IsNullOrEmpty(role))
                            {
                                throw new ArgumentException("The SharePoint role name requires definition.");
                            }
                            roleName.Value = role.ToLower();
                            userName.Value = user.ToLower();
                            remUserFromRoleCommand.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception exception)
                {
                    remUserFromRoleTransaction.Rollback();
                    if (this.WriteExceptionsToEventLog)
                    {
                        this.WriteToEventLog(exception, "RemoveUsersFromRoles");
                        throw new ProviderException(this.exceptionMessage, exception);
                    }
                    throw exception;
                }
                remUserFromRoleTransaction.Commit();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "RemoveUsersFromRoles");
                    throw new ProviderException(this.exceptionMessage, exception);
                }
                throw exception;
            }
            finally
            {
                remUserFromRoleDbConnection.Close();
            }
        }

        /// <summary>
        /// Determine whether the passed rolename exists
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public override bool RoleExists(string roleName)
        {
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("The SharePoint role name requires definition.");
            }
            DbConnection roleExistsDbConnection = this.provider.CreateConnection();
            roleExistsDbConnection.ConnectionString = this.connectionString;
            string queryText = "SELECT COUNT(*) FROM aspnet_roles WHERE LOWER(applicationname) = :applicationname AND LOWER(rolename) = :rolename";
            IDbCommand roleExistsDbCommand = this.CreateCommand(queryText, roleExistsDbConnection);
            roleExistsDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            roleExistsDbCommand.Parameters.Add(this.CreateParameter("rolename", DbType.String, 0xff, roleName.ToLower()));
            int intRead = 0;
            try
            {
                roleExistsDbConnection.Open();
                using (IDataReader roleExistsDbReader = roleExistsDbCommand.ExecuteReader())
                {
                    if (roleExistsDbReader.Read())
                    {
                        intRead = roleExistsDbReader.GetInt32(0);
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "RoleExists");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                roleExistsDbConnection.Close();
            }
            return (intRead > 0);
        }

        /// <summary>
        /// Determine whether the user exists based on the passed username
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        private bool UserExists(string userName)
        {
            if (string.IsNullOrEmpty(userName))
            {
                throw new ArgumentException("The SharePoint user name requires definition.");
            }
            DbConnection userExistsDbConnection = this.provider.CreateConnection();
            userExistsDbConnection.ConnectionString = this.connectionString;
            string queryText = "SELECT COUNT(*) FROM aspnet_users WHERE LOWER(applicationname) = :applicationname AND LOWER(username) = :username";
            IDbCommand userExistsDbCommand = this.CreateCommand(queryText, userExistsDbConnection);
            userExistsDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
            userExistsDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, userName.ToLower()));
            int intRead = 0;
            try
            {
                userExistsDbConnection.Open();
                using (IDataReader userExistsDbReader = userExistsDbCommand.ExecuteReader())
                {
                    if (userExistsDbReader.Read())
                    {
                        intRead = userExistsDbReader.GetInt32(0);
                    }
                }
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "UserExists");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                userExistsDbConnection.Close();
            }
            return (intRead > 0);
        }

        /// <summary>
        /// Write the exceptions as they occur to the machine event log
        /// </summary>
        /// <param name="e"></param>
        /// <param name="action"></param>
        private void WriteToEventLog(Exception e, string action)
        {
            EventLog providerLog = new EventLog();
            providerLog.Source = this.defaultName;
            providerLog.Log = this.eventLog;
            string exceptionText = "An exception occurred communicating with the SharePoint role store.\n\n";
            exceptionText = exceptionText + "Action: " + action + "\n\n";
            exceptionText = exceptionText + "Exception: " + e.ToString();
            providerLog.WriteEntry(exceptionText);
        }
    }
}