JQuery DataTables Paging, Sorting and Searching with ASP.NET Web API

In my previous tutorial Using JQuery DataTables with ASP.NET Web API, I covered the basics of JQuery DataTables plugin. Sometimes, it makes sense to go beyond the basics to make sure the data you are displaying is not only loading fast but also easy for the user to read and understand. If you have thousands of rows of data in database table you can’t simply display all of them at once. You have to avoid information overload by loading the data in chunks. You have to allow users to control how much they want to see and in which order they want to see it. DataTables plugin has built in support for client to server side searching, sorting and pagination using AJAX calls. We can search and filter to zero in on what's relevant. We can change the order, allowing for easy comparison. In this tutorial I will show you how you can enable searching, sorting and paging features of the plugin and use ASP.NET Web API on the server side to support these features.

If you are coming to this tutorial directly, I will recommend you to read my previous tutorial Using JQuery DataTables with ASP.NET Web API so that you are equipped with a working example of Web API controller and DataTables plugin. This tutorial will continue where we left in the previous tutorial.

Understanding DataTables request parameters

When DataTables plugin operates in server-side processing mode, it sends certain parameters to the server indicating what data it needs. It sends information about the page number, page sorting order and searched keywords along with some other information. More detail about these parameters can be found in DataTables manual. All these parameters can be seen easily by using Chrome developer tools window.

We need to make sure all these parameters are received by ASP.NET Web API method so that the Web API method can use this information to query database accordingly. According to DataTables manual the three top level parameters are following:

Let’s create a DataTableRequest class in the Models/DataTables folder and declared following three properties in it. You may be wondering why the properties names are not matching exactly with the above parameters. This is because I am following C# naming conventions by making the first letter of the property name capitalize. Later in this tutorial, I will show you how you can create a Model Binder class to bind DataTables parameters with the DataTableRequest class properties.

Other than the three top level parameters, DataTables also have parameters which send sorting related information to the server side. These parameters are column and dir and they are grouped together in a property called order.

Create a DataTableOrder class in the Models/DataTables folder and declared following two properties in it.

DataTables also have columns related parameters. These parameters send information about the individual columns displayed in the grid.

Create a DataTableColumn class in the Models/DataTables folder and declared following properties in it. Notice the DataTableSearch instance is also declared in the class as a member which means that you can also search on a particular column instead of entire table.

Custom Model Binder Class for DataTables request parameters

When DataTables plugin will send different parameters to server, we want those parameters to automatically map with the DataTableRequest object properties but as I mentioned before that the first letter of each property of DataTableRequest object is capitalized to follow the C# naming conventions so we need to create a custom model binder class to solve this problem. With a custom model binder, we can get access to things like the HTTP request, the action description, and the raw values from the route data. The custom binding class needs to implement IModelBinder interface and needs to define BindModel method. Here is the definition of our custom model binder class that is mapping all the DataTables request parameters to DataTableRequest object.

Your solution explorer should look like the following structure after all the classes are created.

Press F5 and you will see the following output in the browser window

DataTables Searching and Filtering

We spend substantial amount of time in creating many different classes in this tutorial, it’s time to make use of all those classes and properties to add different features to DataTables plugin. First of all we will add searching capability to allow user to search and filter products in our database. To enable searching within the plugin we need to set the value of searching option to true. DataTables plugin will automatically render a search text field on top of the DataTables.

Whatever user will type in this text field will automatically be received on server side in the Value property of DataTableSearch class we implemented above. All we need to do is to filter the products table in database using the Value property.

It is important to note that how we're using RecordsFiltered and RecordsTotal. This allows us to tell DataTables that we're bringing back partial results. DataTables then reflects that in the UI, as the screenshot shown above.

DataTables Sorting

To enable sorting in our DataTables grid, we need to set the value of ordering option to true. DataTables plugin will automatically start showing small arrows in the header row of the DataTables.

When user will click within the header row of any column, the DataTables plugin will send the column index and sort direction to server. We implemented DataTableOrder class on server side to receive this information. The value of column index will be available in Column property of this class and sort direction information will be available in Dir property. If the value of Column property is 0, it means user has clicked within the header row of first column ProductId so we can sort the data based on ProductId column. The following code snippet shows you how you can add sorting support on all five columns of the DataTables grid we implemented in this tutorial.

DataTables Paging

To enable paging in our DataTables grid, we need to set the value of paging option to true. DataTables plugin will automatically start showing paging interface at the bottom of the DataTables grid. It will also add a drop down on top of the grid that will show the no of entries display on each page. User can interact with the dropdown or paging interface and the grid will automatically update its display with the data received from the server.

When user will interact with the dropdown or paging interface, the DataTables plugin will send the current page index and page size information to server. Current page index can be retrieved using Start property and page size can be retrieved using Length property. We can use LINQ Skip and Take methods to fetch only the required rows from the database and send them to client to be displayed in DataTables.

Summary

In this tutorial, I have covered DataTables paging, sorting and searching features and shown you how you can use DataTables with ASP.NET Web API. DataTables has hundreds of other features which can spice up your web pages in no of different ways. It is impossible to cover all those features in a small tutorial but I will recommend you to visit DataTables official website to learn more about this amazing JQuery plugin. I will also try to post some more tutorials on DataTables in the future so keep on visiting my website to learn more about DataTables and other technologies.