//
// MembershipProvider.FindUsersByEmail
//
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords) {
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
"WHERE Email LIKE ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
OdbcDataReader reader = null;
totalRecords = 0;
try {
conn.Open();
totalRecords = (int)cmd.ExecuteScalar();
if (totalRecords <= 0) { return users; }
cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
" FROM Users " +
" WHERE Email LIKE ? AND ApplicationName = ? " +
" ORDER BY Username Asc";
reader = cmd.ExecuteReader();
int counter = 0;
int startIndex = pageSize * pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read()) {
if (counter >= startIndex) {
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex) { cmd.Cancel(); }
counter++;
}
}
catch (OdbcException e) {
if (WriteExceptionsToEventLog) {
WriteToEventLog(e, "FindUsersByEmail");
throw new ProviderException(exceptionMessage);
}
else {
throw e;
}
}
finally {
if (reader != null) { reader.Close(); }
conn.Close();
}
return users;
}
//
// GetProfileInfo
// Retrieves a count of profiles and creates a
// ProfileInfoCollection from the profile data in the
// database. Called by GetAllProfiles, GetAllInactiveProfiles,
// FindProfilesByUserName, FindInactiveProfilesByUserName,
// and GetNumberOfInactiveProfiles.
// Specifying a pageIndex of 0 retrieves a count of the results only.
//
private ProfileInfoCollection GetProfileInfo(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
object userInactiveSinceDate,
int pageIndex,
int pageSize,
out int totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
// Command to retrieve the total count.
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = ? ", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// Command to retrieve the profile data.
OdbcCommand cmd2 = new OdbcCommand("SELECT Username, LastActivityDate, LastUpdatedDate, " +
"IsAnonymous FROM Profiles WHERE ApplicationName = ? ", conn);
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// If searching for a user name to match, add the command text and parameters.
if (usernameToMatch != null)
{
cmd.CommandText += " AND Username LIKE ? ";
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd2.CommandText += " AND Username LIKE ? ";
cmd2.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
}
// If searching for inactive profiles,
// add the command text and parameters.
if (userInactiveSinceDate != null)
{
cmd.CommandText += " AND LastActivityDate <= ? ";
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
cmd2.CommandText += " AND LastActivityDate <= ? ";
cmd2.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
}
// If searching for a anonymous or authenticated profiles,
// add the command text and parameters.
switch (authenticationOption)
{
case ProfileAuthenticationOption.Anonymous:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.TinyInt).Value = true;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.TinyInt).Value = true;
break;
case ProfileAuthenticationOption.Authenticated:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.TinyInt).Value = false;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.TinyInt).Value = false;
break;
default:
break;
}
// Get the data.
OdbcDataReader reader = null;
ProfileInfoCollection profiles = new ProfileInfoCollection();
try
{
conn.Open();
// Get the profile count.
totalRecords = int.Parse(cmd.ExecuteScalar().ToString());
// No profiles found.
if (totalRecords <= 0) { return profiles; }
// Count profiles only.
if (pageSize == 0) { return profiles; }
reader = cmd2.ExecuteReader();
int counter = 0;
int startIndex = pageSize * (pageIndex - 1);
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
ProfileInfo p = GetProfileInfoFromReader(reader);
profiles.Add(p);
}
if (counter >= endIndex)
{
cmd.Cancel();
break;
}
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetProfileInfo");
throw new ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return profiles;
}
