n
n
n
n
n
nIntroduction
nJQuey Datatable come in handy whenever you want to display record that will need sorting, paging,and searching. it came out of the box – without any special configuration with Pagination,Instant search and multi-column ordering.
n
nHowever when you have a huge set of data to display performance now becomes a problem because JQuery Datatable will fetch all the data nnn
nnnand then perform the pagination in client side.
n
nIn this tutorial, I will walk you through on how to implement your own custom server side pagination, instant search and ordering.
n
n
n
nA walk through of the process
nCreate a new ASP.Net MVC4 or MVC5 Application and name it whatever you like mine was named MvcDatatablePagination.
n
n
nAdd ADO.Net Entity
nIn order to get a very huge data set to work with, we will be making use of three tables from adventureworks database that has 19, 972 records.
nnn
nnnSo add a new ADO.NET Entity Data Model, and add Person.Person,Person.EmailAddress and Person.PersonPhone. After add those tables your Entity should look like this:
n
n
n
n
n
nCreate People Service
nFirst we need to add a new composite class that will combine the data that will be coming from the three tables. So add a class and name it PersonViewModel like so:
n
n
public class PersonViewModeln {n public string firstName { get; set; }n public string middleName { get; set; }n public string lastName { get; set; }n public string emailAddress { get; set; }n public string phone { get; set; }n }nn
n
nAdd another class and name it PersonService like so:
n
n public class PersonServicen {n AppEntities context;n public PersonService()n {n context = new AppEntities();n }nn public IQueryable GetPeople()n {n return from p in context.People join em in context.EmailAddressesn on p.BusinessEntityID equals em.BusinessEntityIDn join ph in context.PersonPhones on p.BusinessEntityIDn equals ph.BusinessEntityIDn select new PersonViewModeln {n firstName = p.FirstName,n lastName =p.LastName,n emailAddress = em.EmailAddress1,n middleName =p.MiddleName,n phone =ph.PhoneNumbern };n }n }n
n
nIn this service, as you can see we have only one method that returns IQueryable of people.
nnnnn
nJQuery DataTable needs to send post request to get required data back as Json. While sending the post request, it sends some bunch values that we can make use of in our MVC controller, in our own case we will be making use of few of them. Those values can be gotten via Request.Form.GetValues() method.
n
n
nHomeController ActionResult
nAdd a JsonResult method to your HomeController and name it getPeople. First we need to get the values of draw,start and length values like so:
n
nvar draw = Request.Form.GetValues("draw").FirstOrDefault();nvar start = Request.Form.GetValues("start").FirstOrDefault();nvar length = Request.Form.GetValues("length").FirstOrDefault();n
n
nnnAs you can see, we are fetching those values via Request.Form.GetValues.
n
ndraw – The number of times the table has been drawn.
nstart – Number of record to skip.
nlength – Number of record to display in a page
n
nThe next piece of code is:
nnnnn
n
//Global search fieldn var search = Request.Form.GetValues("search[value]").FirstOrDefault();nn //Custom column search fieldsn var firstName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();n var middleName = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();n var lastName = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();n var email = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();n var phone = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();nnn int pageSize = length != null ? Convert.ToInt32(length) : 0;n int skip = start != null ? Convert.ToInt32(start) : 0;n int recordsTotal = 0;n n
n
nFirst we got the values from the global search, and the values from each column field.
n
nThe full getPeople JsonResult looks like this:
n
[HttpPost]n public JsonResult getPeople()n {n var draw = Request.Form.GetValues("draw").FirstOrDefault();n var start = Request.Form.GetValues("start").FirstOrDefault();n var length = Request.Form.GetValues("length").FirstOrDefault();nn n //Global search fieldn var search = Request.Form.GetValues("search[value]").FirstOrDefault();nn //Custom column search fieldsn var firstName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();n var middleName = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();n var lastName = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();n var email = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();n var phone = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();nnn int pageSize = length != null ? Convert.ToInt32(length) : 0;n int skip = start != null ? Convert.ToInt32(start) : 0;n int recordsTotal = 0;nn var people = personService.GetPeople(); // Get People IQueryblennn //Start searchn if (!string.IsNullOrWhiteSpace(firstName))n {n people = people.Where(x => x.firstName.ToLower().Contains(firstName.ToLower()));n }nn if (!string.IsNullOrWhiteSpace(middleName))n {n people = people.Where(x => x.middleName.ToLower().Contains(middleName.ToLower()));n }nn if (!string.IsNullOrWhiteSpace(lastName))n {n people = people.Where(x => x.lastName.ToLower().Contains(lastName.ToLower()));n }nn if (!string.IsNullOrWhiteSpace(email))n {n people = people.Where(x => x.emailAddress.ToLower().Contains(email.ToLower()));n }nn if (!string.IsNullOrWhiteSpace(phone))n {n people = people.Where(x => x.phone.ToLower().Contains(phone.ToLower()));n }nn if (!string.IsNullOrEmpty(search))n {n people = people.Where(x => x.phone.ToLower().Contains(search.ToLower())n || x.firstName.ToLower().Contains(search.ToLower())n || x.middleName.ToLower().Contains(search.ToLower())n || x.lastName.ToLower().Contains(search.ToLower())n || x.emailAddress.ToLower().Contains(search.ToLower()));n }nn recordsTotal = people.Count();nn n var data = people.OrderBy(x => x.firstName).Skip(skip).Take(pageSize);n n return Json(new {n draw = draw,n recordsFiltered = recordsTotal,n recordsTotal = recordsTotal,n data = datan },JsonRequestBehavior.AllowGet);n }n n
nThat is all we need to do in our controller, next we need to move to our view.
nnnn
nIndex.cshtml Code
n
nIn your view add the table that will hold the data like so:
n
n
nn
| First Name | nMiddle Name | nLast Name | nPhone | n n|
|---|---|---|---|---|
| n | n | n | n | n n |
n
nWhat we have here is a regular html table. But as you can see there are two thead tags. The first thead tag is the normal header caption, and the second on with the id of searchHeader is for the individual column search input fields that we made reference to using the data-column attribute in our controller like so: var firstName = Request.Form.GetValues(“columns[1][search][value]”).FirstOrDefault().
nnn
nnnThe next thing is to setup the ajax part. Add the following script below your index.cshtml page
n
n
nn nn n
n
nnnnnBuild and run your solution.n
nThe complete source code can be downloaded here
nThanks your reading.
n
n
n
n
n
n