JQuery Datatable Server Side Pagination with Custom Multicolumn Filtering in ASP.Net MVC

Source Code

Introduction

JQuey 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.

However when you have a huge set of data to display performance now becomes a problem because JQuery Datatable will fetch all the data
and then perform the pagination in client side.

In this tutorial, I will walk you through on how to implement your own custom server side pagination, instant search and ordering.

A walk through of the process

Create a new ASP.Net MVC4 or MVC5 Application and name it whatever you like mine was named MvcDatatablePagination.

Add ADO.Net Entity

In 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.

So 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:

Create People Service

First 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:

  public class PersonViewModel
    {
        public string firstName { get; set; }
        public string middleName { get; set; }
        public string lastName { get; set; }
        public string emailAddress { get; set; }
        public string phone { get; set; }
    }

Add another class and name it PersonService like so:

 
  public class PersonService
  {
        AppEntities context;
        public PersonService()
        {
            context = new AppEntities();
        }

        public IQueryable GetPeople()
        {
            return from p in context.People join em in context.EmailAddresses
                   on p.BusinessEntityID equals em.BusinessEntityID
                   join ph in context.PersonPhones on p.BusinessEntityID
                   equals ph.BusinessEntityID
                   select new PersonViewModel
                   {
                       firstName = p.FirstName,
                       lastName =p.LastName,
                       emailAddress = em.EmailAddress1,
                       middleName =p.MiddleName,
                       phone =ph.PhoneNumber
                   };
        }
  }

In this service, as you can see we have only one method that returns IQueryable of people.

JQuery 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.

HomeController ActionResult

Add 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:

 
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();

As you can see, we are fetching those values via Request.Form.GetValues.

draw - The number of times the table has been drawn.
start - Number of record to skip.
length - Number of record to display in a page

The next piece of code is:

   //Global search field
 var search = Request.Form.GetValues("search[value]").FirstOrDefault();

 //Custom column search fields
 var firstName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();
 var middleName = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();
 var lastName = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();
 var email = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
 var phone = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();


 int pageSize = length != null ? Convert.ToInt32(length) : 0;
 int skip = start != null ? Convert.ToInt32(start) : 0;
 int recordsTotal = 0;
 

First we got the values from the global search, and the values from each column field.

The full getPeople JsonResult looks like this:

  [HttpPost]
        public JsonResult getPeople()
        {
            var draw = Request.Form.GetValues("draw").FirstOrDefault();
            var start = Request.Form.GetValues("start").FirstOrDefault();
            var length = Request.Form.GetValues("length").FirstOrDefault();

            
            //Global search field
            var search = Request.Form.GetValues("search[value]").FirstOrDefault();

            //Custom column search fields
            var firstName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();
            var middleName = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();
            var lastName = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();
            var email = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
            var phone = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();


            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;

            var people = personService.GetPeople(); // Get People IQueryble


            //Start search
            if (!string.IsNullOrWhiteSpace(firstName))
            {
                people = people.Where(x => x.firstName.ToLower().Contains(firstName.ToLower()));
            }

            if (!string.IsNullOrWhiteSpace(middleName))
            {
                people = people.Where(x => x.middleName.ToLower().Contains(middleName.ToLower()));
            }

            if (!string.IsNullOrWhiteSpace(lastName))
            {
                people = people.Where(x => x.lastName.ToLower().Contains(lastName.ToLower()));
            }

            if (!string.IsNullOrWhiteSpace(email))
            {
                people = people.Where(x => x.emailAddress.ToLower().Contains(email.ToLower()));
            }

            if (!string.IsNullOrWhiteSpace(phone))
            {
                people = people.Where(x => x.phone.ToLower().Contains(phone.ToLower()));
            }

            if (!string.IsNullOrEmpty(search))
            {
                people = people.Where(x => x.phone.ToLower().Contains(search.ToLower())
                    || x.firstName.ToLower().Contains(search.ToLower())
                    || x.middleName.ToLower().Contains(search.ToLower())
                    || x.lastName.ToLower().Contains(search.ToLower())
                    || x.emailAddress.ToLower().Contains(search.ToLower()));
            }

            recordsTotal = people.Count();

         
            var data = people.OrderBy(x => x.firstName).Skip(skip).Take(pageSize);
            
            return Json(new {
                draw = draw,
                recordsFiltered = recordsTotal,
                recordsTotal = recordsTotal,
                data = data
            },JsonRequestBehavior.AllowGet);
        }
 

That is all we need to do in our controller, next we need to move to our view.

Index.cshtml Code

In your view add the table that will hold the data like so:

 

  

 



   
First Name
   
Middle Name
   
Last Name
   
Email
   
Phone
   
  

 


   

   

   

   

   

   
  


 

What 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().

The next thing is to setup the ajax part.  Add the following script below your index.cshtml page

Build and run your solution.
The complete source code can be downloaded here
Thanks your reading.