/// <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>
/// 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>
/// 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>
/// 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>
/// Returns an integer value that is the count of all the users in the data source where
/// the <c>LastActivityDate</c> is greater than the current date and time minus the
/// <c>UserIsOnlineTimeWindow</c> property. The <c>UserIsOnlineTimeWindow</c> property is an integer
/// value specifying the number of minutes to use when determining whether a user is online.
/// </summary>
/// <remarks>
/// This is the main implementation for the <c>GetNumberOfUsersOnline</c> method of the provider. Please
/// see the corresponding method in the Facade, which calls this method, for full documentaion.
/// </remarks>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
/// <returns>The number of users online.</returns>
internal int GetNumberOfUsersOnline(IngresConnection conn, IngresTransaction tran)
{
TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
string sql = @"
SELECT
Count(*)
FROM
aspnet_Membership,
aspnet_Applications,
aspnet_Users
WHERE
aspnet_Users.LastActivityDate > ?
AND aspnet_Applications.LoweredApplicationName = ?
AND aspnet_Membership.ApplicationId = aspnet_Applications.ApplicationId
AND aspnet_Users.UserId = aspnet_Membership.UserId
";
// 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("CompareDate", DbType.DateTime).Value = compareTime;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
// Execute the command to obtain the number of users online
int numOnline = Convert.ToInt32(cmd.ExecuteScalar());
return numOnline;
}
/// <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;
}