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