Implement custom multicolumn server-side filtering in jQuery dataTables



Introduction

In my previous article, I have explained jQuery Datatable server-side pagination and sorting. In this example, We will implement custom multicolumn server-side filtering in jQuery dataTables.

By default jQuery Datatable provides a global search box for filter records and that is applied on the whole table. But here in this example I will show how we can remove the default search box and add our own custom search area with input fields(input, select etc) for implement custom multicolumn server-side filtering in jQuery dataTables. In this way, we can also place our search fields anywhere in our page with no restriction like jQuery DataTables default search box.

Here we will see followings with ASP.NET MVC as server side...
  1. Part 1: Implement jQuery Datatable (Basic initialization) in ASP.NET MVC application.
  2. Part 2: jQuery Datatable server side pagination and sorting in ASP.NET MVC
  3. Part 3: Implement custom multicolumn server-side filtering in jQuery dataTables
  4. Next article coming soon...

Just follow the following steps in order implement custom multicolumn server-side filtering in jQuery dataTables

[Note: If you already visit my previous article and implemented the same, you can start from step 6]

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
Implement custom multicolumn server-side filtering in jQuery dataTables

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.


Implement custom multicolumn server-side filtering in jQuery dataTables

Hello ! My name is Sourav Mondal. I am a software developer working in Microsoft .NET technologies since 2010.

I like to share my working experience, research and knowledge through my site.

I love developing applications in Microsoft Technologies including Asp.Net webforms, mvc, winforms, c#.net, sql server, entity framework, Ajax, Jquery, web api, web service and more.