Post
Ask Question

jQuery Datatable Server Side Processing With Custom Operations

Introduction

Let us see how to implement the jQuery DataTables with server-side custom filter searching, and IQueryable sorting and pagination in ASP.NET MVC 5 application. It’s always best to use a grid with server side processing for fast loading of grid list in application even with millions of data in a database table.

Background

jQuery DataTables is a plug-in for the Javascript library developed by Allan Jardine. It very flexible and easy to implement the tool, which will give you advanced interaction controls to any HTML tables.

Setup jQuery dataTables

Add the jQuery.DataTables from NuGet package manager and refer to the query.dataTables.min.js, dataTables.bootstrap.js and dataTables.bootstrap.css to your Layout.

Create Model

First, let us create the model for returning the data and displaying the values for the grid.

  1. using System.ComponentModel.DataAnnotations;  
  2. namespace MVCApplication.Models  
  3. {  
  4.     public class EmployeeDetails  
  5.     {  
  6.         public long  Id { get; set; }  
  7.         public string EmpCode { get; set; }  
  8.         public string EmpName { get; set; }  
  9.         public string Gender { get; set; }  
  10.     }  
  11. }  

Create Controller and View

Now, let us create a controller and cshtml view for rendering the jQuery dataTable.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace MVCApplication.Controllers  
  8. {  
  9.     public class EmployeeDataController : Controller  
  10.     {  
  11.   
  12.        [HttpGet]  
  13.         public ActionResult EmployeeDataList()  
  14.         {  
  15.             try  
  16.             {  
  17.                 return View();  
  18.             }  
  19.             catch (Exception ex)  
  20.             {  
  21.                 throw ex;  
  22.             }  
  23.         }  
  24.     }  
  25. }  

I have created the ActionResult method EmployeeDataList inside the controller with HTTP get attribute. Now let’s create the EmployeeDataList.cstml view for the action result.

  1. @{  
  2.     ViewBag.Title = "Employee data";  
  3.     Layout = "~/Views/Shared/_Layout.cshtml";  
  4. }  
  5.   
  6. @section Styles{  
  7.     <style type="text/css">  
  8.         div.dataTables_filter {  
  9.             display: none !important;  
  10.         }  
  11.   
  12.         table.dataTable tbody td {  
  13.             vertical-align: middle !important;  
  14.             padding: 5px !important;  
  15.         }  
  16.     </style>  
  17. }  
  18.     <div class="row">  
  19.         <div class="col-md-12 col-sm-12 col-xs-12">  
  20.             <div class="table-responsive" style="background-color:#fff;font-size:13px;padding-top:5px;">  
  21.         <table id="Employeegrid" class="display dataTable cell-border table-bordered" style="width: 100%;" cellspacing="0">  
  22.                         <thead>  
  23.                             <tr>  
  24.                                 <th data-sortable="false">Id</th>  
  25.                                 <th>Emp Code</th>  
  26.                                 <th>Emp Name</th>  
  27.                                 <th>Gender</th>  
  28.                             </tr>  
  29.                         </thead>  
  30.                         <thead class="filters" style="text-transform:uppercase;">  
  31.                             <tr>  
  32.                                 <td style="padding:5px;">Emp Code</td>  
  33.                                 <td style="padding:5px;">Emp Name</td>  
  34.                                 <td style="padding:5px;">Gender</td>  
  35.                             </tr>  
  36.                         </thead>  
  37.                         <tbody style="text-transform:uppercase;"></tbody>  
  38.                     </table>  
  39.   
  40.                 </div>  
  41.             </div>  
  42.         </div>  

The first <thead></thead> is for column header and the second is for the custom filter on column level. The next step is to populate the data on the grid using the Ajax call to server side. The below code snippet will do it.

  1. @section Scripts  
  2. {  
  3.     <script type="text/javascript">  
  4.   
  5.         $(document).ready(function () {  
  6.              $('#Employeegrid).dataTable({  
  7.                  "paging": true,  
  8.                  "ordering": true,  
  9.                  "filter": true,  
  10.                  "destroy": true,  
  11.                  "orderMulti": false,  
  12.                  "serverSide": true,  
  13.                  "Processing": true,  
  14.                  "columnDefs": [  
  15.                      { "width": "5%", "targets": [0] }  
  16.                  ],  
  17.                 "ajax":  
  18.                     {  
  19.                         "url": "/EmployeeData/GetEmployeeDataList",  
  20.                         "type": "POST",  
  21.                         "dataType": "JSON"  
  22.                     },  
  23.                 "aoColumns": [  
  24.                     {  
  25.                         "mDataProp": "Id",  
  26.                         "visible": false  
  27.                     },  
  28.                     {"mDataProp": "EmpCode"},  
  29.                     {"mDataProp": "EmpName"},  
  30.                     {"mDataProp": "Gender"}  
  31.                 ]  
  32.             });  
  33.   
  34.                 // Setup - add a text input to each filter column  
  35.                 $('#Employeegrid.filters td').each(function () {  
  36.   
  37.                     var title = $('# Employeegrid thead td').eq($(this).index()).text();  
  38.                     if (title) {  
  39.                         $(this).html('<input type="text" class="form-control" />');  
  40.                     }  
  41.                 });  
  42.   
  43.                 // DataTable  
  44.             var table = $('#Employeegrid').DataTable(  
  45.                 {  
  46.                     "bFilter": true,  
  47.                     "order": []  
  48.                 });  
  49.   
  50.             // function to clear the previous timer and set new timer for filter column keyup event to execute.  
  51.             var delay = (function () {  
  52.                 var timer = 0;  
  53.                 return function (callback, ms) {  
  54.                     clearTimeout(timer);  
  55.                     timer = setTimeout(callback, ms);  
  56.                 };  
  57.             })();  
  58.   
  59.   
  60.                 // script for column filter Keyup event and here I have created half second(500) delay using the timer. You can increase depends on your requirement.  
  61.                 table.columns().eq(0).each(function (colIdx) {  
  62.                     $('input', $('.filters td')[colIdx]).bind('keyup', function () {  
  63.                             var coltext = this.value; // typed value in the search column  
  64.                             var colindex = colIdx; // column index  
  65.                             delay(function () {  
  66.                                 table  
  67.                                     .column(colindex)  
  68.                                     .search(coltext)  
  69.                                     .draw();  
  70.                             }, 500);  
  71.                     });  
  72.             });  
  73.         });  
  74.     </script>  
  75. }  

Next, let create a post ActionResult method to get the data from database and populate in the jQuery dataTable.

  1. public ActionResult GetEmployeeDataList()  
  2.         {  
  3.             try  
  4.             {  
  5.                 string draw = Request.Form.GetValues("draw")[0];  
  6.                 string order = Request.Form.GetValues("order[0][column]")[0];  
  7.                 string orderDir = Request.Form.GetValues("order[0][dir]")[0];  
  8.                 int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);  
  9.                 int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);  
  10.   
  11.                 //Custom column search fields  
  12.                 string EmpCode = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault();  
  13.                 string EmpName = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault();  
  14.                 string Gender = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault();  
  15.   
  16.                 IQueryable<Employee> employee = DbContext.Employee;  
  17.   
  18.   //Getting the total count of Employee to display on the grid pagination.    
  19.   long TotalRecordsCount = employee.count();  
  20.   
  21.           /* Here I have done the filter on And condition (You can change to OR condition if required). Validating the search value is not null or empty and not containing only space. */  
  22.  
  23.                 #region filters  
  24.   
  25.                 if (!string.IsNullOrEmpty(EmpCode) && !string.IsNullOrWhiteSpace(EmpCode))  
  26.                 {  
  27.                     employee = employee.Where(x => x.Code != null && x. Code.ToLower().Contains(EmpCode.ToLower()));  
  28.                 }  
  29.                 if (!string.IsNullOrEmpty(EmpName) && !string.IsNullOrWhiteSpace(EmpName))  
  30.                 {  
  31.                     employee = employee.Where(x => x.Name != null && x.Name.ToLower().Contains(EmpName.ToLower()));  
  32.                 }  
  33.                 if (!string.IsNullOrEmpty(Gender) && !string.IsNullOrWhiteSpace(Gender))  
  34.                 {  
  35.                     employee = employee.Where(x => x.Gender != null && x.Gender.ToLower().Contains(Gender.ToLower()));  
  36.    }  
  37. #endregion  
  38.   
  39.   
  40.         //count of record after filter   
  41. long FilteredRecordCount = employee.Count();  
  42.   
  43.   
  44. /*Here we are allowing only one sorting at time. orderDir will hold asc or desc for sorting the column. */   
  45. #region Sorting  
  46.   
  47. // Sorting     
  48.                 switch (order)  
  49.                 {  
  50.                     case "1":  
  51.                         employee = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? employee.OrderByDescending(p => p.Code) : employee.OrderBy(p => p.Code);  
  52.                         break;  
  53.                     case "2":  
  54.                         employee = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? employee.OrderByDescending(p => p.Name) : employee.OrderBy(p => p.Name);  
  55.                         break;  
  56.                     case "3":  
  57.                         employee = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? employee.OrderByDescending(p => p.Gender) : employee.OrderBy(p => p.Gender);  
  58.                         break;  
  59.                     default:  
  60.                        employee = employee.OrderByDescending(p => p.Id);  
  61.                         break;  
  62.  
  63. #endregion    
  64. /* Apply pagination to employee iqueryable, startRec will hold the record number from which we need to display and pageSize will hold the number of records to display. Then assign the values to EmployeeDetails model.  */  
  65.                 var listemployee = employee.Skip(startRec).Take(pageSize).ToList()  
  66.                     .Select(d => new EmployeeDetails()  
  67.                     {  
  68.                         Id = d.Id,  
  69.                         EmpCode = d.Code,  
  70.                    EmpName = d.Name,  
  71.                         Gender = d.Gender  
  72.                     }).ToList();  
  73.   
  74.          // To avoid object reference error incase of listemployee being null.    
  75.                 if (listemployee == null)  
  76.                     listemployee = new List<EmployeeDetails>();  
  77.   
  78.   
  79.                 return this.Json(new  
  80.                 {  
  81.                     draw = Convert.ToInt32(draw),  
  82.                     recordsTotal = TotalRecordsCount,  
  83.                     recordsFiltered = FilteredRecordCount,  
  84.                     data = listemployee  
  85.                 }, JsonRequestBehavior.AllowGet);  
  86.             }  
  87.             catch (Exception ex)  
  88.             {  
  89.                 throw;  
  90.             }  
  91.         }  

So, we have to finished all the steps to build the server side processing with jquery DataTables in MVC application. I hope this will help you out.


Similar Articles