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