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.

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

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.

DataTables AJAX call response in developer tools

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:

DataTables AJAX call response in developer tools

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.

DataTables Request Ordering

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.

DataTables Request Searching

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.

DataTables Request Columns

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 = ""
            };
        }
    }
}

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.

Visual Studio Solution Explorer

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

Datatables

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" }
    ]
});

Datatables Searching and Filtering

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" }
    ]
});

Datatables Sorting

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" }
    ]
});

DataTables Paging

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 = ""
};

Here is the complete source code of ProductController class

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();

        // 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;
        }

        // 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;
                    }
            }
        }

        // 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.

 

 

About author


User avatar

Waqas Anwar

Founder of EzzyLearning.com, Senior Consultant, Microsoft .NET and Web Technologies

Comments 3

Add Comment