Post
Ask Question

Server Side Pagination And Searching With DataTable And ASP.NET MVC

Hello everyone!
 
Today, we are going to integrate DataTable plugin with ASP.NET MVC website and perform some server-side searching and paging with it. As all of you might have heard, DataTable is a jQuery plugin that provides grid functionality with inbuilt searching, sorting, and paging functionalities.

Step 1

Create two action methods in Controller, one for displaying View and another method will return the data.

  1. public ActionResult All(int rwaId=0)  
  2.        {  
  3.            return View();  
  4.        }  
  5.   
  6.        public string GetPatientList(string sEcho, int iDisplayStart, int iDisplayLength, string sSearch)  
  7.        {  
  8.            string test = string.Empty;  
  9.            sSearch = sSearch.ToLower();  
  10.            int totalRecord = ent.Patients.Count();  
  11.            var patients = new List<Patient>();  
  12.            if (!string.IsNullOrEmpty(sSearch))  
  13.                patients = ent.Patients.Where(a=>a.EmailId.ToLower().Contains(sSearch)  
  14.                || a.PatientName.ToLower().Contains(sSearch)  
  15.                || a.MobileNumber.StartsWith(sSearch)  
  16.                ).OrderBy(a => a.Id).Skip(iDisplayStart).Take(iDisplayLength).ToList();  
  17.            else  
  18.                patients = ent.Patients.OrderBy(a => a.Id).Skip(iDisplayStart).Take(iDisplayLength).ToList();  
  19.   
  20.            var result = (from p in patients join s in ent.StateMasters  
  21.                         on  p.StateMaster_Id equals s.Id  
  22.                         join c in ent.CityMasters   
  23.                         on p.CityMaster_Id equals c.Id  
  24.                         select new PatientDTO  
  25.                         {  
  26.                             CityName=c.CityName,  
  27.                             StateName=s.StateName,  
  28.                             Id=p.Id,  
  29.                             IsApproved=p.IsApproved,  
  30.                             IsDeleted=p.IsDeleted,  
  31.                             CityMaster_Id=p.CityMaster_Id,  
  32.                             EmailId=p.EmailId,  
  33.                             MobileNumber=p.MobileNumber,  
  34.                             StateMaster_Id=p.StateMaster_Id,  
  35.                             PatientName=p.PatientName,  
  36.                             Rwa_Id=p.Rwa_Id,  
  37.                             Location=p.Location,  
  38.                         }  
  39.                         ).ToList();  
  40.   
  41.            StringBuilder sb = new StringBuilder();  
  42.            sb.Clear();  
  43.            sb.Append("{");  
  44.            sb.Append("\"sEcho\": ");  
  45.            sb.Append(sEcho);  
  46.            sb.Append(",");  
  47.            sb.Append("\"iTotalRecords\": ");  
  48.            sb.Append(totalRecord);  
  49.            sb.Append(",");  
  50.            sb.Append("\"iTotalDisplayRecords\": ");  
  51.            sb.Append(totalRecord);  
  52.            sb.Append(",");  
  53.            sb.Append("\"aaData\": ");  
  54.            sb.Append(JsonConvert.SerializeObject(result));  
  55.            sb.Append("}");  
  56.            return sb.ToString();  
  57.        }  
Step 2

Create a View for displaying your data.

  1. <div id="mainContainer">  
  2.        <div class="row">  
  3.            <div class="col-md-6">  
  4.                Patient List  
  5.            </div>  
  6.        </div>  
  7.        <div class="row">  
  8.            <div class="col-md-12">  
  9.               <table id="tbl">  
  10.                   <thead>  
  11.                       <tr>  
  12.                           <th>Name</th>  
  13.                           <th>Email Id</th>  
  14.                           <th>Mobile Number</th>  
  15.                           <th>Location</th>  
  16.                           <th>City</th>  
  17.                           <th>State</th>  
  18.                           <th>Action</th>  
  19.                       </tr>  
  20.                   </thead>  
  21.                   
  22.               </table>  
  23.            </div>  
  24.        </div>  
  25.   
  26.    </div>  
Step 3
 
Add the required libraries and JavaScript code in the end of your View page. 
  1. <link rel="stylesheet" href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.min.css" />  
  2. <script src="/Scripts/Library/jquery-1.7.1.min.js"></script>  
  3. <script src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.min.js"></script>  
  4. <script>  
  5.     var oTable = $( '#tbl').dataTable({  
  6.         "bServerSide": true ,  
  7.         "sAjaxSource": "/Patient/GetPatientList",  
  8.         "sServerMethod": "POST" ,  
  9.         "aoColumns": [  
  10.         { "mData": "PatientName" },  
  11.         { "mData": "EmailId" },  
  12.         { "mData": "MobileNumber" },  
  13.         { "mData": "Location" },  
  14.         { "mData": "CityName" },  
  15.         { "mData": "StateName" },  
  16.         {  
  17.             "mData": function (o) {  
  18.                 return "<a href='/Patient/Edit?Id="+o.Id+"' class='btn btn-info'><i class='fa fa-pencil'></i></a>";  
  19.             }  
  20.         }  
  21.         ],  
  22.     });  
  23. </script>  
Thats it. Our grid is ready.