public SortedCollection <Person, PersonSortCriteria> GetPersonSorted(PersonSortCriteria sortCriteria, SortDirection sortDirection) { var sqlSort = new StringBuilder(); ValueTypeMultiSwitch <PersonSortCriteria, SortDirection> .On(sortCriteria, sortDirection) .Case(PersonSortCriteria.ById, SortDirection.Ascending, () => sqlSort.Append("PersonID Asc")) .Case(PersonSortCriteria.ById, SortDirection.Descending, () => sqlSort.Append("PersonID Desc")) .Case(PersonSortCriteria.ByFirstName, SortDirection.Ascending, () => sqlSort.Append("FirstName Asc")) .Case(PersonSortCriteria.ByFirstName, SortDirection.Descending, () => sqlSort.Append("FirstName Desc")) .Case(PersonSortCriteria.ByLastName, SortDirection.Ascending, () => sqlSort.Append("LastName Asc")) .Case(PersonSortCriteria.ByLastName, SortDirection.Descending, () => sqlSort.Append("LastName Desc")) .Case(PersonSortCriteria.ByBirthDate, SortDirection.Ascending, () => sqlSort.Append("DateOfBirth Asc")) .Case(PersonSortCriteria.ByBirthDate, SortDirection.Descending, () => sqlSort.Append("DateOfBirth Desc")) .Default(() => sqlSort.Append("PersonID Asc")) .Evaluate(); var sqlData = new StringBuilder(); sqlData.Append(@" SELECT [PersonID] ,[FirstName] ,[LastName] ,[DateOfBirth] FROM [dbo].[persons] order by " + sqlSort.ToString()); List <Person> itemsPerPage = new List <Person>(); using (var sqlConnection = new SqlConnection(this.connectionString)) { sqlConnection.Open(); using (var sqlCmd = new SqlCommand(sqlData.ToString(), sqlConnection)) { using (var reader = sqlCmd.ExecuteReader()) { while (reader.Read()) { itemsPerPage.Add(new Person { PersonID = (int)reader["PersonID"], FirstName = reader["FirstName"]?.ToString(), LastName = reader["LastName"]?.ToString(), DateOfBirth = (reader["DateOfBirth"] != DBNull.Value) ? (DateTime)reader["DateOfBirth"] : DateTime.MinValue }); } } } sqlConnection.Close(); } return(new SortedCollection <Person, PersonSortCriteria>(itemsPerPage, sortCriteria, sortDirection)); }
public SortedPagedCollection <Person, PersonSortCriteria> GetPersonsPaged(int pageIndex, int pageSize, PersonSortCriteria sortCriteria, SortDirection sortDirection) { var sqlSort = new StringBuilder(); ValueTypeMultiSwitch <PersonSortCriteria, SortDirection> .On(sortCriteria, sortDirection) .Case(PersonSortCriteria.ById, SortDirection.Ascending, () => sqlSort.Append("Id Asc")) .Case(PersonSortCriteria.ById, SortDirection.Descending, () => sqlSort.Append("Id Desc")) .Case(PersonSortCriteria.ByFirstName, SortDirection.Ascending, () => sqlSort.Append("FirstName Asc")) .Case(PersonSortCriteria.ByFirstName, SortDirection.Descending, () => sqlSort.Append("FirstName Desc")) .Case(PersonSortCriteria.ByLastName, SortDirection.Ascending, () => sqlSort.Append("LastName Asc")) .Case(PersonSortCriteria.ByLastName, SortDirection.Descending, () => sqlSort.Append("LastName Desc")) .Case(PersonSortCriteria.ByBirthDate, SortDirection.Ascending, () => sqlSort.Append("DateOfBirth Asc")) .Case(PersonSortCriteria.ByBirthDate, SortDirection.Descending, () => sqlSort.Append("DateOfBirth Desc")) .Default(() => sqlSort.Append("Id Asc")) .Evaluate(); var sqlData = new StringBuilder(); sqlData.Append(@" SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY " + sqlSort.ToString() + @") as RowNumber, Id, FirstName, LastName, DateOfBirth, ImageFileName FROM Person ) as data WHERE data.RowNumber Between (@PageIndex * @PageSize + 1) and ((@PageIndex + 1) * @PageSize) "); var sqlCount = new StringBuilder(); sqlCount.Append(@" select COUNT(*) from Person; "); List <Person> itemsPerPage = new List <Person>(); int totalRecordsCount = 0; using (var sqlConnection = new SqlConnection(this.connectionString)) { sqlConnection.Open(); using (var sqlCmd = new SqlCommand(sqlData.ToString(), sqlConnection)) { sqlCmd.Parameters.AddWithValue("PageIndex", pageIndex); sqlCmd.Parameters.AddWithValue("PageSize", pageSize); using (var reader = sqlCmd.ExecuteReader()) { while (reader.Read()) { itemsPerPage.Add(new Person { Id = (int)reader["Id"], FirstName = reader["FirstName"]?.ToString(), LastName = reader["LastName"]?.ToString(), DateOfBirth = (reader["DateOfBirth"] != DBNull.Value) ? (DateTime)reader["DateOfBirth"] : DateTime.MinValue, ImageFileName = reader["ImageFileName"]?.ToString() }); } } } using (var sqlCmd = new SqlCommand(sqlCount.ToString(), sqlConnection)) { totalRecordsCount = (int)sqlCmd.ExecuteScalar(); } sqlConnection.Close(); } return(new SortedPagedCollection <Person, PersonSortCriteria>(itemsPerPage, pageIndex, pageSize, totalRecordsCount, sortCriteria, sortDirection)); }