// // MembershipProvider.GetAllUsers // public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { SqliteConnection conn = new SqliteConnection(connectionString); SqliteCommand cmd = new SqliteCommand("SELECT Count(*) FROM `" + tableName + "` " + "WHERE ApplicationName = $ApplicationName", conn); cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = ApplicationName; MembershipUserCollection users = new MembershipUserCollection(); SqliteDataReader reader = null; totalRecords = 0; try { conn.Open(); totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); if (totalRecords <= 0) { return users; } cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + " FROM `" + tableName + "` " + " WHERE ApplicationName = $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 (SqliteException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetAllUsers"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return users; }
public override MembershipUserCollection FindUsersByName( string usernameToMatch, int pageIndex, int pageSize, out int totalRecords) { SqliteConnection conn = new SqliteConnection(connectionString); SqliteCommand cmd = new SqliteCommand("SELECT Count(*) FROM `" + tableName + "` " + "WHERE Username LIKE $UsernameSearch AND ApplicationName = $ApplicationName", conn); cmd.Parameters.Add("$UsernameSearch", DbType.String).Value = usernameToMatch; cmd.Parameters.Add("$ApplicationName", DbType.String).Value = pApplicationName; MembershipUserCollection users = new MembershipUserCollection(); SqliteDataReader reader = null; totalRecords = 0; try { conn.Open(); totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); if (totalRecords <= 0) { return users; } cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + " FROM `" + tableName + "` " + " WHERE Username LIKE $UsernameSearch AND ApplicationName = $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 (Exception e) { log.Error(e.Message, e.InnerException); } finally { if (reader != null) { reader.Close(); } conn.Close(); } return users; }