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