/// <summary>
/// A helper function to determine if a user exists with a given id exists.
/// </summary>
/// <param name="userId">The Id to check.</param>
/// <returns>Whether a user exists with the given Id.</returns>
private bool DoesUserIdExist(Guid? userId)
{
string sql = @"
SELECT
UserId
FROM
aspnet_Users
WHERE
UserId = ?
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
int rows = cmd.ExecuteNonQuery();
return rows > 0;
}
/// <summary>
/// Returns a clone of the Command object
/// </summary>
/// <returns></returns>
public IngresCommand Clone()
{
IngresCommand newCommand = new IngresCommand();
newCommand.CommandText = this.CommandText;
newCommand.CommandTimeout = this.CommandTimeout;
newCommand.CommandType = this.CommandType;
newCommand.Connection = this.Connection;
newCommand.DesignTimeVisible= this.DesignTimeVisible;
newCommand.Transaction = this.Transaction;
newCommand.UpdatedRowSource = this.UpdatedRowSource;
if (this.Parameters != null)
foreach(IngresParameter oldParm in this.Parameters)
{
newCommand.Parameters.Add((IngresParameter)oldParm.Clone());
}
return newCommand;
}
// Name: CheckVersion - Test the stored schema version
//
// Description:
// Compares the specified version number with the stored version
// If the verson matches or exceeds the required version the
// function returns true.
//
// Inputs:
// Major Major version number.
// Minor Minor version number.
// Release Release version number.
//
// Outputs:
// None.
//
// Returns:
// true Version matches or exceeds the required version.
// false Incorrect version level.
//
// History
// 02-Oct-2006 ([email protected])
// Created.
public bool CheckVersion(int Major, int Minor, int Release)
{
bool retcode = false;
IngresCommand ingresVersionSelect = new IngresCommand();
IngresDataAdapter ingresVersionAdapter;
// Create query parameters and store the method parameter values
// into each one.
IngresParameter majorVersion = new IngresParameter("ver_major", IngresType.Int);
IngresParameter minorVersion = new IngresParameter("ver_minor", IngresType.Int); ;
IngresParameter releaseVersion = new IngresParameter("ver_release", IngresType.Int);
majorVersion.Value = Major;
minorVersion.Value = Minor;
releaseVersion.Value = Release;
ingresVersionSelect = new IngresCommand(
"SELECT FIRST 1 ver_major, ver_minor, ver_release, ver_date, ver_install" +
" FROM version" +
" WHERE ver_major >= ? AND ver_minor >= ? AND ver_release >=?" +
" ORDER BY ver_id DESC",
ingresConnection1);
ingresVersionSelect.Connection = ingresConnection1;
ingresVersionAdapter = new IngresDataAdapter(ingresVersionSelect);
try
{
ingresVersionSelect.Connection.Open();
ingresVersionSelect.Prepare();
ingresVersionSelect.Parameters.Clear();
ingresVersionSelect.Parameters.Add(majorVersion);
ingresVersionSelect.Parameters.Add(minorVersion);
ingresVersionSelect.Parameters.Add(releaseVersion);
versionDataSet.Clear();
ingresVersionAdapter.Fill(versionDataSet);
if (versionDataSet.Tables[0].Rows.Count > 0)
{
retcode = true;
}
}
catch (Ingres.Client.IngresException ex)
{
if (DisplayError(ex) == DialogResult.OK)
{
throw;
}
else
{
Application.Exit();
}
}
finally
{
if (ingresVersionSelect != null &&
ingresVersionSelect.Connection != null)
ingresVersionSelect.Connection.Close();
}
return (retcode);
}
/// <summary>
/// Helper method to create a user.
/// </summary>
/// <param name="username">The username that we wish to create.</param>
/// <returns>The Id for the newly created user.</returns>
private string CreateUser(string username)
{
// Validate the username
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string id = Guid.NewGuid().ToString().ToLower();
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
UserId,
UserName,
LoweredUserName,
MobileAlias,
IsAnonymous,
LastActivityDate)
VALUES
(?,
?,
?,
?,
NULL,
'0',
?)
";
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId.ToLower();
cmd.Parameters.Add("UserId", DbType.String).Value = id;
cmd.Parameters.Add("UserName", DbType.String).Value = username;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LastActivity", DbType.Date).Value = DateTime.Now;
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
// the user has been successfully created and so we return the id.
return id;
}
/*
** Name: CreateCommand
**
** History:
** 27-Aug-02 (thoda04)
** Created.
*/
/// <summary>
/// Creates an IngresCommand object associated with the IngresConnection.
/// </summary>
/// <returns>An IngresCommand object.</returns>
public new IngresCommand CreateCommand()
{
// Return a new instance of a command object.
IngresCommand cmd = new IngresCommand(
String.Empty, this, this.Transaction);
return cmd;
}
/// <summary>
/// Takes, as input, a list of user names and a list of role names and removes the
/// specified users from the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>RemoveUsersFromRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">The array of usernames.</param>
/// <param name="roleNames">The array of roles.</param>
internal void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
// Validate the input parameters
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Instantiate lists to hold the calculated user and role Ids.
List<string> userIdsList = new List<string>();
List<string> roleIdsList = new List<string>();
// Ensure that all of the roleNames are valid
foreach (string username in usernames)
{
string userId = this.GetUserIdByName(username);
if (userId == null)
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
userIdsList.Add(userId);
}
// Ensure that all of the roleNames are valid
foreach (string rolename in roleNames)
{
string roleId = this.GetRoleIdByName(rolename);
if (roleId == null)
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
roleIdsList.Add(roleId);
}
// Ensure that the users are actually the the roles to begin with!
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (!this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyNotInRole, username, rolename));
}
}
}
// Build up the SQL string
string sql = @"
DELETE FROM aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// 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
IngresParameter userParm = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParm = cmd.Parameters.Add("RoleId", DbType.String);
// For each user
foreach (string userId in userIdsList)
{
// For each role
foreach (string roleId in roleIdsList)
{
userParm.Value = userId;
roleParm.Value = roleId;
// Remove the user from the role
cmd.ExecuteNonQuery();
}
}
}
/// <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 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>
/// 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>
/// Takes, as input, the name of a new user, a password, and an email address and inserts
/// a new user for the application into the data source. The <c>CreateUser</c> method returns a
/// <c>MembershipUser</c> object populated with the information for the newly created user.
/// The <c>CreateUser</c> method also defines an out parameter that returns a
/// <c>MembershipCreateStatus</c> value that indicates whether the user was successfully created,
/// or a reason that the user was not successfully created.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>CreateUser</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="username">name of the new user.</param>
/// <param name="password">password for the new user.</param>
/// <param name="email">email address of the new user.</param>
/// <param name="passwordQuestion">password reset question for the new user.</param>
/// <param name="passwordAnswer">password reset answer for the new user.</param>
/// <param name="isApproved">a boolean indicating whether the user has been approved or not</param>
/// <param name="providerUserKey">the identifier/key for the user.</param>
/// <param name="status">membership creation status for the user.</param>
/// <returns>A MembershipUser object populated with the information for the newly created user.</returns>
internal MembershipUser CreateUser(
string username,
string password,
string email,
string passwordQuestion,
string passwordAnswer,
bool isApproved,
object providerUserKey,
out MembershipCreateStatus status)
{
// Ensure that the password is valid
if (!ValidationUtil.IsParameterValid(ref password, true, true, false, 128))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Get a new salt and then encode the password
string salt = PasswordUtil.GetSalt(16);
string pass = this.EncodePassword(password, this.config.PasswordFormat, salt);
// Ensure that the password does not exceed the maximum allowed length
if (pass.Length > 128)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Encode the password answer and validate.
string encodedPasswordAnswer;
if (passwordAnswer != null)
{
passwordAnswer = passwordAnswer.Trim();
}
if (!string.IsNullOrEmpty(passwordAnswer))
{
if (passwordAnswer.Length > 128)
{
status = MembershipCreateStatus.InvalidAnswer;
return null;
}
encodedPasswordAnswer = this.EncodePassword(passwordAnswer.ToLower(CultureInfo.InvariantCulture), this.config.PasswordFormat, salt);
}
else
{
encodedPasswordAnswer = passwordAnswer;
}
if (!ValidationUtil.IsParameterValid(ref encodedPasswordAnswer, this.config.RequiresQuestionAndAnswer, true, false, 128))
{
status = MembershipCreateStatus.InvalidAnswer;
return null;
}
// Validate username.
if (!ValidationUtil.IsParameterValid(ref username, true, true, true, 256))
{
status = MembershipCreateStatus.InvalidUserName;
return null;
}
// Validate email
if (!ValidationUtil.IsParameterValid(
ref email,
this.config.RequiresUniqueEmail,
this.config.RequiresUniqueEmail,
false,
256))
{
status = MembershipCreateStatus.InvalidEmail;
return null;
}
// Validate password question.
if (!ValidationUtil.IsParameterValid(ref passwordQuestion, this.config.RequiresQuestionAndAnswer, true, false, 256))
{
status = MembershipCreateStatus.InvalidQuestion;
return null;
}
// Ensure that the key is valid (i.e. a valid Guid).
if (providerUserKey != null)
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
// Check that the password is of a sufficient length.
if (password.Length < this.config.MinRequiredPasswordLength)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Ensure that the password has the requires number of non-alphanumeric characters.
int count = 0;
for (int i = 0; i < password.Length; i++)
{
if (!char.IsLetterOrDigit(password, i))
{
count++;
}
}
if (count < this.config.MinRequiredNonAlphanumericCharacters)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Check that the password meets the required regex strength
if (this.config.PasswordStrengthRegularExpression.Length > 0)
{
if (!Regex.IsMatch(password, this.config.PasswordStrengthRegularExpression))
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
}
// Raise the 'on validating password' event.
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
this.provider.OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
// Make sure that if we require a unique email that there are
// not any existing users with the users email.
if (this.config.RequiresUniqueEmail && (this.GetUserNameByEmail(email) != string.Empty))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
// Ensure the user doesn't exist
MembershipUser user = this.GetUser(username, false);
// The user doesn't exist so create him
if (user == null)
{
this.CreateUserForApplication(username, false, DateTime.Now, ref providerUserKey);
string sql = @"
INSERT INTO aspnet_Membership
(ApplicationId,
UserId,
Password,
PasswordFormat,
PasswordSalt,
MobilePIN,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailPwdAttemptCount,
FailPwdAttemptWindowStart,
FailPwdAnswerAttemptCount,
FailPwdAnswerAttemptWindowStart,
Comment)
VALUES
(?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?)
";
// 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;
cmd.Parameters.Add("UserId", DbType.String).Value = providerUserKey.ToString();
cmd.Parameters.Add("Password", DbType.String).Value = pass;
cmd.Parameters.Add("PasswordFormat", DbType.Int32).Value = (int)this.config.PasswordFormat;
cmd.Parameters.Add("PasswordSalt", DbType.String).Value = salt;
cmd.Parameters.Add("MobilePIN", DbType.String).Value = "PIN";
cmd.Parameters.Add("Email", DbType.String).Value = email;
cmd.Parameters.Add("LoweredEmail", DbType.String).Value = email.ToLower();
cmd.Parameters.Add("PasswordQuestion", DbType.String).Value = passwordQuestion;
cmd.Parameters.Add("PasswordAnswer", DbType.String).Value = encodedPasswordAnswer;
cmd.Parameters.Add("IsApproved", DbType.String).Value = isApproved ? '1' : '0';
cmd.Parameters.Add("IsLockedOut", DbType.String).Value = '0';
cmd.Parameters.Add("CreateDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastLoginDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastPasswordChangedDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("LastLockoutDate", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("FailPwdAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("FailPwdAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("FailPwdAnswerAttemptCount", DbType.Int32).Value = 0;
cmd.Parameters.Add("FailPwdAnswerAttemptWindowStart", DbType.DateTime).Value = DateTime.Now.ToString();
cmd.Parameters.Add("Comment", DbType.String).Value = "Created by the Ingres ASP.NET Membership Provider.";
int rows = cmd.ExecuteNonQuery();
if (rows > 1)
{
throw new Exception(Messages.MoreThanOneRowWasAffected);
}
status = rows > 0 ? MembershipCreateStatus.Success : MembershipCreateStatus.UserRejected;
return this.GetUser(username, false);
}
// The user existed so we return duplicate user name.
status = MembershipCreateStatus.DuplicateUserName;
return null;
}
/// <summary>
/// Takes, as input, the name of a user and deletes that user's information from the data
/// source. The <c>DeleteUser</c> method returns true if the user was successfully deleted;
/// otherwise, false. An additional Boolean parameter is included to indicate whether
/// related information for the user, such as role or profile information is also deleted.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteUser</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 delete.</param>
/// <param name="deleteAllRelatedData">Whether to delete all related data or not.</param>
/// <returns>Returns true if the user was successfully deleted; otherwise, false.</returns>
internal bool DeleteUser(string username, bool deleteAllRelatedData)
{
Guid userId;
if (!this.GetUserIdByName(username, out userId))
{
throw new ProviderException(Messages.UserDoesNotExist);
}
// If the username is an empty string then throw an argument exception.
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
string sql = @"
DELETE FROM aspnet_Membership
WHERE
UserId = ?
";
// 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("UserId", DbType.String).Value = userId.ToString();
// Execute the query and check that an appropriate number of rows have been affected.
int rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
return false;
}
if (rows != 1)
{
throw new Exception(Messages.MoreThanOneUserWouldHaveBeenDeleted);
}
if (deleteAllRelatedData)
{
// Delete from the Users in Roles table
sql =
@"
DELETE FROM aspnet_UsersInRoles
WHERE
UserId = ?";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query.
cmd.ExecuteNonQuery();
// Delete from the Users table
sql = @"
DELETE FROM aspnet_Users
WHERE
UserId = ?";
cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Add the required parameters
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
// Execute the query.
rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.ASingleUserShouldHaveBeenDeleted);
}
// We would also delete from the profile table when the profile provider is implemented.
// We would also delete from the personalization table when the personalization provider is implemented.
}
// The user was successfully deleted and so return true.
return true;
}
/// <summary>
/// Takes, as input, a user name, a password, a password question, and a password answer,
/// and updates the password question and answer in the data source if the supplied user
/// name and password are valid. The <c>ChangePasswordQuestionAndAnswer</c> method returns true if
/// the password question and answer are updated successfully; otherwise, false.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>ChangePasswordQuestionAndAnswer</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="password">The password.</param>
/// <param name="newPasswordQuestion">The new password question.</param>
/// <param name="newPasswordAnswer">The new password answer.</param>
/// <returns>Returns true if the password question and answer are updated successfully; otherwise, false.</returns>
internal bool ChangePasswordQuestionAndAnswer(
string username,
string password,
string newPasswordQuestion,
string newPasswordAnswer)
{
// Check the parameters
ValidationUtil.CheckParameterIsOK(ref username, true, true, true, 256, "username");
ValidationUtil.CheckParameterIsOK(ref password, true, true, false, 128, "password");
string salt;
MembershipPasswordFormat passwordFormat;
// Check the password
if (!this.CheckPassword(username, password, false, false, out salt, out passwordFormat))
{
return false;
}
ValidationUtil.CheckParameterIsOK(ref newPasswordQuestion, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 256, "newPasswordQuestion");
// validate and encode the password answer
string encodedPasswordAnswer;
if (newPasswordAnswer != null)
{
newPasswordAnswer = newPasswordAnswer.Trim();
}
ValidationUtil.CheckParameterIsOK(ref newPasswordAnswer, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 128, "newPasswordAnswer");
if (!string.IsNullOrEmpty(newPasswordAnswer))
{
encodedPasswordAnswer = this.EncodePassword(newPasswordAnswer.ToLower(CultureInfo.InvariantCulture), passwordFormat, salt);
}
else
{
encodedPasswordAnswer = newPasswordAnswer;
}
ValidationUtil.CheckParameterIsOK(ref encodedPasswordAnswer, this.config.RequiresQuestionAndAnswer, this.config.RequiresQuestionAndAnswer, false, 128, "newPasswordAnswer");
// build up the required SQL
string sql = @"
UPDATE aspnet_Membership
FROM
aspnet_Applications,
aspnet_Users
SET PasswordQuestion = ?,
PasswordAnswer = ?
WHERE
aspnet_Users.LoweredUserName = ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Applications.ApplicationId = aspnet_Users.ApplicationId
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// 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("PasswordQuestion", DbType.String).Value = newPasswordQuestion;
cmd.Parameters.Add("PasswordAnswer", DbType.String).Value = encodedPasswordAnswer;
cmd.Parameters.Add("LoweredUsername", DbType.String).Value = username.ToLower();
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
int rowsAffected = cmd.ExecuteNonQuery();
// Ensure that no more than one row was updated.
if (rowsAffected > 1)
{
throw new Exception(Messages.MoreThanOneRowWasAffectedWhenAttemptingToChangeAPassword);
}
return rowsAffected > 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>
/// 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 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>
/// 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>
/// Takes, as input, a user name and a role name and determines whether the specified user
/// is associated with the specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>IsUserInRole</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 that we wish to check.</param>
/// <param name="roleName">The role which we wish to check.</param>
/// <returns>Whether the given user is in the given role.</returns>
internal bool IsUserInRole(string username, string roleName)
{
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username");
if (username.Length < 1)
{
return false;
}
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserNotFound));
}
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// ensure that we have an appication id
if (this.ApplicationId == null)
{
throw new ArgumentNullException("ApplicationId");
}
// Instantiate a bool for whether the user is in the role - assume not.
bool userIsInRole = false;
// Build up the SQL string
string sql = @"
SELECT
COUNT(*)
FROM
aspnet_UsersInRoles
WHERE
UserId = ?
AND RoleId = ?
";
// 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;
// Get the user and role id's for the specified user and role names.
string userid = this.GetUserIdByName(username);
string roleid = this.GetRoleIdByName(roleName);
cmd.Parameters.Add("UserId", DbType.String).Value = userid;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
// Execute the query and determine is the user is in the role.
int rows = Convert.ToInt32(cmd.ExecuteScalar());
if (rows > 0)
{
userIsInRole = true;
}
return userIsInRole;
}
/// <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 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 list of user names and a list of role names and adds the specified
/// users to the specified roles.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>AddUsersToRoles</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="usernames">A list of user names.</param>
/// <param name="roleNames">A list of roles.</param>
internal void AddUsersToRoles(string[] usernames, string[] roleNames)
{
// Note: Most of this validation is also done by the .NET framework - however I will
// explicitly do it here also.
ValidationUtil.CheckArrayParameterIsOk(ref roleNames, true, true, true, 256, "roleNames");
ValidationUtil.CheckArrayParameterIsOk(ref usernames, true, true, true, 256, "usernames");
// Ensure that all of the roleNames are valid.
foreach (string rolename in roleNames)
{
if (!this.RoleExists(rolename))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, rolename));
}
}
// Ensure that all of the usernames are valid.
foreach (string username in usernames)
{
if (!this.UserExists(username))
{
throw new ProviderException(string.Format(Messages.UserWasNotFound, username));
}
}
// Ensure that all of the users actually are in the roles.
foreach (string username in usernames)
{
foreach (string rolename in roleNames)
{
if (this.IsUserInRole(username, rolename))
{
throw new ProviderException(string.Format(Messages.UserAlreadyInRole, username, rolename));
}
}
}
// Instantiate a string to hold the required SQL
string sql = @"
INSERT INTO aspnet_UsersInRoles
(UserId,
RoleId)
VALUES
(?,
?)
";
// 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
IngresParameter userParam = cmd.Parameters.Add("UserId", DbType.String);
IngresParameter roleParam = cmd.Parameters.Add("RoleId", DbType.String);
// Note: this if a bit "row at a time" style rather than "chunk at a time"
// processing - however it makes for easier to read and understand code and
// allows for a static parameterized SQL string. If performance is poor then
// this could be changed.
// for each user
foreach (string username in usernames)
{
// obtain the user id
string userId = this.GetUserIdByName(username);
// for each rolename
foreach (string rolename in roleNames)
{
// obtain role id
string roleId = this.GetRoleIdByName(rolename);
userParam.Value = userId;
roleParam.Value = roleId;
// try to add user to role
// The number of rows affected
int rows = cmd.ExecuteNonQuery();
// One row should be affected
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
}
}
/// <summary>
/// Creates a role in the database.
/// </summary>
/// <param name="roleName">The rolename to create.</param>
/// <param name="roleid">The role id.</param>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
private void CreateRole(string roleName, out string roleid, IngresConnection conn, IngresTransaction tran)
{
// Validate the roleName
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// Ensure that the proposed roleName does not already exist
if (this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleAlreadyExists, roleName));
}
string sql = @"
INSERT INTO aspnet_Roles
(ApplicationId,
RoleId,
RoleName,
LoweredRoleName,
Description)
VALUES
(?,
?,
?,
?,
NULL)
";
// Create the command with the current connection and enrol in the transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.CommandTimeout = this.config.CommandTimeout;
// Generate a new role Id - this will be the sent out
roleid = Guid.NewGuid().ToString().ToLower();
// Add the required parameters
cmd.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
cmd.Parameters.Add("RoleId", DbType.String).Value = roleid;
cmd.Parameters.Add("RoleName", DbType.String).Value = roleName;
cmd.Parameters.Add("LoweredRoleName", DbType.String).Value = roleName.ToLower();
// Execute the query
int rows = cmd.ExecuteNonQuery();
// Validate that the query affected the correct numbber of rows.
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
}
/// <summary>
/// Takes, as input, a role name and a Boolean value that indicates whether to throw an
/// exception if there are users currently associated with the role, and then deletes the
/// specified role.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>DeleteRole</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 user name that we wish to delete.</param>
/// <param name="throwOnPopulatedRole">Whether we should throw an exception if the role
/// we wish to delete has any users in the role or not.</param>
/// <returns>Whether the role was successfully deleted.</returns>
internal bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
// Validate the rolename
ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName");
// The roleName should exist
if (!this.RoleExists(roleName))
{
throw new ProviderException(string.Format(Messages.RoleNotFound, roleName));
}
// If we have to throw errors for a poupulated roll then we must check if there are
// any users in the role.
if (throwOnPopulatedRole && (this.GetUsersInRole(roleName).Length > 0))
{
throw new ProviderException(string.Format(Messages.RoleNotEmpty));
}
string sqlDeleteUsersInRoles = @"
DELETE FROM aspnet_UsersInRoles
WHERE
RoleId = ?
";
// Create a new command and enrol in the current transactions
IngresCommand cmdDeleteUsersInRoles = new IngresCommand(sqlDeleteUsersInRoles, this.conn);
cmdDeleteUsersInRoles.Transaction = this.tran;
cmdDeleteUsersInRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required parameter
cmdDeleteUsersInRoles.Parameters.Add("RoleId", DbType.String).Value = this.GetRoleIdByName(roleName);
// Delete the users
cmdDeleteUsersInRoles.ExecuteNonQuery();
string sqlDeleteRoles = @"
DELETE FROM aspnet_Roles
WHERE
LoweredRolename = ?
AND ApplicationId = ?
";
// Create a new command and enrol in the current transaction
IngresCommand cmdDeleteRoles = new IngresCommand(sqlDeleteRoles, this.conn);
cmdDeleteRoles.Transaction = this.tran;
cmdDeleteRoles.CommandTimeout = this.config.CommandTimeout;
// Add the required paramaters
cmdDeleteRoles.Parameters.Add("LoweredRolename", DbType.String).Value = roleName.ToLower();
cmdDeleteRoles.Parameters.Add("ApplicationId", DbType.String).Value = this.ApplicationId;
// Finally delete the role
int rows = cmdDeleteRoles.ExecuteNonQuery();
// If more than one row was effected then throw an error
if (rows != 1)
{
throw new ProviderException(string.Format(Messages.UnknownError));
}
return true;
}
/// <summary>
/// Create a new DataAdapter object with specified SELECT Command object.
/// </summary>
/// <param name="selectCommand">
/// Command object with SELECT SQL CommandText.</param>
public IngresDataAdapter(IngresCommand selectCommand)
{
this.SelectCommand = selectCommand;
}
/// <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>
/// Populate the IngresCommand.Parameters collection with data type
/// metadata about the parameters of the specified database procedure.
/// </summary>
/// <param name="command"></param>
public static void DeriveParameters(IngresCommand command)
{
if (command == null)
throw new ArgumentNullException(
"DeriveParameters parameter 'command' must not be null.");
IngresConnection conn = command.Connection;
if (conn == null || conn.State != ConnectionState.Open)
throw new InvalidOperationException(
"The IngresCommand.Connection must be specified and open.");
if (command.CommandText == null ||
command.CommandText.Length == 0)
throw new InvalidOperationException(
"The IngresCommand.CommandText must be specify a procedure name.");
if (command.CommandType != CommandType.StoredProcedure)
throw new InvalidOperationException(
"Only CommandType.StoredProcedure is supported.");
ArrayList tokens = Ingres.ProviderInternals.MetaData.ScanSqlStatement(
command.CommandText);
String[] restriction = new String[3];
if (tokens.Count == 1) // procname
{
restriction[2] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
}
else
if (tokens.Count == 3 && // schemaname.procname
(String)tokens[1] == ".")
{
restriction[1] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
}
else
if (tokens.Count == 5 && // catalogname.schemaname.procname
(String)tokens[1] == "." &&
(String)tokens[3] == ".")
{
restriction[0] = UnquoteIdent((String)tokens[0], conn, "\"", "\"");
restriction[1] = UnquoteIdent((String)tokens[2], conn, "\"", "\"");
restriction[2] = UnquoteIdent((String)tokens[4], conn, "\"", "\"");
}
else
throw new InvalidOperationException(
"Invalid procedure name.");
DataTable datatable = conn.GetSchema("ProcedureParameters", restriction);
command.Parameters.Clear();
foreach (DataRow row in datatable.Rows)
{
string name = (String)row["COLUMN_NAME"];
IngresType ingresType = (IngresType)row["INGRESTYPE"];
IngresParameter parm = new IngresParameter(name, ingresType);
command.Parameters.Add(parm);
} // end foreach (DataRow row in datatable)
}
/// <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];
}
private void InitializeIngres()
{
versionDataSet = new DataSet();
// The following query text is used to show the syntax for
// executing a database procedure.
ingresSelectCommand4 = new IngresCommand(
"{ call get_my_airports( ccode = ?, area = ? ) }",
ingresConnection1);
ingresSelectCommand4.Connection = ingresConnection1;
ingresDataAdapter4 = new IngresDataAdapter(ingresSelectCommand4);
ingresSelectCommand7 = new IngresCommand(
"SELECT up_email FROM user_profile ORDER BY up_email");
ingresSelectCommand7.Connection = ingresConnection1;
ingresDataAdapter7 = new IngresDataAdapter(ingresSelectCommand7);
ingresSelectCommand8 = new IngresCommand(
"SELECT airport.ap_iatacode, airport.ap_place, " +
"country.ct_name " +
"FROM airport, country " +
"WHERE airport.ap_ccode = country.ct_code " +
"AND airport.ap_iatacode = ?");
ingresSelectCommand8.Connection = ingresConnection1;
ingresDataAdapter8 = new IngresDataAdapter(ingresSelectCommand8);
if (CheckVersion() == false)
{
// Incorrect version exception.
// Get the string from the string table
Exception badVersion = new Exception(rm.GetString("ErrorVersion"));
throw badVersion;
}
}
/// <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>
/// Helper method to check that the Ingres database used for testing does not have any
/// data in the Roles tables prior to commencing set up of a test case and after we
/// tear down a test case.
/// </summary>
private void ValidateDatabaseIsEmpty()
{
// Validate that the aspnet_UsersInRoles table is empty
IngresCommand cmd = new IngresCommand();
cmd.Connection = this.Connection;
cmd = new IngresCommand();
cmd.Connection = this.Connection;
string sql = @"SELECT
COUNT(*)
FROM
aspnet_UsersInRoles";
cmd.CommandText = sql;
int rows = (int)cmd.ExecuteScalar();
cmd.ExecuteNonQuery();
if (rows != 0)
{
throw new Exception("The aspnet_UsersInRoles table is not empty.");
}
// Validate that the aspnet_Users table is empty
cmd = new IngresCommand();
cmd.Connection = this.Connection;
sql = @"SELECT
COUNT(*)
FROM
aspnet_Users";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
if (rows != 0)
{
throw new Exception("The aspnet_Users table is not empty.");
}
// Validate that the aspnet_Roles table is empty
cmd = new IngresCommand();
cmd.Connection = this.Connection;
sql = @"SELECT
COUNT(*)
FROM
aspnet_Roles";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
if (rows != 0)
{
throw new Exception("The aspnet_Roles table is not empty.");
}
// Validate that the aspnet_Application table is empty.
sql = @"SELECT
COUNT(*)
FROM
aspnet_Applications";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
rows = (int)cmd.ExecuteScalar();
// The aspnet_applications table may contain 0 or 1 rows, depending where we are
// in the test fixture.
if (rows != 0)
{
throw new Exception("The aspnet_Applications is not empty.");
}
}
/// <summary>
/// Creates a new user in the asnet_Users table in the database.
/// </summary>
/// <param name="userName">The username for the user.</param>
/// <param name="isUserAnonymous">Whether the user is anonymous.</param>
/// <param name="lastActivityDate">The last activity date for the user,</param>
/// <param name="userId">The Id for the user that we wish to create.</param>
private void CreateUserForApplication(string userName, bool isUserAnonymous, DateTime lastActivityDate, ref object userId)
{
if (userId == null)
{
userId = Guid.NewGuid();
}
else
{
if (this.DoesUserIdExist((Guid)userId))
{
throw new Exception("Error!!!!");
}
}
string sql = @"
INSERT INTO aspnet_Users
(ApplicationId,
userId,
userName,
LoweredUserName,
IsAnonymous,
lastActivityDate)
VALUES
(?,
?,
?,
?,
?,
?)
";
// 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;
cmd.Parameters.Add("UserId", DbType.String).Value = userId.ToString();
cmd.Parameters.Add("UserName", DbType.String).Value = userName;
cmd.Parameters.Add("LoweredUserName", DbType.String).Value = userName.ToLower();
cmd.Parameters.Add("IsAnonymous", DbType.String).Value = isUserAnonymous.ToString();
cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = lastActivityDate;
// Execute the command and ensure that the row was inserted
int rows = cmd.ExecuteNonQuery();
if (rows != 1)
{
throw new Exception(Messages.ErrorAttemptingToCreateAUser);
}
}