This article explains how to implement paging and sorting in MVC using Entity Framework Paged List Library.
- Getting Started
- Create a new project; first open Visual Studio 2012
- Then go to "File" => "New" => "Project..."
- Select Web in installed templates
- Select ASP.NET MVC 4 Web Application
- Enter the Name and choose the location
- Click OK
Please refer to this article to bind the Entity Framework and load data into views:
http://www.c-sharpcorner.com/UploadFile/raj1979/unit-testing-in-mvc-4-using-entity-framework/
Now let's add a new assembly using Manage Nuget Packages and type.
![img1.jpg]()
Image 1.
And install it.
This is my Model class:
public partial class Employee
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public int DepartmentID { get; set; }
public decimal Salary { get; set; }
public System.DateTime HireDate { get; set; }
}
This is my repository class:
public class EmployeeRepository : IEmployeeRepository, IDisposable
{
private PagingDataEntities2 context;
public EmployeeRepository(PagingDataEntities2 context)
{
this.context = context;
}
public IEnumerable<Employee> GetEmployee()
{
return context.Employees.ToList();
}
public Employee GetEmployeeByID(int EmployeeID)
{
return context.Employees.Find(EmployeeID);
}
public void InsertEmployee(Employee employee)
{
context.Employees.Add(employee);
}
public void DeleteEmployee(int EmployeeID)
{
Employee employee = context.Employees.Find(EmployeeID);
context.Employees.Remove(employee);
}
public void UpdateEmployee(Employee employee)
{
context.Entry(employee).State = EntityState.Modified;
}
public void Save()
{
context.SaveChanges();
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
context.Dispose();
}
}
this.disposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
This is the code for the IEmployeeRepository interface:
public interface IEmployeeRepository : IDisposable
{
IEnumerable<Employee> GetEmployee();
Employee GetEmployeeByID(int EmployeeID);
void InsertEmployee(Employee employee);
void DeleteEmployee(int EmployeeID);
void UpdateEmployee(Employee employee);
void Save();
}
Let's work on the controller now.
public class EmployeeController : Controller
{
private IEmployeeRepository employeeRepository;
public EmployeeController()
{
this.employeeRepository = new EmployeeRepository(new PagingDataEntities2());
}
public EmployeeController(IEmployeeRepository employeeRepository)
{
this.employeeRepository = employeeRepository;
}
//
// GET: /Employee/
public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Salery desc" : "";
ViewBag.DateSortParm = sortOrder == "HireDate" ? "HireDate desc" : "HireDate";
if (Request.HttpMethod == "GET")
{
searchString = currentFilter;
}
else
{
page = 1;
}
ViewBag.CurrentFilter = searchString;
var employees = from s in employeeRepository.GetEmployee()
select s;
if (!String.IsNullOrEmpty(searchString))
{
employees = employees.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())
|| s.FirstName.ToUpper().Contains(searchString.ToUpper()));
}
switch (sortOrder)
{
case "Salery desc":
employees = employees.OrderByDescending(s => s.LastName);
break;
case "HireDate":
employees = employees.OrderBy(s => s.HireDate);
break;
case "HireDate desc":
employees = employees.OrderByDescending(s => s.HireDate);
break;
default:
employees = employees.OrderBy(s => s.LastName);
break;
}
int pageSize = 10;
int pageNumber = (page ?? 1);
return View(employees.ToPagedList(pageNumber, pageSize));
}
//
// GET: /Employee/Details/5
public ViewResult Details(int id)
{
Employee employee = employeeRepository.GetEmployeeByID(id);
return View(employee);
}
//
// GET: /Employee/Create
public ActionResult Create()
{
return View();
}
//
// POST: /Employee/Create
[HttpPost]
public ActionResult Create(Employee employee)
{
try
{
if (ModelState.IsValid)
{
employeeRepository.InsertEmployee(employee);
employeeRepository.Save();
return RedirectToAction("Index");
}
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
}
return View(employee);
}
//
// GET: /Employee/Edit/5
public ActionResult Edit(int id)
{
Employee employee = employeeRepository.GetEmployeeByID(id);
return View(employee);
}
//
// POST: /Employee/Edit/5
[HttpPost]
public ActionResult Edit(Employee employee)
{
try
{
if (ModelState.IsValid)
{
employeeRepository.UpdateEmployee(employee);
employeeRepository.Save();
return RedirectToAction("Index");
}
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
}
return View(employee);
}
//
// GET: /Employee/Delete/5
public ActionResult Delete(int id, bool? saveChangesError)
{
if (saveChangesError.GetValueOrDefault())
{
ViewBag.ErrorMessage = "Unable to save changes. Try again, and if the problem persists see your system administrator.";
}
Employee employee = employeeRepository.GetEmployeeByID(id);
return View(employee);
}
//
// POST: /Employee/Delete/5
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
try
{
Employee employee = employeeRepository.GetEmployeeByID(id);
employeeRepository.DeleteEmployee(id);
employeeRepository.Save();
}
catch (DataException)
{
//Log the error (add a variable name after DataException)
return RedirectToAction("Delete",
new System.Web.Routing.RouteValueDictionary {
{ "id", id },
{ "saveChangesError", true } });
}
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
employeeRepository.Dispose();
base.Dispose(disposing);
}
}
Now add a View using the Razor View Engine and select a model class and scaffold template; see:
@model PagedList.IPagedList<MvcPagingSorting.Models.Employee>
@{
ViewBag.Title = "Employees";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
@using (Html.BeginForm())
{
<p>
Search By Name : @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)
<input type="submit" value="Search" /></p>
}
<table>
<tr>
<th>
@Html.Label("Last Name", ViewBag.CurrentFilter as string)
</th>
<th>
@Html.Label("First Name", ViewBag.CurrentFilter as string)
</th>
<th>
@Html.Label("Department ID", ViewBag.CurrentFilter as string)
</th>
<th>
@Html.ActionLink("Salery", "Index", new { sortOrder=ViewBag.NameSortParm, currentFilter=ViewBag.CurrentFilter })
</th>
<th>
@Html.ActionLink("HireDate", "Index", new { sortOrder=ViewBag.NameSortParm, currentFilter=ViewBag.CurrentFilter })
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstName)
</td>
<td>
@Html.DisplayFor(modelItem => item.DepartmentID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Salary)
</td>
<td>
@Html.DisplayFor(modelItem => item.HireDate)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.EmployeeID }) |
@Html.ActionLink("Details", "Details", new { id=item.EmployeeID }) |
@Html.ActionLink("Delete", "Delete", new { id=item.EmployeeID })
</td>
</tr>
}
<div>
Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber)
of @Model.PageCount
@if (Model.HasPreviousPage)
{
@Html.ActionLink("<<", "Index", new { page = 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter })
@Html.Raw(" ");
@Html.ActionLink("< Prev", "Index", new { page = Model.PageNumber - 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter })
}
else
{
@:<<
@Html.Raw(" ");
@:< Prev
}
@if (Model.HasNextPage)
{
@Html.ActionLink("Next >", "Index", new { page = Model.PageNumber + 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter })
@Html.Raw(" ");
@Html.ActionLink(">>", "Index", new { page = Model.PageCount, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter })
}
else
{
@:Next >
@Html.Raw(" ")
@:>>
}
</div>
</table>
Now let's run the project to see the output:
![img2.jpg]()
Image 2.
Let's search by name.
![img3.jpg]()
Image 3.
Click on the paging link and see the query string.
![img4.jpg]()
Image 4.
Click on the Salary and Hire Date columns to test sorting.
![img5.jpg]()
Image 5.