jQuery Datatables 1.10+ & ASP.NET MVC 5 Server Side Integration

jQuery Datatables MVC

Datatables is a highly flexible plugin-in for the jQuery Javascript library. Datatables add advance interaction controls like pagination, search and multi-column ordering to any HTML table. This article explains how to use the Datables v1.10+ (new parameters) with ASP.NET MVC 5 application. We will step by step walkthrough the server side implementation, implement multi-column server-side filters and Datatables extensions like scroller.

Download Solution

Introduction

In contrast to older versions, Datatables v1.10+ have new set of parameters sent when making a request to the server. In this article we will work with these new set of parameters to implement the server side integration of Datatables v1.10+ with ASPT.NET MVC 5 application. For this demo, I have generated a sample dataset of 10,000 records. This can be downloaded from here: Download Dataset

We will be looking at the following implementations;

  • Create an ASP.NET MVC 5 application with default template in Visual Studios 2013
  • Import the generated dataset with 10,000 records
  • Create Entity Data Model for the imported dataset using Entity Framework 6.x
  • Format view for the demo table
  • JavaScript function to make AJAX calls to query the server
  • View model class to accept the server parameters
  • Controller to accept the sent parameters and return Json results.
  • Vertical and horizontal scroll using Scroller extension of the datatables
  • Multi-column server-side search

Create a solution and the dataset

Create a new project in Visual Studios 2013 and select ASP.NET Web Application create MVC app

Selected MVC Template and change Authentication method to No Authentication change authentication

Imported the dataset under App_Data folder
dataset

We will add a new item in Model folder and create an ADO.NET Entity Data Model Entity Framework 6

Entity Framework 6

Entity Framework 6

Entity Framework 6

Above steps will create the Customers.edmx under Models
Entity Framework 6

The View

I have trimmed down _layout.cshtml file to make things simple and retain the elements relevant for this demo.

About.cshtml and Contact.cshtml have been deleted since we won’t be needing same for the demo app. The index.cshtml has been edited to include a table element with id of ‘datatab’. The table headers have been defined from the model IEnumerable.

The Scripts

Download the Datatables package from here: Download Datatables
I have used v1.10.7 for this demo. Make sure you import following files to your scripts (.js) and content (.css) folders

  • jquery.dataTables.min.js
  • dataTables.scroller.min.js
  • jquery.dataTables.min.css
  • dataTables.scroller.min.css
We will now create an index.js file in the script folder. We will set serverSide to true to make server side call. An Ajax call will be made to DataHandler JsonResult method of Home Controller. We will also configure the columns to display and set an ascending order or the Name column, which has an index of 0. I have bundled the imported scripts in the bundleConfig.cs file.
Make sure you import the images for the datatables from the package. I have created a new folder named same. images folder

The View Model Class

I have used the view model class from Marien Monnier’s demo. DTParameters class defines the parameters sent by Datatables AJAX request. DTResult class defined the result set which will be returned to the view. SortBy is a custom property to sort the data.

The Controller and multi-column search

The HomeController will have have an ActionResult which returns the Index View. It is necessary to implement the server-side logic that will provide the data to the Datatables and apply the search filters. Since we have declared the Ajax URL as /Home/DataHandler, we will create a JsonResult method named DataHandler. We will start with below structure;

Home Controller

The parameters sent by Datatables are encapsulated in the DTParameters class. We will first create a ResultSet class to encapsulate the search and sort logic. FilterResult accepts three parameters, i.e. search, dtResult and columnFilters. We pass global search box value from Datatables to the search parameter. DtResult is the unfiltered dataset. columnFilters is the string list of values applied to column specific search boxes. Lets sneak back to the index.js file we created for a moment.

oTable.columns().every(function () {
    var that = this;
    $('input', this.footer()).on('keyup change', function () {
        that
            .search(this.value)
            .draw();
    });
});                                     
Above, we use the columns() method of Datatables to insert a textbox for each column and attached a keyup / change event which sends the typed search text to the controller. The value is sent as an array defining all columns in the datatables. More info on sent parameters

Coming back to the FilterResult, we use a LINQ query to filter the result set. Global search will filter the records based on match with any column's text. Column specific search boxes will apply the filters on the corresponding columns. Both global and column search can function correspondingly. GetResult will sort the filtered dataset and convert same to a list. Count returns the count of filtered dataset. We will now modify the DataHandler method as below: Once we have the filtered dataset in data list, we return the data to the Datables in JSON format. Finally, looking back at our index.js; we have enabled the scroller by defining an object for scroller. Latter can be just set true, however I have disabled the loading indicator as same is sorted by the processing property for server-side. scrollY defines the vertical scrolling and scrollX is set to true to enable the horizontal scroll as well since we have large number of columns in the table. scrollCollapse allows the table to reduce in height when a limited number of rows are shown. More info on scrollCollapse

"scrollY": 500,
"scrollX": true,
"scrollCollapse": true,
"scroller": {
    loadingIndicator: false
},
                                        
I have observed scroller extension occasionally doesn't work well with IE. This may be disabled if need be. The entire working solution can be downloaded

The solution is also available on Github

Share this Post: