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.
Table of Contents
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.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
public class DataTableRequest
{
public int Draw { get; set; }
public int Start { get; set; }
public int Length { get; set; }
}
}
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.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
public class DataTableOrder
{
public int Column { get; set; }
public string Dir { get; set; }
}
}
DataTables also have search related parameters. These parameters send user searched keyword to server.
Create a DataTableSearch class in the Models/DataTables folder and declared following two properties in it.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
public class DataTableSearch
{
public string Value { get; set; }
public bool Regex { get; set; }
}
}
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.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
public class DataTableColumn
{
public string Data { get; set; }
public string Name { get; set; }
public bool Searchable { get; set; }
public bool Orderable { get; set; }
public DataTableSearch Search { get; set; }
}
}
Once all the above classes are implemented, we need to declare their instances in top level DataTableRequest class.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
public class DataTableRequest
{
public int Draw { get; set; }
public int Start { get; set; }
public int Length { get; set; }
public DataTableOrder[] Order { get; set; }
public DataTableColumn[] Columns { get; set; }
public DataTableSearch Search { get; set; }
}
}
It is now time to revisit ProductsController class. The GetProducts method will receive DataTableRequest object as input parameter.
using ASPNET_MVC_Datatables.Data;
using ASPNET_MVC_Datatables.Data.Repository;
using ASPNET_MVC_Datatables.Models.DataTables;
namespace ASPNET_MVC_Datatables.API
{
public class ProductsController : ApiController
{
private IProductRepository productRepository;
public ProductsController()
{
this.productRepository = new ProductRepository(new NorthwindEntities());
}
[HttpGet]
[Route("api/Products")]
public DataTableResponse GetProducts(DataTableRequest request)
{
// Query products
var products = productRepository.GetProducts();
return new DataTableResponse
{
draw = request.Draw,
recordsTotal = products.Count(),
recordsFiltered = products.Count(),
data = products.ToArray(),
error = ""
};
}
}
}
Following is the list of parameters DataTables expects will return from the server side.
We need to create following DataTableResponse class with above properties
public class DataTableResponse
{
public int draw { get; set; }
public long recordsTotal { get; set; }
public int recordsFiltered { get; set; }
public object[] data { get; set; }
public string error { get; set; }
}
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.
using System.Web.Http.ModelBinding;
using System.Web.Http.ValueProviders;
using ASPNET_MVC_Datatables.Models.DataTables;
namespace Ascertia.LEI.Admin.Models.DataTables
{
public class DataTableModelBinder: IModelBinder
{
public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext)
{
if (bindingContext.ModelType != typeof(DataTableRequest))
{
return false;
}
var model = (DataTableRequest) bindingContext.Model ?? new DataTableRequest();
model.Draw = Convert.ToInt32(GetValue(bindingContext, "draw"));
model.Start = Convert.ToInt32(GetValue(bindingContext, "start"));
model.Length = Convert.ToInt32(GetValue(bindingContext, "length"));
// Search
model.Search = new DataTableSearch
{
Value = GetValue(bindingContext, "search.value"),
Regex = Convert.ToBoolean(GetValue(bindingContext, "search.regex"))
};
// Order
var o = 0;
var order = new List < DataTableOrder > ();
while (GetValue(bindingContext, "order[" + o + "].column") != null)
{
order.Add(new DataTableOrder
{
Column = Convert.ToInt32(GetValue(bindingContext, "order[" + o + "].column")),
Dir = GetValue(bindingContext, "order[" + o + "].dir")
});
o++;
}
model.Order = order.ToArray();
// Columns
var c = 0;
var columns = new List < DataTableColumn > ();
while (GetValue(bindingContext, "columns[" + c + "].data") != null)
{
columns.Add(new DataTableColumn {
Data = GetValue(bindingContext, "columns[" + c + "].data"),
Name = GetValue(bindingContext, "columns[" + c + "].name"),
Orderable = Convert.ToBoolean(GetValue(bindingContext, "columns[" + c + "].orderable")),
Searchable = Convert.ToBoolean(GetValue(bindingContext, "columns[" + c + "].searchable")),
Search = new DataTableSearch {
Value = GetValue(bindingContext, "columns[" + c + "][search].value"),
Regex = Convert.ToBoolean(GetValue(bindingContext, "columns[" + c + "].search.regex"))
}
});
c++;
}
model.Columns = columns.ToArray();
bindingContext.Model = model;
return true;
}
private string GetValue(ModelBindingContext context, string key)
{
var result = context.ValueProvider.GetValue(key);
return result == null ? null: result.AttemptedValue;
}
}
}
We also need to add ModelBinder attribute to our DataTableRequest class to let ASP.NET Web API framework know that we want incoming parameters to be mapped with our DataTableRequest object.
namespace ASPNET_MVC_Datatables.Models.DataTables
{
[ModelBinder(typeof(DataTableModelBinder))]
public class DataTableRequest
{
public int Draw { get; set; }
public int Start { get; set; }
public int Length { get; set; }
public DataTableOrder[] Order { get; set; }
public DataTableColumn[] Columns { get; set; }
public DataTableSearch Search { get; set; }
}
}
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.
$('#example').DataTable({
processing: true,
serverSide: true,
searching: true,
ordering: false,
paging: false,
ajax: "api/Products",
columns: [
{ "data": "ProductID" },
{ "data": "ProductName" },
{ "data": "QuantityPerUnit" },
{ "data": "UnitPrice" },
{ "data": "UnitsInStock" }
]
});
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.
[HttpGet]
[Route("api/Products")]
public DataTableResponse GetProducts(DataTableRequest request)
{
// Query products
var products = productRepository.GetProducts();
// Searching Data
IEnumerable<Product> filteredProducts;
if (request.Search.Value != "")
{
var searchText = request.Search.Value.Trim();
filteredProducts = products.Where(p =>
p.ProductName.Contains(searchText) ||
p.QuantityPerUnit.Contains(searchText) ||
p.UnitPrice.ToString().Contains(searchText));
}
else
{
filteredProducts = products;
}
return new DataTableResponse
{
draw = request.Draw,
recordsTotal = products.Count(),
recordsFiltered = filteredProducts.Count(),
data = filteredProducts.ToArray(),
error = ""
};
}
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.
$('#example').DataTable({
processing: true,
serverSide: true,
searching: true,
ordering: true,
paging: false,
ajax: "api/Products",
columns: [
{ "data": "ProductID" },
{ "data": "ProductName" },
{ "data": "QuantityPerUnit" },
{ "data": "UnitPrice" },
{ "data": "UnitsInStock" }
]
});
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.
// Sort Data
if (request.Order.Any())
{
int sortColumnIndex = request.Order[0].Column;
string sortDirection = request.Order[0].Dir;
Func<Product, string> orderingFunctionString = null;
Func<Product, int> orderingFunctionInt = null;
Func<Product, decimal?> orderingFunctionDecimal = null;
switch (sortColumnIndex)
{
case 0: // ProductID
{
orderingFunctionInt = (c => c.ProductID);
filteredProducts =
sortDirection == "asc"
? filteredProducts.OrderBy(orderingFunctionInt)
: filteredProducts.OrderByDescending(orderingFunctionInt);
break;
}
case 1: // ProductName
{
orderingFunctionString = (c => c.ProductName);
filteredProducts =
sortDirection == "asc"
? filteredProducts.OrderBy(orderingFunctionString)
: filteredProducts.OrderByDescending(orderingFunctionString);
break;
}
case 2: // QuantityPerUnit
{
orderingFunctionString = (c => c.QuantityPerUnit);
filteredProducts =
sortDirection == "asc"
? filteredProducts.OrderBy(orderingFunctionString)
: filteredProducts.OrderByDescending(orderingFunctionString);
break;
}
case 3: // UnitPrice
{
orderingFunctionDecimal = (c => c.UnitPrice);
filteredProducts =
sortDirection == "asc"
? filteredProducts.OrderBy(orderingFunctionDecimal)
: filteredProducts.OrderByDescending(orderingFunctionDecimal);
break;
}
case 4: // UnitsInStock
{
orderingFunctionDecimal = (c => c.UnitsInStock);
filteredProducts =
sortDirection == "asc"
? filteredProducts.OrderBy(orderingFunctionDecimal)
: filteredProducts.OrderByDescending(orderingFunctionDecimal);
break;
}
}
}
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.
$('#example').DataTable({
processing: true,
serverSide: true,
searching: true,
ordering: true,
paging: true,
ajax: "api/Products",
columns: [
{ "data": "ProductID" },
{ "data": "ProductName" },
{ "data": "QuantityPerUnit" },
{ "data": "UnitPrice" },
{ "data": "UnitsInStock" }
]
});
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.
// Paging Data
var pagedProducts = filteredProducts.Skip(request.Start).Take(request.Length);
return new DataTableResponse
{
draw = request.Draw,
recordsTotal = products.Count(),
recordsFiltered = products.Count(),
data = pagedProducts.ToArray(),
error = ""
};
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.
hey you forgot to define DataTableResponse in this tutorial.
Is there a way I can test the API side using Postman? What is the format of the GET request that I would need to send?
Thanks, I’m a beginner with DataTables, and this tutorial is really well explained.
wao thanks for the latest explanation, all other examples are alteast 3-5 years older