Implement custom multicolumn server-side filtering in jQuery dataTables

Step-1: Create New Project.

Go to File > New > Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Basic > Select view engine Razor > OK

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.
Here I have added a database for store some location information in our database for show in the google map.

Step-3: Create a table.

Here I will create 1 table (as below) for store data.
Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used 1 table as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >


Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Create a Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller "HomeController"

Step-6: Add new action into the controller to get the view where we will implement jQuery DataTable with server-side paging and sorting.

Here I have added "Index" Action into "Home" Controller. Please write this following code

public ActionResult Index()
{
    return View();
}

Step-7: Add view for the action (here "Index") & design.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select View Engine (Razor) > Add.
Complete HTML code 

@{
    ViewBag.Title = "Index";
}
 
<h2>Part 3: Custom multicolumn server-side filtering in jQuery DataTables.</h2>
<div style="width:90%; margin:0 auto">
    @* Search Area *@
    <div style="background-color:#f5f5f5; padding:20px">
        <h2>Search Panel</h2>
        <table>
            <tbody>
                <tr>
                    <td>Employee Name</td>
                    <td><input type="text" id="txtEmployeeName" /></td>
                    <td>Country</td>
                    <td>
                        <select id="ddCountry" style="width:200px">
                            <option value="">All Country</option>
                            <option value="USA">USA</option>
                            <option value="Poland">Poland</option>
                            <option value="Finland">Finland</option>
                        </select>
                        @* Here I have made this select box with some static data for make this example simpler. 
                        You can fill it from database data *@
                    </td>
                    <td>
                        <input type="button" value="Search" id="btnSearch" />
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    @* jQuery DataTables *@
    <div>
        <table id="myTable" class="table table-responsive table-striped">
            <thead>
                <tr>
                    <th>Employee Name</th>
                    <th>Company</th>
                    <th>Phone</th>
                    <th>Country</th>
                    <th>City</th>
                    <th>Postal Code</th>
                </tr>
            </thead>
        </table>
    </div>
</div>
 
@* jQuery DataTables css *@
<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />
@* jQuery *@
@section Scripts{
    <script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script> 
    <script>
        $(document).ready(function () {
 
            //jQuery DataTables initialization 
            $('#myTable').DataTable({
                "processing": true, // for show processing bar
                "serverSide": true, // for process on server side
                "orderMulti": false, // for disable multi column order
                "dom": '<"top"i>rt<"bottom"lp><"clear">', // for hide default global search box // little confusion? don't worry I explained in the tutorial website
                "ajax": {
                    "url": "/home/LoadData",
                    "type": "POST",
                    "datatype": "json"
                },
                "columns" : [
                        { "data": "ContactName", "name": "ContactName", "autoWidth": true }, //index 0
                        { "data": "CompanyName", "name": "CompanyName", "autoWidth": true }, //index 1
                        { "data": "Phone", "name": "Phone", "autoWidth": true },             //index 2
                        { "data": "Country", "name": "Country", "autoWidth": true },         //index 3
                        { "data": "City", "name": "City", "autoWidth": true },               //index 4
                        { "data": "PostalCode", "name": "PostalCode", "autoWidth": true },   //index 5
                    ]
            });
 
            //Apply Custom search on jQuery DataTables here
            oTable = $('#myTable').DataTable();
            $('#btnSearch').click(function () {
                //Apply search for Employee Name // DataTable column index 0
                oTable.columns(0).search($('#txtEmployeeName').val().trim());
                //Apply search for Country // DataTable column index 3
                oTable.columns(3).search($('#ddCountry').val().trim());
                //hit search on server
                oTable.draw();
            });
        });
    </script>
}

If you already implemented Part 2 of this series, you can see here I have just added 1 extra line in the jQuey DataTables initialization code.

"dom": '<"top"i>rt<"bottom"lp><"clear">'

I have added this line for hide default global search box.

About "dom"

This initialisation variable allows you to specify exactly where in the DOM you want DataTables to inject the various controls it adds to the page.

Confusion ? Don't worry. Please check the below image

Step-8: Add reference of System.Linq.Dynamic

Skip this step if you have already added reference of System.Linq.Dynamic (we had added this on part 2)

Confusion ? Don't worry. Please check the below image

Here I have added System.Linq.Dynamic reference from NuGet packages


Go to Solution Explorer > Right click on References > Manage NuGet packages > Search with "System.Linq.Dynamic" > Install.

Step-9: Add another action (here "LoadData") for fetch data from the database and implement logic for server-side paging, sorting and filtering.

[HttpPost]
        public ActionResult LoadData()
        {
            //jQuery DataTables Param
            var draw = Request.Form.GetValues("draw").FirstOrDefault();
            //Find paging info
            var start = Request.Form.GetValues("start").FirstOrDefault();
            var length = Request.Form.GetValues("length").FirstOrDefault();
            //Find order columns info
            var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() 
                                    + "][name]").FirstOrDefault();
            var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
            //find search columns info
            var contactName = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault(); 
            var country = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();
 
            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt16(start) : 0;
            int recordsTotal = 0;
 
 
            using (MyDatabaseEntities dc = new MyDatabaseEntities())
            {
                // dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key
                var v = (from a in dc.Customers select a);
 
                //SEARCHING...
                if (!string.IsNullOrEmpty(contactName))
                {
                    v = v.Where(a=>a.ContactName.Contains(contactName));
                }
                if (!string.IsNullOrEmpty(country))
                {
                    v = v.Where(a => a.Country == country);
                }
                //SORTING...  (For sorting we need to add a reference System.Linq.Dynamic)
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
                {
                    v = v.OrderBy(sortColumn + " " + sortColumnDir);
                }
 
                recordsTotal = v.Count();
                var data = v.Skip(skip).Take(pageSize).ToList();
                return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, 
                    JsonRequestBehavior.AllowGet);
 
            }
        }

If you already implemented part 2 of this series, you can see I have just added few new lines [line no: 13-14 and line no: 27-34]

 //find search columns info
var contactName = Request.Form.GetValues("columns[0][search][value]").FirstOrDefault(); 
var country = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault();

AND

//SEARCHING...
if (!string.IsNullOrEmpty(contactName))
{
    v = v.Where(a=>a.ContactName.Contains(contactName));
}
if (!string.IsNullOrEmpty(country))
{
    v = v.Where(a => a.Country == country);
}

Step-10: Run Application.