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