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 do a step by step walkthrough of the server side implementation, implement multi-column server-side filters and Datatables extensions like scroller.
Introduction
In contrast to older versions, Datatables v1.10+ have a 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 ASP.NET MVC 5 application. For this demo, I have generated a sample dataset of 10,000 records. This can be downloaded from here.
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.
Selected MVC Template and change Authentication method to No Authentication.
Imported the dataset under App_Data folder:
We will add a new item in Model folder and create an ADO.NET Entity Data Model.
The above steps will create the Customers.edmx under Models:
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 the 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.
I have used v1.10.7 for this demo. Make sure you import the 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.
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 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 the below structure:
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. Let's 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. Read more information 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 Datatables 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 the 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 information on scrollCollapse.
"scrollY": 500,
"scrollX": true,
"scrollCollapse": true,
"scroller": {
loadingIndicator: false
},
I have observed scroller extension occasionally doesn't work well with Internet Explorer. This may be disabled if need be. The entire working solution can be downloaded from GitHub Repo.
The solution is also available at echosteg.com.
