Post
Ask Question

Server-Side Processing With Custom Range Filtering Using MVC And Entity Framework

Introduction

 
In this session, we will learn how to implement server-side processing with custom range filtering in jQuery DataTables. I am going to show you a DataTable's server-side paging, sorting, and filtering in ASP.NET MVC application. Server-side means using C# code behind the file in the Controller section. We can erase the global search box of jQuery DataTable and use our custom filter area with input fields like textbox and dropdown for implementing custom multicolumn server-side filtering in jQuery DataTables. By using that way, we can implement our own filter areas anywhere on our page, to work with jQuery DataTables.
 

Description

 
DataTable is a most powerful and easy-to-use jQuery plugin for displaying tabular data with features like pagination, searching, state saving, and multi-column sorting with data type detection and lots more, with ZERO or minimal configuration.
 
The prerequisites of this article include knowledge of the following technologies.
  1. ASP.NET MVC
  2. HTML
  3. JavaScript
  4. AJAX
  5. CSS
  6. Bootstrap
  7. C# Programming
  8. C# LINQ
  9. jQuery
Note
Before going through the session, I suggest you first visit my previous articles with a back-end section.
Steps to be followed.
 
Step 1
 
Add a new action into the Controller to get the View where we will implement the jQuery DataTable with server-side paging and sorting.
 
Code
  1. public ActionResult filter()  
  2.        {  
  3.            return View();  
  4.        }  

Step 2

Add a View for the action (here "filter") and design.
 
Code
  1. @{  
  2.     ViewBag.Title = "Satyaprakash - Filter Records";  
  3. }  
  4.   
  5. <h2 style="color: blue">Satyaprakash-Server Side Processing With Custom Range Filtering</h2>  
  6.   
  7. <style>  
  8.     table {  
  9.         font-family: arial, sans-serif;  
  10.         border-collapse: collapse;  
  11.         width: 100%;  
  12.     }  
  13.   
  14.     td, th {  
  15.         border: 1px solid #dddddd;  
  16.         text-align: left;  
  17.         padding: 8px;  
  18.     }  
  19.   
  20.     tr:nth-child(even) {  
  21.         background-color: #dddddd;  
  22.     }  
  23.   
  24.     .custom-loader-color {  
  25.         color: #fff !important;  
  26.         font-size: 40px !important;  
  27.     }  
  28.   
  29.     .custom-loader-background {  
  30.         background-color: #f60 !important;  
  31.     }  
  32.   
  33.     .custom-middle-align {  
  34.         vertical-align: middle !important;  
  35.     }  
  36. </style>  
  37.   
  38. <div style="width:90%; margin:0 auto">  
  39.     <div style="background-color:#f5f5f5; padding:20px">  
  40.         <h2 style="color: blue">Filter Records</h2>  
  41.         <table>  
  42.             <tbody>  
  43.                 <tr>  
  44.                     <td style="color: blue">City</td>  
  45.                     <td><input type="text" class="form-control" id="txtCity" /></td>  
  46.                     <td style="color: blue">State</td>  
  47.                     <td>  
  48.                         <select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">  
  49.                             <option value="">All States</option>  
  50.                             <option value="Karnataka">Karnataka</option>  
  51.                             <option value="Andhra Pradesh">Andhra Pradesh</option>  
  52.                             <option value="Georgia">Georgia</option>  
  53.                             <option value="Uttar Pradesh">Uttar Pradesh</option>  
  54.                             <option value="Odisha">Odisha</option>  
  55.                         </select>  
  56.                     </td>  
  57.                     <td>  
  58.                         <input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />  
  59.                     </td>  
  60.                 </tr>  
  61.             </tbody>  
  62.         </table>  
  63.     </div>  
  64.     @* jQuery DataTables *@  
  65.     <div style="width:90%; margin:0 auto;">  
  66.         <table id="myTable" class="table table-responsive table-striped">  
  67.             <thead>  
  68.                 <tr>  
  69.                     <th style="background-color: Yellow;color: blue">First Name</th>  
  70.                     <th style="background-color: Yellow;color: blue">Last Name</th>  
  71.                     <th style="background-color: Yellow;color: blue">Age</th>  
  72.                     <th style="background-color: Yellow;color: blue">Address</th>  
  73.                     <th style="background-color: Yellow;color: blue">City</th>  
  74.                     <th style="background-color: Yellow;color: blue">State</th>  
  75.                 </tr>  
  76.             </thead>  
  77.         </table>  
  78.     </div>  
  79. </div>  
  80.   
  81. @* Load bootstrap datatable css *@  
  82. <link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />  
  83. <link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" />  
  84.   
  85. @* Load normal datatable css *@  
  86. @*<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />*@  
  87.   
  88. @* Load bootstrap datatable js *@  
  89. @section Scripts{  
  90.     <script src="//code.jquery.com/jquery-3.3.1.js"></script>  
  91.     <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>  
  92.     <script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>  
  93.   
  94.     @* Load normal datatable js *@  
  95.     @*<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>*@  
  96.   
  97.     <script>  
  98.         $(document).ready(function () {  
  99.   
  100.             //jQuery DataTables initialization  
  101.             $('#myTable').DataTable({  
  102.                 "language":  
  103.                 {  
  104.                     "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"  
  105.                 },  
  106.                 "processing": true,   
  107.                 "serverSide": true,   
  108.                 "orderMulti": false,   
  109.                 "dom": '<"top"i>rt<"bottom"lp><"clear">',  
  110.                 "ajax": {  
  111.                     "url": "/home/FilterData",  
  112.                     "type": "POST",  
  113.                     "datatype": "json"  
  114.                 },  
  115.                 "columns": [  
  116.                     { "data": "FirstName", "name": "FirstName", "autoWidth": true },   
  117.                     { "data": "LastName", "name": "LastName", "autoWidth": true },   
  118.                     { "data": "Age", "name": "Age", "autoWidth": true },   
  119.                     { "data": "Address", "name": "Address", "autoWidth": true },   
  120.                     { "data": "City", "name": "City", "autoWidth": true },  
  121.                     { "data": "State", "name": "State", "autoWidth": true }   
  122.                 ]  
  123.             });  
  124.   
  125.             oTable = $('#myTable').DataTable();  
  126.             $('#btnFilter').click(function () {  
  127.                 oTable.columns(4).search($('#txtCity').val().trim());  
  128.                 oTable.columns(5).search($('#ddState').val().trim());  
  129.                 oTable.draw();  
  130.             });  
  131.         });  
  132.     </script>  
  133. }  
Code description
 
Here, I have added a textbox for City and a dropdown for State to filter the records. I used some static records in the dropdown for basic understanding and in my next session, I will fill the dropdown from the database using Entity Framework.
  1. <div style="background-color:#f5f5f5; padding:20px">  
  2.        <h2 style="color: blue">Filter Records</h2>  
  3.        <table>  
  4.            <tbody>  
  5.                <tr>  
  6.                    <td style="color: blue">City</td>  
  7.                    <td><input type="text" class="form-control" id="txtCity" /></td>  
  8.                    <td style="color: blue">State</td>  
  9.                    <td>  
  10.                        <select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">  
  11.                            <option value="">All States</option>  
  12.                            <option value="Karnataka">Karnataka</option>  
  13.                            <option value="Andhra Pradesh">Andhra Pradesh</option>  
  14.                            <option value="Georgia">Georgia</option>  
  15.                            <option value="Uttar Pradesh">Uttar Pradesh</option>  
  16.                            <option value="Odisha">Odisha</option>  
  17.                        </select>  
  18.                    </td>  
  19.                    <td>  
  20.                        <input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />  
  21.                    </td>  
  22.                </tr>  
  23.            </tbody>  
  24.        </table>  
  25.    </div>  
I have updated the code to implement custom multicolumn server-side filtering in jQuery DataTables by removing the default global search box. This initialization variable allows you to specify where in the DOM you want DataTables to introduce the various controls it composes to the page. 
  • <"top"i> means it is showing the info of entries. 
  • rt means it is showing the progress bar with loading records in DataTable. 
  • <"bottom"lp> means it is showing the length of records and also, the paging in the page.
  • <"clear"> means it clears the controls or any data inside div element. 
  1. "dom": '<"top"i>rt<"bottom"lp><"clear">'  
The following piece of code will enable the data loading from server-side. The path "/home/FilterData" is the function that will be returning data from server side. The columns here are the exact names of the properties that we have created in the table and uploaded using the Entity Data Model file. Here, we can get the index number of the table's columns.
  1. $('#myTable').DataTable({  
  2.                 "language":  
  3.                 {  
  4.                     "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"  
  5.                 },  
  6.                 "processing": true,  
  7.                 "serverSide": true,  
  8.                 "orderMulti": false,  
  9.                 "dom": '<"top"i>rt<"bottom"lp><"clear">',   
  10.                 "ajax": {  
  11.                     "url": "/home/FilterData",  
  12.                     "type": "POST",  
  13.                     "datatype": "json"  
  14.                 },  
  15.                 "columns": [  
  16.                     { "data": "FirstName", "name": "FirstName", "autoWidth": true }, //index 0  
  17.                     { "data": "LastName", "name": "LastName", "autoWidth": true }, //index 1  
  18.                     { "data": "Age", "name": "Age", "autoWidth": true }, //index 2  
  19.                     { "data": "Address", "name": "Address", "autoWidth": true }, //index 3  
  20.                     { "data": "City", "name": "City", "autoWidth": true }, //index 4  
  21.                     { "data": "State", "name": "State", "autoWidth": true } //index 5  
  22.                 ]  
  23.             });  
The following piece of code is used to apply custom search on jQuery DataTables. I have applied search for the city name using DataTable column index 4 and search for state name using DataTable column index 5.
  1. oTable = $('#myTable').DataTable();  
  2.            $('#btnFilter').click(function () {          
  3.                oTable.columns(4).search($('#txtCity').val().trim());              
  4.                oTable.columns(5).search($('#ddState').val().trim());                  
  5.               oTable.draw();  
  6.            });  
  7.        });  
Step 3
 
Add another action (here "FilterData") for fetching the data from the database and implementing the logic for server-side paging and sorting.
 
Code
  1. [HttpPost]  
  2.         public ActionResult FilterData()  
  3.         {  
  4.             // Initialization.     
  5.             JsonResult result = new JsonResult();  
  6.             try  
  7.             {      
  8.                 var draw = Request.Form.GetValues("draw").FirstOrDefault();    
  9.                 var start = Request.Form.GetValues("start").FirstOrDefault();  
  10.                 var length = Request.Form.GetValues("length").FirstOrDefault();     
  11.                 var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault()  
  12.                                         + "][name]").FirstOrDefault();  
  13.                 var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();            
  14.                 var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();  
  15.                 var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();  
  16.                 int pageSize = length != null ? Convert.ToInt32(length) : 0;  
  17.                 int skip = start != null ? Convert.ToInt16(start) : 0;  
  18.                 int recordsTotal = 0;  
  19.                 using (SatyaDBEntities dc = new SatyaDBEntities())  
  20.                 {         
  21.                     var v = (from a in dc.employees select a);            
  22.                     if (!string.IsNullOrEmpty(city))  
  23.                     {  
  24.                         v = v.Where(a => a.City.Contains(city));  
  25.                     }  
  26.                     if (!string.IsNullOrEmpty(state))  
  27.                     {  
  28.                         v = v.Where(a => a.State == state);  
  29.                     }                 
  30.                     if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))  
  31.                     {  
  32.                         v = v.OrderBy(sortColumn + " " + sortColumnDir);  
  33.                     }  
  34.                     recordsTotal = v.Count();  
  35.                     var data = v.Skip(skip).Take(pageSize).ToList();  
  36.                     return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data },  
  37.                         JsonRequestBehavior.AllowGet);  
  38.                 }  
  39.             }  
  40.             catch (Exception ex)  
  41.             {  
  42.                 // Info     
  43.                 Console.Write(ex);  
  44.             }  
  45.             // Return info.     
  46.             return result;  
  47.         }  

Code description

I have described the code using the comment line in every line of code. It will be easy for a quick understanding of the code flow. In this piece of code, which is based on searching, sorting, and pagination information sent from the DataTable plugin, the following has been done: The data is being loaded first. It is being churned out based on the search criteria. Data is then sorted by a provided column in a provided order. Lastly, it is paginated and returned.
 
I have declared two variables which contain the informaion of two columns for filtering records with their index values as I have described in the View section.
  1. var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();  
  2. var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();  
The following is the piece of code for filtering records using city and state columns.
  1. if (!string.IsNullOrEmpty(city))  
  2.                     {  
  3.                         v = v.Where(a => a.City.Contains(city));  
  4.                     }  
  5.                     if (!string.IsNullOrEmpty(state))  
  6.                     {  
  7.                         v = v.Where(a => a.State == state);  
  8.                     }  
For sorting, we need to add a reference of System.Linq.Dynamic. 
  1. if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))  
  2.                    {  
  3.                        v = v.OrderBy(sortColumn + " " + sortColumnDir);  
  4.                    }  

OUTPUT

During the initial load, the processing loader will look like below.
 
Server Side Processing With Custom Range Filtering Using MVC And Entity Framework
Filter records using state dropdown, as shown below.
 
Server Side Processing With Custom Range Filtering Using MVC And Entity Framework
Filter records using city textbox, as shown below. 
 
Server Side Processing With Custom Range Filtering Using MVC And Entity Framework
Filter records using both, City and State, as shown below.
 
Server Side Processing With Custom Range Filtering Using MVC And Entity Framework 

Summary


In this write-up, we have learned how to -
  • Filter records using custom multicolumn server-side features.
  • Remove default global search box of jQuery DataTable by using initialisation variable in DOM.
  • Get record's length, sorting and pagination information with the DataTable plugin.
  • Get Server-side integration of DataTable plugin with ASP.NET MVC 5. 


Similar Articles