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 datatable
s 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 Datatable
s 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.