private void OnSortEvent(PersonSortCriteria criteria, SortDirection sortDirection)
{
this.sortCriteria = criteria;
this.sortDirection = sortDirection;
LoadDataForCurrentPage();
}
public IActionResult List(int pageIndex = 0, int pageSize = 20, PersonSortCriteria criteria = PersonSortCriteria.ById, SortDirection sortDirection = SortDirection.Ascending)
{
var personBO = new PersonBusinessObject(this.personRepository);
var data = personBO.GetPersonsPaged(criteria, sortDirection);
return(View("Index", data));
}
public void Start()
{
this.menuView.OnViewEvent += MenuView_OnViewEvent;
this.sortCriteria = PersonSortCriteria.ById;
this.sortDirection = SortDirection.Ascending;
this.OnGoToFirstPageEvent();
}
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 IActionResult List(int pageIndex = 0, int pageSize = 20, PersonSortCriteria criteria = PersonSortCriteria.ById, SortDirection sortDirection = SortDirection.Ascending)
{
var personBO = new PersonBusinessObject(this.personRepository, this.fileNameGenerator, this.fileManager);
var data = personBO.GetPersonsPaged(pageIndex, pageSize, criteria, sortDirection);
if (pageIndex > data.GetLastPageIndex())
{
pageIndex = data.GetLastPageIndex();
data = personBO.GetPersonsPaged(pageIndex, pageSize, criteria, sortDirection);
}
return(View("Index", data));
}
public SortedPagedCollection <Person, PersonSortCriteria> GetPersonsPaged(int pageIndex, int pageSize, PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
if (pageIndex < 0)
{
throw new ArgumentException($"{nameof(pageIndex)} must be a positive numeric value");
}
if (pageSize <= 0)
{
throw new ArgumentException($"{nameof(pageSize)} must be a strict-positive numeric value");
}
return(this.personsRepository.GetPersonsPaged(pageIndex, pageSize, sortCriteria, sortDirection));
}
SortedCollection <Person, PersonSortCriteria> GetPersonSorted(PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
IEnumerable <Person> query = InMemoryPersonRepository.personsCollection;
switch (sortCriteria)
{
case PersonSortCriteria.ById:
default:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.PersonID);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.PersonID);
break;
}
break;
case PersonSortCriteria.ByFirstName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.FirstName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.FirstName);
break;
}
break;
case PersonSortCriteria.ByLastName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.LastName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.LastName);
break;
}
break;
case PersonSortCriteria.ByBirthDate:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.DateOfBirth);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.DateOfBirth);
break;
}
break;
}
int totalRecordsCount = query.Count();
var data = query;
return(new SortedCollection <Person, PersonSortCriteria>(data, sortCriteria, sortDirection));
}
SortedCollection <Person, PersonSortCriteria> IPersonRepository.GetPersonSorted(PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
throw new NotImplementedException();
}
public SortedCollection <Person, PersonSortCriteria> GetPersonSorted(PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
IEnumerable <Person> query = from persElement in this.xmlDocument.Descendants("Person")
let dateOfBirthString = persElement.Element("DateOfBirth")?.Value
let dateOfBirthFormat = persElement.Element("DateOfBirth") != null?
persElement.Element("DateOfBirth").Attribute("format")?.Value
:
""
select new Person()
{
PersonID = int.Parse(persElement.Attribute("PersonID").Value),
FirstName = persElement.Element("FirstName")?.Value,
LastName = persElement.Element("LastName")?.Value,
DateOfBirth = dateOfBirthString.ParseWithFormat(dateOfBirthFormat)
};
switch (sortCriteria)
{
case PersonSortCriteria.ById:
default:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.PersonID);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.PersonID);
break;
}
break;
case PersonSortCriteria.ByFirstName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.FirstName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.FirstName);
break;
}
break;
case PersonSortCriteria.ByLastName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.LastName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.LastName);
break;
}
break;
case PersonSortCriteria.ByBirthDate:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.DateOfBirth);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.DateOfBirth);
break;
}
break;
}
var data = query;
return(new SortedCollection <Person, PersonSortCriteria>(data, sortCriteria, sortDirection));
}
public SortedCollection <Person, PersonSortCriteria> GetPersonsPaged(PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
return(this.personsRepository.GetPersonSorted(sortCriteria, sortDirection));
}
public SortedPagedCollection <Person, PersonSortCriteria> GetPersonsPaged(int pageIndex, int pageSize, PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
IEnumerable <Person> query = from persElement in this.xmlDocument.Descendants("Person")
let dateOfBirthString = persElement.Element("DateOfBirth")?.Value
let dateOfBirthFormat = persElement.Element("DateOfBirth") != null?
persElement.Element("DateOfBirth").Attribute("format")?.Value
:
""
select new Person()
{
Id = int.Parse(persElement.Attribute("id").Value),
FirstName = persElement.Element("FirstName")?.Value,
LastName = persElement.Element("LastName")?.Value,
DateOfBirth = dateOfBirthString.ParseWithFormat(dateOfBirthFormat)
};
switch (sortCriteria)
{
case PersonSortCriteria.ById:
default:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.Id);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.Id);
break;
}
break;
case PersonSortCriteria.ByFirstName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.FirstName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.FirstName);
break;
}
break;
case PersonSortCriteria.ByLastName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.LastName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.LastName);
break;
}
break;
case PersonSortCriteria.ByBirthDate:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.DateOfBirth);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.DateOfBirth);
break;
}
break;
}
int totalRecordsCount = query.Count();
var data = query.Skip(pageIndex * pageSize).Take(pageSize);
return(new SortedPagedCollection <Person, PersonSortCriteria>(data, pageIndex, pageSize, totalRecordsCount, 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));
}
public SortedPagedCollection <Person, PersonSortCriteria> GetPersonsPaged(int pageIndex, int pageSize, PersonSortCriteria sortCriteria, SortDirection sortDirection)
{
IEnumerable <Person> query = InMemoryPersonRepository.personsCollection;
switch (sortCriteria)
{
case PersonSortCriteria.ById:
default:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.Id);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.Id);
break;
}
break;
case PersonSortCriteria.ByFirstName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.FirstName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.FirstName);
break;
}
break;
case PersonSortCriteria.ByLastName:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.LastName);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.LastName);
break;
}
break;
case PersonSortCriteria.ByBirthDate:
switch (sortDirection)
{
case SortDirection.Ascending:
default:
query = query.OrderBy(p => p.DateOfBirth);
break;
case SortDirection.Descending:
query = query.OrderByDescending(p => p.DateOfBirth);
break;
}
break;
}
int totalRecordsCount = query.Count();
var data = query.Skip(pageIndex * pageSize).Take(pageSize);
return(new SortedPagedCollection <Person, PersonSortCriteria>(data, pageIndex, pageSize, totalRecordsCount, sortCriteria, sortDirection));
}
