/// <summary> /// Takes, as input, a search pattern and a role name and returns a list of users belonging /// to the specified role whose user names match the pattern. Wildcard syntax is /// data-source-dependent and may vary from provider to provider. User names are returned /// in alphabetical order. /// </summary> /// <remarks> /// This is the main implementation for the <c>FindUsersInRole</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="roleName">The rolename to find users for.</param> /// <param name="usernameToMatch">The username wildcard to match.</param> /// <returns>Returns a list of users belonging to the specified role whose user names match the pattern.</returns> internal string[] FindUsersInRole(string roleName, string usernameToMatch) { // Validate the input parameters ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName"); ValidationUtil.CheckParameterIsOK(ref usernameToMatch, true, true, false, 256, "usernameToMatch"); // Check if the role exists and throw an exception if it doesn't if (!this.RoleExists(roleName)) { throw new ProviderException(string.Format(Messages.RoleNotFound, roleName)); } // Get the role id string roleId = this.GetRoleIdByName(roleName); // Adjust the username so that it is in the correct format for an Ingres "LIKE" usernameToMatch = String.Format("%{0}%", usernameToMatch); string sql = @" SELECT aspnet_Users.UserName FROM aspnet_Users, aspnet_UsersInRoles WHERE aspnet_Users.Username LIKE ? AND aspnet_Users.UserId = aspnet_UsersInRoles.UserId AND aspnet_UsersInRoles.RoleId = ? AND aspnet_Users.ApplicationId = ? ORDER BY aspnet_Users.UserName "; // Create a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the requires parameters cmd.Parameters.Add("Username", DbType.String).Value = usernameToMatch; cmd.Parameters.Add("RoleId", DbType.String).Value = roleId; cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower(); // Instantiate a new reader and execute the query IngresDataReader reader = cmd.ExecuteReader(); if (reader != null) { // Get the results out of the reader List<string> userNamesList = new List<string>(); while (reader.Read()) { userNamesList.Add(DBUtil.ColValAsString(reader, "UserName")); } reader.Close(); // return an appropriate string array if (userNamesList.Count > 0) { return userNamesList.ToArray(); } } return new string[0]; }
/// <summary> /// Returns the names of all existing roles. If no roles exist, <c>GetAllRoles</c> returns an /// empty string array (a string array with no elements). /// </summary> /// <remarks> /// This is the main implementation for the <c>GetAllRoles</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <returns>A string array with all of the roles for the application.</returns> internal string[] GetAllRoles() { // Ensure that we have an application Id if (this.ApplicationId == null) { throw new Exception(Messages.ApplicationIdNotFound); } // build the SQL command string sql = @" SELECT Rolename FROM aspnet_Roles WHERE ApplicationId = ? "; // Create a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the requires parameteters cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId; // Instantiate a reader and execute the query IngresDataReader reader = cmd.ExecuteReader(); if (reader != null) { // Get all roles from the reader List<string> roleNamesList = new List<string>(); while (reader.Read()) { roleNamesList.Add(DBUtil.ColValAsString(reader, "Rolename")); } reader.Close(); // If we have some role names then return them as a string array if (roleNamesList.Count > 0) { return roleNamesList.ToArray(); } } // Otherwise just return a new empty string array return new string[0]; }
/// <summary> /// A helper method to return the User Id for a given username. /// </summary> /// <param name="username">The username.</param> /// <returns>The Id of the username.</returns> private string GetUserIdByName(string username) { string id = null; string sql = @" SELECT UserId FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ? "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower(); IngresDataReader datareader = cmd.ExecuteReader(); if (datareader != null) { if (datareader.HasRows) { datareader.Read(); id = datareader.GetString(0); } datareader.Close(); } return id; }
/// <summary> /// Helper method to determine whether a user exists for a given /// </summary> /// <param name="username">The username that we want to check the existence of.</param> /// <returns>A boolean indicating whether the user exists.</returns> private bool UserExists(string username) { // Validate the input ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username"); // assume that the user doesn't exist bool exists = false; // build up the parameterised SQL string string sql = @" SELECT UserName FROM aspnet_Users WHERE LoweredUserName = ? AND ApplicationId = ? "; // Create a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId; // Instantiate a new reader and execute the query IngresDataReader datareader = cmd.ExecuteReader(); // If we have returned anything then the user exists if (datareader.HasRows) { exists = true; } // close the datareader and return datareader.Close(); return exists; }
/// <summary> /// Takes, as input, a user name and a password answer and retrieves the password for that /// user from the data source and returns the password as a string. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetPassword</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="username">The username.</param> /// <param name="answer">The password answer.</param> /// <returns>The password for the given username.</returns> internal string GetPassword(string username, string answer) { // Ensure that password retrieval is supported if (!this.config.EnablePasswordRetrieval) { throw new NotSupportedException(string.Format(Messages.PasswordRetrievalNotSupported)); } // If an answer has been specified then convert it to lower case if (answer != null) { answer = answer.ToLower(); } string sql = @" SELECT aspnet_Users.UserId, aspnet_Membership.IsLockedOut, aspnet_Membership.Password, aspnet_Membership.PasswordFormat, aspnet_Membership.PasswordSalt, aspnet_Membership.PasswordAnswer, aspnet_Membership.FailPwdAttemptCount, aspnet_Membership.FailPwdAnswerAttemptCount, aspnet_Membership.IsApproved, aspnet_Users.LastActivityDate, aspnet_Membership.LastLoginDate FROM aspnet_Applications, aspnet_Users, aspnet_Membership WHERE aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Users.LoweredUserName = ? "; // Create the Ingres command. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower(); // Execute the command and retrieve the required information string password; MembershipPasswordFormat usersPasswordFormat; string passwordSalt; string passwordAnswer; IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.HasRows) { reader.Read(); try { // Check that the user is not locked out. if (DBUtil.ColValAsString(reader, "IsLockedOut") == "1" ? true : false) { throw new MembershipPasswordException(Messages.TheUserIsLockedOut); } // Determine the password format. int tempPasswordFormat = DBUtil.ColValAsInt32(reader, "PasswordFormat"); switch (tempPasswordFormat) { case 0: usersPasswordFormat = MembershipPasswordFormat.Clear; break; case 1: usersPasswordFormat = MembershipPasswordFormat.Hashed; break; case 2: usersPasswordFormat = MembershipPasswordFormat.Encrypted; break; default: throw new ProviderException(Messages.PasswordIsStoredInAUnrecognisedFormat); } // Retrieved the password, salt and password answer. password = DBUtil.ColValAsString(reader, "Password"); passwordSalt = DBUtil.ColValAsString(reader, "PasswordSalt"); passwordAnswer = DBUtil.ColValAsString(reader, "PasswordAnswer"); } catch (Exception) { throw new ProviderException(Messages.ErrorInTheGetPasswordMethod); } } else { // no errors occurred but we didnt have any rows -> user does not exist throw new MembershipPasswordException(Messages.SuppliedUserNameWasNotFound); } reader.Close(); // If we require question and answer and the wrong answer was given... if (this.config.RequiresQuestionAndAnswer && !this.CheckPassword(answer, passwordAnswer, usersPasswordFormat, passwordSalt)) { // ...updatate the failure count and throw an exception. this.UpdateFailureCount(username, FailureReason.PasswordAnswer); // If we got here then commit the transaction and then handle the exception this.tran.Commit(); // Set the transaction to null so that we don't attempt to roll it back. this.tran.Dispose(); throw new MembershipPasswordException(Messages.IncorrectPasswordAnswer); } // If the password was hashed we can't return it! if (usersPasswordFormat == MembershipPasswordFormat.Hashed) { throw new ProviderException(Messages.CannotUnencodeAHashedPassword); } // If the password is encrypted then we need do decrypt it before returning it. if (usersPasswordFormat == MembershipPasswordFormat.Encrypted) { password = this.DecodePassword(password, usersPasswordFormat); } // Otherwise the password is just a "clear" password and so we don't need to do anything to it. return password; }
/// <summary> /// Gets the Id for the current application. /// </summary> /// <param name="conn">The Ingres connection to use.</param> /// <param name="tran">The Ingres transaction to use.</param> /// <returns>The Id for the current application.</returns> private string GetApplicationId(IngresConnection conn, IngresTransaction tran) { string id = null; string sql = @" SELECT ApplicationId FROM aspnet_Applications WHERE LoweredApplicationName = ? "; // Create the new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); conn.Open(); IngresDataReader reader = cmd.ExecuteReader(); if (reader != null) { if (reader.HasRows) { reader.Read(); // Retrieve the Id id = DBUtil.ColValAsString(reader, "ApplicationId"); reader.Close(); } else { // Close the reader. reader.Close(); // We don't have an application so create one. this.CreateApplication(this.config.ApplicationName, out id); } } // Mark the application Id as current so that we don't have to fetch it from the database // again unless it changes. this.config.IsApplicationIdCurrent = true; // Close the connection conn.Close(); return id; }
/// <summary> /// Retrieves the user Id for a given username. /// </summary> /// <param name="username">The username.</param> /// <param name="userId">[out] The user Id.</param> /// <returns>Whether the username/id exist on the database.</returns> private bool GetUserIdByName(string username, out Guid userId) { string userIdTemp = null; // Build up the required SQL string sql = @" SELECT aspnet_Users.UserId FROM aspnet_Users, aspnet_Applications, aspnet_Membership WHERE LoweredUserName = ? AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.UserId = aspnet_Membership.UserId "; // Create a new Ingres command. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the query IngresDataReader reader = cmd.ExecuteReader(); // Retrieve the Id if (reader.HasRows) { reader.Read(); userIdTemp = DBUtil.ColValAsString(reader, "UserId"); } // If we didnt get an Id then the user doesn't exist. if (userIdTemp == null) { userId = Guid.Empty; return false; } if (!reader.IsClosed) { reader.Close(); } // Parse the user Id as a Guid userId = (Guid)SqlGuid.Parse(userIdTemp); return true; }
/// <summary> /// Takes, as input, a role name and determines whether the role exists. /// </summary> /// <remarks> /// This is the main implementation for the <c>RoleExists</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="roleName">role name to check the existence of</param> /// <returns>Whether the given role exists.</returns> internal bool RoleExists(string roleName) { // Validate the Parameter ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName"); // Assume that the role does not exist bool exists = false; string sql = @" SELECT RoleName FROM aspnet_Roles WHERE LoweredRoleName = ? AND ApplicationId = ? "; // Instantiate a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower(); cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower(); // Execute the query. IngresDataReader reader = cmd.ExecuteReader(); // Determine whether the role exists. if (reader.HasRows) { exists = true; } reader.Close(); return exists; }
/// <summary> /// Takes, as input, a <c>MembershipUser</c> object populated with user information and updates /// the data source with the supplied values. /// </summary> /// <remarks> /// This is the main implementation for the <c>Update</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="user">The membership user to update.</param> internal void UpdateUser(MembershipUser user) { if (user == null) { throw new ArgumentNullException("user"); } // Instatiate a string to hold the required sql. string applicationId; string userId; string sql = @" SELECT aspnet_Users.UserId, aspnet_Applications.ApplicationId FROM aspnet_Users, aspnet_Applications, aspnet_Membership WHERE LoweredUserName = ? AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.UserId = aspnet_Membership.UserId "; // Instantiate a new Ingres command using the new connection and sql and add required parameters. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredUserName", DbType.String).Value = user.UserName.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the reader and retrieve the user and application ids IngresDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); userId = DBUtil.ColValAsString(reader, "UserId"); applicationId = DBUtil.ColValAsString(reader, "ApplicationId"); reader.Close(); } else { throw new ProviderException("Could not find user."); } if (userId == null) { throw new ProviderException("Could not find user."); } // If we require a unique email then check that the email is unique. if (this.config.RequiresUniqueEmail) { sql = @"SELECT COUNT(*) FROM aspnet_Membership WHERE ApplicationId = ? AND UserId <> ? AND LoweredEmail = ?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add parameters cmd.Parameters.Add("ApplicationId", DbType.String).Value = applicationId; cmd.Parameters.Add("UserId", DbType.String).Value = user.ProviderUserKey; cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower(); // Ensure that we don't get anything returned - throw an exception if we do. int records = (int)cmd.ExecuteScalar(); if (records != 0) { throw new ProviderException(Messages.UniqueEmailRequired); } } // Update the last activity date sql = @"UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add required parameters. cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = user.LastActivityDate; cmd.Parameters.Add("UserId", DbType.String).Value = userId; // Execute the query. int rows = cmd.ExecuteNonQuery(); if (rows != 1) { throw new ProviderException(Messages.FailedToUpdateTheLastActivityDate); } // Update the memebership details sql = @"UPDATE aspnet_Membership SET Email = ?, LoweredEmail = ?, Comment = ?, IsApproved = ?, LastLoginDate = ? WHERE UserId =?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the parameters cmd.Parameters.Add("Email", DbType.String).Value = user.Email; cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower(); cmd.Parameters.Add("Comment", DbType.String).Value = user.Comment; cmd.Parameters.Add("IsApproved", DbType.String).Value = user.IsApproved; cmd.Parameters.Add("LastLoginDate", DbType.String).Value = user.LastLoginDate; cmd.Parameters.Add("UserId", DbType.String).Value = userId; rows = cmd.ExecuteNonQuery(); if (rows != 1) { throw new ProviderException(Messages.FailedToUpdateMembershipDetails); } }
/// <summary> /// Checks whether a supplied password for a user is valid or not. /// </summary> /// <param name="username">The username to check.</param> /// <param name="password">The supplied password to check.</param> /// <param name="updateLastLoginActivityDate">Whether to update the last login activity date.</param> /// <param name="failIfNotApproved">Whether the operation should fail if the user is not approved.</param> /// <param name="salt">The salt to use.</param> /// <param name="passwordFormat">The password format to use.</param> /// <returns>Whether the supplied password matches the password on the database.</returns> private bool CheckPassword(string username, string password, bool updateLastLoginActivityDate, bool failIfNotApproved, out string salt, out MembershipPasswordFormat passwordFormat) { // Default the 'out' parameters. salt = null; passwordFormat = MembershipPasswordFormat.Clear; // Assume that the user is not valid bool valid = false; // Build the required SQL string. string sql = @" SELECT Password, PasswordFormat, PasswordSalt, IsApproved FROM aspnet_Membership, aspnet_Users, aspnet_Applications WHERE aspnet_Users.LoweredUserName = ? AND aspnet_Applications.LoweredApplicationName = ? AND IsLockedOut = 0 AND aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId "; // Create a command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("LoweredUserNameUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); bool approved; // Execute the command and determine the password and whether the user is locked out IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); string databasePassword; if (reader.HasRows) { reader.Read(); databasePassword = DBUtil.ColValAsString(reader, "Password"); int tempPasswordFormat = DBUtil.ColValAsInt32(reader, "PasswordFormat"); switch (tempPasswordFormat) { case 0: passwordFormat = MembershipPasswordFormat.Clear; break; case 1: passwordFormat = MembershipPasswordFormat.Hashed; break; case 2: passwordFormat = MembershipPasswordFormat.Encrypted; break; default: throw new ProviderException(Messages.PasswordIsStoredInAUnrecognisedFormat); } salt = DBUtil.ColValAsString(reader, "PasswordSalt"); approved = (DBUtil.ColValAsString(reader, "IsApproved") == "1"); } else { return false; } reader.Close(); if (this.CheckPassword(password, databasePassword, passwordFormat, salt)) { if (approved) { valid = true; sql = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET LastLoginDate = ?, FailPwdAttemptCount = 0 WHERE aspnet_Users.LoweredUserName = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId "; // Create a new command and enrol in the current transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters, cmd.Parameters.Add("LastLoginDate", DbType.DateTime).Value = DateTime.Now; cmd.Parameters.Add("Username", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the query and ensure that only one row was updated int rows = cmd.ExecuteNonQuery(); if (rows != 1) { throw new Exception(Messages.ErrorTryingToUpdateTheLastLoginDate); } } } else { this.UpdateFailureCount(username, FailureReason.Password); } return valid; }
/// <summary> /// Takes, as input, a user name and a password answer and generates a new, random password /// for the specified user. The <c>ResetPassword</c> method updates the user information in the /// data source with the new password value and returns the new password as a string. A /// convenient mechanism for generating a random password is the <c>GeneratePassword</c> method of /// the <c>Membership</c> class. /// </summary> /// <remarks> /// This is the main implementation for the <c>ResetPassword</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="username">The username to reset the password for.</param> /// <param name="answer">The answer to the users password question.</param> /// <returns>Whether the membership user was successfully updated.</returns> internal string ResetPassword(string username, string answer) { MembershipPasswordFormat passwordFormat; string salt; // Ensure that password reset is enabled. if (!this.config.EnablePasswordReset) { throw new NotSupportedException(Messages.PasswordResetIsNotEnabled); } // Ensure that an answer has been supplied if appropriate. if (answer == null && this.config.RequiresQuestionAndAnswer) { this.UpdateFailureCount(username, FailureReason.PasswordAnswer); // If we got here then commit the transaction and then handle the exception this.tran.Commit(); // Set the transaction to null so that we don't attempt to roll it back. this.tran.Dispose(); throw new ProviderException(Messages.PasswordAnswerRequiredForPasswordReset); } // Generate a new password string newPassword = Membership.GeneratePassword(this.config.NewPasswordLength, this.config.MinRequiredNonAlphanumericCharacters); // Raise the 'on validating password' event. ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true); this.provider.OnValidatingPassword(args); if (args.Cancel) { if (args.FailureInformation != null) { throw args.FailureInformation; } throw new MembershipPasswordException(Messages.ResetPasswordCanceledDueToPasswordValidationFailure); } // Build up the required SQL. string sql = @" SELECT PasswordAnswer, PasswordFormat, PasswordSalt, IsLockedOut FROM aspnet_Membership, aspnet_Users, aspnet_Applications WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; // Create a new Ingres command IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the SQL as a reader. IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); string passwordAnswer; if (reader.HasRows) { reader.Read(); // Ensure that the user is not locked out. if (DBUtil.ColValAsString(reader, "IsLockedOut") == "1") { throw new MembershipPasswordException(Messages.TheSuppliedUserIsLockedOut); } // Get the password answer and ensure that it is lowercased passwordAnswer = DBUtil.ColValAsString(reader, "PasswordAnswer"); // Determine the password format. int tempPasswordFormat = DBUtil.ColValAsInt32(reader, "PasswordFormat"); switch (tempPasswordFormat) { case 0: passwordFormat = MembershipPasswordFormat.Clear; break; case 1: passwordFormat = MembershipPasswordFormat.Hashed; break; case 2: passwordFormat = MembershipPasswordFormat.Encrypted; break; default: throw new ProviderException(Messages.PasswordIsStoredInAUnrecognisedFormat); } // Get the salt. salt = DBUtil.ColValAsString(reader, "PasswordSalt"); } else { throw new MembershipPasswordException(Messages.TheSuppliedUserNameIsNotFound); } if (answer == null) { answer = string.Empty; } reader.Close(); // Check that the correct password answer was supplied... if (this.config.RequiresQuestionAndAnswer && !this.CheckPassword(answer.ToLower(CultureInfo.InvariantCulture), passwordAnswer, passwordFormat, salt)) { // ... if not, update the failure count and throw an exception this.UpdateFailureCount(username, FailureReason.PasswordAnswer); // If we got here then commit the transaction and then handle the exception this.tran.Commit(); // Set the transaction to null so that we don't attempt to roll it back. this.tran.Dispose(); throw new MembershipPasswordException(Messages.IncorrectPasswordAnswer); } reader.Close(); // Build up the SQL to reset the password sql = @" UPDATE aspnet_Membership FROM aspnet_Users, aspnet_Applications SET Password = ?, LastPasswordChangedDate = ? WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND IsLockedOut = 0 AND aspnet_Membership.UserId = aspnet_Users.UserId AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId "; // Create a new Ingres command IngresCommand updateCmd = new IngresCommand(sql, this.conn); updateCmd.Transaction = this.tran; updateCmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. updateCmd.Parameters.Add("Password", DbType.String).Value = this.EncodePassword(newPassword, passwordFormat, PasswordUtil.GetSalt(16)); updateCmd.Parameters.Add("LastPasswordChangedDate", DbType.DateTime).Value = DateTime.Now; updateCmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); updateCmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the query and ensure that a row was updated. int rowsAffected = updateCmd.ExecuteNonQuery(); if (rowsAffected > 0) { return newPassword; } throw new MembershipPasswordException(Messages.PasswordNotReset); }
/// <summary> /// Takes, as input, a unique user identifier and a Boolean value indicating whether to /// update the <c>LastActivityDate</c> value for the user to show that the user is currently /// online. The <c>GetUser</c> method returns a <c>MembershipUser</c> object populated with current /// values from the data source for the specified user. If the user name is not found in /// the data source, the <c>GetUser</c> method returns null. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetUser</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="providerUserKey">The unique indentifer for the user.</param> /// <param name="userIsOnline">Whether the user is online.</param> /// <returns>The membership user with the specified provider user key.</returns> internal MembershipUser GetUser(object providerUserKey, bool userIsOnline) { // Validate the input. The providerUserKey should be a Guid if (providerUserKey == null) { throw new ArgumentNullException("providerUserKey"); } if (!(providerUserKey is Guid)) { throw new ArgumentException(string.Format(Messages.InvalidProviderUserKey), "providerUserKey"); } string sql = @" SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Users, aspnet_Membership WHERE aspnet_Users.UserId = ? AND aspnet_Users.UserId = aspnet_Membership.UserId "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString().ToLower(); MembershipUser user = null; IngresDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); user = this.GetUserFromReader(reader); if (userIsOnline) { sql = @" UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ? "; cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = DateTime.Now; cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString().ToLower(); cmd.ExecuteNonQuery(); } } return user; }
/// <summary> /// Takes, as input, a user name and a Boolean value indicating whether to update the /// <c>LastActivityDate</c> value for the user to show that the user is currently online. The /// <c>GetUser</c> method returns a <c>MembershipUser</c> object populated with current values from the /// data source for the specified user. If the user name is not found in the data source, /// the <c>GetUser</c> method returns <c>null</c>. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetUser</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="username">The username.</param> /// <param name="userIsOnline">Whether the user us currently online.</param> /// <returns>The membership user with the specified username.</returns> internal MembershipUser GetUser(string username, bool userIsOnline) { // Ensure that a username has been provided if (string.IsNullOrEmpty(username)) { throw new ProviderException(Messages.UsernameMustBeSuppliedToGetAUser); } string sql = @" SELECT TOP 1 aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Applications, aspnet_Users, aspnet_Membership WHERE aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Users.LoweredUserName = ? AND aspnet_Users.UserId = aspnet_Membership.UserId "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower(); // Innocuously initialise the user MembershipUser user = null; IngresDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); user = this.GetUserFromReader(reader); reader.Close(); if (userIsOnline) { sql = @" UPDATE aspnet_Users FROM aspnet_Applications, aspnet_Membership SET LastActivityDate = ? WHERE aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? "; IngresCommand updateCmd = new IngresCommand(sql, this.conn); updateCmd.Transaction = this.tran; updateCmd.CommandTimeout = this.config.CommandTimeout; updateCmd.Parameters.Add("ActivityDate", DbType.DateTime).Value = DateTime.Now; updateCmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); updateCmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); int rows = updateCmd.ExecuteNonQuery(); if (rows != 1) { throw new Exception(Messages.FailedToUpdateLastActivityDate); } } } if (!reader.IsClosed) { reader.Close(); } return user; }
/// <summary> /// Takes, as input, a user name and returns the names of the roles to which the user /// belongs. If the user is not assigned to any roles, <c>GetRolesForUser</c> returns an empty /// string array (a string array with no elements). If the user name does not exist, /// <c>GetRolesForUser</c> throws a <c>ProviderException</c>. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetRolesForUser</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="username">The username we want to get the roles for.</param> /// <returns>Any array of all of the roles for the given user.</returns> internal string[] GetRolesForUser(string username) { // Validate the username ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username"); // Validate that the user exists and throw exception if he doesn't if (!this.UserExists(username)) { throw new ProviderException(string.Format(Messages.UserNotFound)); } // Build up the required SQL string sql = @" SELECT aspnet_Roles.RoleName FROM aspnet_Roles, aspnet_UsersInRoles WHERE aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId AND aspnet_Roles.ApplicationId = ? AND aspnet_UsersInRoles.UserId = ? ORDER BY aspnet_Roles.RoleName "; // Create a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add required parameters cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId; cmd.Parameters.Add("UserId", DbType.String).Value = this.GetUserIdByName(username); // Instantiate a new reader and read in the query IngresDataReader reader = cmd.ExecuteReader(); // Get the roles out of the reader List<string> roleNamesList = new List<string>(); while (reader.Read()) { roleNamesList.Add(DBUtil.ColValAsString(reader, "RoleName")); } reader.Close(); // Return an appropriate string array if (roleNamesList.Count > 0) { return roleNamesList.ToArray(); } // we had no roles and so just return an empty string array. return new string[0]; }
/// <summary> /// A helper method that performs the checks and updates associated with password failure /// tracking. /// </summary> /// <param name="username">The username to update the failure count for.</param> /// <param name="reason">The reason why we wish to update the failure count.</param> private void UpdateFailureCount(string username, FailureReason reason) { // The required SQL string sql = @" SELECT FailPwdAttemptCount, FailPwdAttemptWindowStart, FailPwdAnswerAttemptCount, FailPwdAnswerAttemptWindowStart FROM aspnet_Membership, aspnet_Users, aspnet_Applications WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Membership.ApplicationId = aspnet_Applications.ApplicationId "; // Instantiate an Ingres command (using the current connection and transaction) with the SQL IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); DateTime windowStart = new DateTime(); int failureCount = 0; // Execute the command as a reader IngresDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.HasRows) { reader.Read(); if (reason == FailureReason.Password) { failureCount = DBUtil.ColValAsInt32(reader, "FailPwdAttemptCount"); try { windowStart = DBUtil.ColValAsDateTime(reader, "FailPwdAttemptWindowStart"); } catch { windowStart = DateTime.Now; } } if (reason == FailureReason.PasswordAnswer) { failureCount = DBUtil.ColValAsInt32(reader, "FailPwdAnswerAttemptCount"); windowStart = DBUtil.ColValAsDateTime(reader, "FailPwdAnswerAttemptWindowStart"); } } // Close the reader and build up a new command reader.Close(); cmd = new IngresCommand(); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; DateTime windowEnd = windowStart.AddMinutes(this.config.PasswordAttemptWindow); if (failureCount == 0 || DateTime.Now > windowEnd) { // Start a new failure count and window if (reason == FailureReason.Password) { // For password failed attempts: cmd.CommandText = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET FailPwdAttemptCount = ?, FailPwdAttemptWindowStart = ? WHERE LoweredUsername = ? AND LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; } if (reason == FailureReason.PasswordAnswer) { // For password answer failed attempts: cmd.CommandText = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET FailPwdAnswerAttemptCount = ?, FailPwdAnswerAttemptWindowStart = ? WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; } // Ad the required parameters cmd.Parameters.Add("Count", DbType.Int32).Value = 1; cmd.Parameters.Add("WindowStart", DbType.DateTime).Value = DateTime.Now; cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the query and ensure that one row was updated if (cmd.ExecuteNonQuery() != 1) { throw new ProviderException(Messages.UnableToUpdateFailureCountAndWindowStart); } } else { // Not the first failure or still inside the window // If the failure accout is now greater than or equal to the max attempts allowed // then lock the user out if (failureCount++ >= this.config.MaxInvalidPasswordAttempts) { // Password attempts have exceeded the failure threshold. Lock out // the user. cmd.CommandText = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET IsLockedOut = ?, LastLockoutDate = ? WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; cmd.Parameters.Clear(); cmd.Parameters.Add("IsLockedOut", DbType.String).Value = '1'; cmd.Parameters.Add("LastLockoutDate", DbType.DateTime).Value = DateTime.Now; cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); if (cmd.ExecuteNonQuery() < 0) { throw new ProviderException("Unable to lock out user."); } } else { // Otherwise just save the incremented failure count. if (reason == FailureReason.Password) { cmd.CommandText = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET FailPwdAttemptCount = ? WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; } if (reason == FailureReason.PasswordAnswer) { cmd.CommandText = @" UPDATE aspnet_Membership FROM aspnet_Applications, aspnet_Users SET FailPwdAnswerAttemptCount = ? WHERE aspnet_Users.LoweredUsername = ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Membership.UserId = aspnet_Users.UserId "; } cmd.Parameters.Clear(); cmd.Parameters.Add("Count", DbType.Int32).Value = failureCount; cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); if (cmd.ExecuteNonQuery() != 1) { throw new ProviderException(Messages.UnableToUpdateFailureCount); } } } }
/// <summary> /// Takes, as input, a role name and returns the names of all users assigned to that role. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetUsersInRole</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="roleName">The rolename that we wish to get all the users for.</param> /// <returns>An array of all of the users for the given role.</returns> internal string[] GetUsersInRole(string roleName) { // If we don't have an application id then we aren't going to have any roles. if (this.ApplicationId == null) { return new string[0]; } // Validate the roleName ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName"); // Check if the role exists and throw an exception if it doesn't if (!this.RoleExists(roleName)) { throw new ProviderException(string.Format(Messages.RoleNotFound, roleName)); } string sql = @" SELECT aspnet_Users.UserName FROM aspnet_Users, aspnet_UsersInRoles, aspnet_Roles WHERE aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId AND aspnet_UsersInRoles.UserId = aspnet_Users.UserId AND aspnet_Roles.ApplicationId = aspnet_Users.ApplicationId AND aspnet_Users.ApplicationId = ? AND aspnet_Roles.LoweredRoleName = ? ORDER BY aspnet_Users.UserName "; // Create a new command and enrol in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower(); cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower(); // Instantiate a new reader and execute the query IngresDataReader reader = cmd.ExecuteReader(); if (reader != null) { // Get the users from the reader List<string> userList = new List<string>(); while (reader.Read()) { userList.Add(DBUtil.ColValAsString(reader, "UserName")); } // Close the reader reader.Close(); // retun an appropriate string array dependent on whether we managed to return any users or not if (userList.Count > 0) { return userList.ToArray(); } } // no users so return an empty array return new string[0]; }
/// <summary> /// Returns a list of membership users where the user name contains a match of the supplied /// <c>usernameToMatch</c> for the this.configured <c>ApplicationName</c>. For example, if the <c>usernameToMatch</c> /// parameter is set to "user," then the users "user1," "user2," "user3," and so on are /// returned. Wildcard support is included based on the data source. Users are returned in /// alphabetical order by user name. /// </summary> /// <remarks> /// This is the main implementation for the <c>FindUsersByName</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="usernameToMatch">The username to match.</param> /// <param name="pageIndex">The page to return.</param> /// <param name="pageSize">The number of users to return.</param> /// <param name="totalRecords">[out] the total number of matches.</param> /// <returns>Returns a list of membership users where the user name contains a match of the /// supplied usernameToMatch for the this.configured ApplicationName.</returns> internal MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords) { // Validate the input ValidationUtil.CheckParameterIsOK(ref usernameToMatch, true, true, false, 256, "usernameToMatch"); if (pageIndex < 0) { throw new ArgumentException(string.Format(Messages.PageIndexInvalid), "pageIndex"); } if (pageSize < 1) { throw new ArgumentException(string.Format(Messages.PageSizeInvalid), "pageSize"); } long upperBound = ((long)pageIndex * pageSize) + (pageSize - 1); if (upperBound > Int32.MaxValue) { throw new ArgumentException(string.Format(Messages.PageIndexAndPageSizeCombinationInvalid), "pageIndex and pageSize"); } // Adjust the username so that it is in the correct format for an Ingres "LIKE" usernameToMatch = String.Format("%{0}%", usernameToMatch); string sql = @" SELECT COUNT(*) FROM aspnet_Membership, aspnet_Applications, aspnet_Users WHERE aspnet_Users.LoweredUsername LIKE ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredUsername", DbType.String).Value = usernameToMatch.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); MembershipUserCollection users = new MembershipUserCollection(); totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); if (totalRecords <= 0) { return users; } // Create a new Ingres command cmd = new IngresCommand(); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters cmd.Parameters.Add("LoweredUsername", DbType.String).Value = usernameToMatch.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Build up the SQL cmd.CommandText = @" SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Users, aspnet_Membership, aspnet_Applications WHERE aspnet_Users.LoweredUsername LIKE ? AND aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId ORDER BY Username ASC "; // Execute the query and return the correct subset as stipulated by the paging options. IngresDataReader reader = cmd.ExecuteReader(); int counter = 0; int startIndex = (pageSize * pageIndex) + 1; int endIndex = pageSize * (pageIndex + 1); while (reader.Read()) { counter++; if ((counter >= startIndex) && (counter <= endIndex)) { MembershipUser user = this.GetUserFromReader(reader); users.Add(user); } if (counter >= endIndex) { cmd.Cancel(); } } return users; }
/// <summary> /// Creates an application in the database. /// </summary> /// <param name="name">The name of the application to create.</param> /// <param name="id">The Id of the application to create.</param> private void CreateApplication(string name, out string id) { // Ensure that the proposed rolename is of a valid form and does not already exist if (name.IndexOf(',') > 0) { throw new ArgumentException(Messages.ApplicationNamesCannotContainCommas); } // Build up the command and connection details id = null; string sql = @" SELECT ApplicationId FROM aspnet_Applications WHERE LoweredApplicationName = ? "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower(); IngresDataReader datareader = cmd.ExecuteReader(); if (datareader != null) { if (datareader.HasRows) { datareader.Read(); id = datareader.GetString(0); datareader.Close(); return; } id = Guid.NewGuid().ToString(); } if (datareader != null) { datareader.Close(); } sql = @" INSERT INTO aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName) VALUES (?, ?, ?) "; cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("ApplicationId", DbType.String).Value = id; cmd.Parameters.Add("ApplicationName", DbType.String).Value = name; cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = name.ToLower(); cmd.ExecuteNonQuery(); return; }
/// <summary> /// Returns a <c>MembershipUserCollection</c> populated with <c>MembershipUser</c> objects for all of the /// users in the data source. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetAllUsers</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="pageIndex">Which page to return.</param> /// <param name="pageSize">The maximum number of users to return.</param> /// <param name="totalRecords">[out] The total number of users.</param> /// <returns>Returns a MembershipUserCollection populated with MembershipUser objects /// for all of the users in the data source.</returns> internal MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { // Validate the input. if (pageIndex < 0) { throw new ArgumentException(string.Format(Messages.PageIndexInvalid), "pageIndex"); } if (pageSize < 1) { throw new ArgumentException(string.Format(Messages.PageSizeInvalid), "pageSize"); } long upperBound = ((long)pageIndex * pageSize) + (pageSize - 1); if (upperBound > Int32.MaxValue) { throw new ArgumentException(string.Format(Messages.PageIndexAndPageSizeCombinationInvalid), "pageIndex and pageSize"); } // Put the SQL in a string. string sql = @" SELECT Count(*) FROM aspnet_Membership, aspnet_Applications WHERE aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId"; // Create a new command and enrol it in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the requires parameters cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); MembershipUserCollection users = new MembershipUserCollection(); // The result is the number of users that we have totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); // If we don't have any users then just return the empty MembershipUserCollection if (totalRecords <= 0) { return users; } // Create a new Ingres command cmd = new IngresCommand(); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Build up the SQL to retrieve the users for the application. cmd.CommandText = @" SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Users, aspnet_Membership, aspnet_Applications WHERE aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId ORDER BY Username ASC "; // Execute the command as a reader and return the correct users as stipulated by the paging options IngresDataReader reader = cmd.ExecuteReader(); int counter = 0; int startIndex = (pageSize * pageIndex) + 1; int endIndex = pageSize * (pageIndex + 1); // Note: This might have to be done differently if the performance is too poor! while (reader.Read()) { counter++; if ((counter >= startIndex) && (counter <= endIndex)) { MembershipUser user = this.GetUserFromReader(reader); users.Add(user); } if (counter >= endIndex) { cmd.Cancel(); } } return users; }