Server side paging with DataTables.js and MVC

Source code for this article can be found here – https://github.com/RossWhitehead/ServerSidePagingDataTables

When evaluating the usefulness of a javascript grid library the first thing I do is determine whether it is possible to implement server-side paging. This is a must-have feature for most business scenarios, and you will be backing yourself into a corner if you implement a grid that only supports client-side paging.

In a previous post I demonstrated how to implement server side paging with KoGrid. I’m going to repeat this demonstration, but this time for DataTables.NET.

I like KoGrid. It’s based on Knockout, which in my humble opinion is the bees-knees, and my go-to when developing all but the most basic of MVC views.

I also like DataTables.NET.

Both KoGrid and DataTables.NET are widely used and well respected in the community. They meet most demands placed on them, but they differ in terms of their implementation. There’s a good article by Jason Howard here comparing the two. And I agree with most of the statements with the caveat that I am reserving judgement on the assertion that DataTables.NET works with Knockout. I have not yet managed to get the 2 working together when implementing server-side paging. And I will reserve judgment until I have either succeeded or failed miserably.

So onto the implementation of server side paging with DataTables.NET and MVC (without Knockout).

This is what I’m going to build –

BasicDataTable

When initializing the grid, rather than requesting all products from the server, the client is going to request a single page of products. And when the user interacts with the grid – either changing the number of entries to view, selecting a new page, clicking on a column header to sort, clicking on the refresh button, or typing in the search box – the client will update the grid by requesting products one page at a time. In so doing the server will receive a greater number of requests. However, each request will only be for a small page of data, and so should take significantly less resources to meet. Additionally, the client will not be charged with initializing the page with a huge amount of data.

I will be presenting the solution in the following order –

  1. Model
  2. Controller
  3. View
  4. View Model

1. Model

ProductCategory.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ServerSidePagingDataTables.Models
{
    public class ProductCategory
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ProductCategoryId { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        // Navigational Properties
        public virtual ICollection<Product> Products { get; set; }
    }
}

Product.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ServerSidePagingDataTables.Models
{
    public class Product
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ProductId { get; set; }

        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        [Required]
        [StringLength(1000)]
        public string Description { get; set; }

        [Required]
        public int ProductCategoryId { get; set; }

        // Navigation properties
        public virtual ProductCategory ProductCategory { get; set; }
    }
}

ServerSidePagingDataTablesDbContext.cs

using System.Data.Entity;

namespace ServerSidePagingDataTables.Models
{
    public class ServerSidePagingDataTablesDbContext : DbContext
    {
        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<ProductCategory> ProductCategories { get; set; }
    }
}

I am using Entity Framework code-first.

I have 2 entities – ProductCategory and Product, with a one-to-many relationship between them. And I have a DbContext called DataTablesDemoDbContext, which is used to map the model to the database.

2. Controller

using DataTables.Mvc;
using System;
using System.Linq;
using System.Linq.Dynamic;
using System.Web.Mvc;
using ServerSidePagingDataTables.Models;

namespace ServerSidePagingDataTables.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public JsonResult DataTableGet([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
        {
            var db = new ServerSidePagingDataTablesDbContext();

            IQueryable<Product> query = db.Products;

            var totalCount = query.Count();

            // Apply filters
            if (requestModel.Search.Value != String.Empty)
            {
                var value = requestModel.Search.Value.Trim();
                query = query.Where(p => p.Name.Contains(value) || p.Description.Contains(value));
            }

            var filteredCount = query.Count();

            // Sort
            var sortedColumns = requestModel.Columns.GetSortedColumns();
            var orderByString = String.Empty;

            foreach (var column in sortedColumns)
            {
                orderByString += orderByString != String.Empty ? "," : "";
                orderByString += (column.Data == "Category" ? "ProductCategory.Name" : column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? " asc" : " desc");
            }

            query = query.OrderBy(orderByString == String.Empty ? "name asc" : orderByString);

            // Paging
            query = query.Skip(requestModel.Start).Take(requestModel.Length);

            var data = query.Select(p => new
            {
                ProductId = p.ProductId,
                Name = p.Name,
                Description = p.Description,
                Category = p.ProductCategory.Name
            }).ToList();

            return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);
        }
    }
}

I have an MVC controller with 2 actions.

Index serves up a .cshtml page that contains the UI markup (view) and the javascript (view model). I do not pass any data to the view – as the view model will be requesting the data once the page has been loaded.

DataTableGet returns a Json response containing the data required to initialize or update the grid. This action will be called by the view model using Ajax when the grid needs updating with new data.

The first thing of note about the DataTableGet action is that it uses a custom model binder, DataTableBinder, to map the request to the IDataTableRequest requestModel parameter. The DataTableGet action also responds with an instance of DataTablesResponse serialized to a Json string.

The aforementioned interfaces and classes can be downloaded from Nuget.

PM> Install-Package datatables.mvc5

The reason we need a custom model binder is that when the DataTable.NET client code makes Ajax requests for new data, the request details, such as the page required, number of records, sort orders, and search strings, are passed using query string parameters. And the naming convention used by DataTables.NET is not compatible with the default MVC model binders, as you can see from the example URL below.

http://localhost:50055/api/datatableproducts?draw=1&columns[0][data]=productId&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=true&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=name&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=true&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=description&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=true&columns[2][search][value]=&
columns[2][search][regex]=false&columns[3][data]=category&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=true&columns[3][search][value]=&columns[3][search][regex]=false&order[0][column]=0&order[0][dir]=asc&start=0&length=10&search[value]=&search[regex]=false&_=1437911688049

The DataTableGet action creates an instance of the database context, and constructs a query to grab a page of products, whilst applying the requested filters and sort orders. I have used System.Linq.Dynamic to simplify the construction of the query. This library is not included in an MVC project by default. It needs to be loaded from NuGet.

PM> Install-Package System.linq.Dynamic

3. View

 <link href="//cdn.datatables.net/plug-ins/1.10.7/integration/bootstrap/3/dataTables.bootstrap.css" rel="stylesheet" /> 

I am creating a DataTables.NET grid styled with bootstrap. I therefore need to include the dataTable.bootstrap.css stylesheet in my view.

<style type="text/css">
    .list-panel {
        margin-top: 20px;
    }

        .list-panel .panel-heading {
            overflow: auto;
            padding: 5px 20px;
        }

        .list-panel .panel-title {
            float: left;
            margin-top: 10px;
        }

        .list-panel .panel-body {
            padding: 20px 20px 10px 20px;
        }

        .list-panel .refresh-button {
            float: right;
        }

    .data-table {
        border-collapse: collapse;
        border-spacing: 0;
    }
</style>

And some styling for the panel that grid will sit in.

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-primary list-panel" id="list-panel">
            <div class="panel-heading list-panel-heading">
                <h3 class="panel-title list-panel-title">Products</h3>
                <button type="button" class="btn btn-default btn-md refresh-button" id="refresh-button">
                    <span class="glyphicon glyphicon-refresh" aria-hidden="true"></span> Refresh
                </button>
            </div>
            <div class="panel-body">
                <table id="data-table" class="table table-striped table-bordered" style="width:100%"></table>
            </div>
        </div>
    </div>
</div>

The HTML for the grid is a simple <table> element, which I have placed in a bootstrap panel along with a refresh button.

4. View Model

<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/plug-ins/1.10.7/integration/bootstrap/3/dataTables.bootstrap.js"></script>

The view model includes the DataTables.NET JQuery library (jquery.dataTables.min.js), and additional includes the DataTables,NET Bootstrap integration library (dataTables.bootstrap.js). This library can be omitted if you do not want to use Bootstrap with you grid.

<script type="text/javascript">
    $(function () {
        var productListVM = {
            dt: null,

            init: function () {
                dt = $('#data-table').DataTable({
                    "serverSide": true,
                    "processing": true,
                    "ajax": "/home/datatableget",
                    "columns": [
                        { "title": "Product Id", "data": "ProductId", "searchable": false },
                        { "title": "Name", "data": "Name" },
                        { "title": "Description", "data": "Description" },
                        { "title": "Category", "data": "Category" }
                    ],
                    "lengthMenu": [[2, 5, 10, 25], [2, 5, 10, 25]]
                });
            },

            refresh: function () {
                dt.ajax.reload();
            }
        }

        $('#refresh-button').on("click", productListVM.refresh);

        /////////////////////////////////////////////////////////////////
        // Let's kick it all off
        productListVM.init();
    })
</script>

I have encapsulated the view model functionality into a javascript object called productListVM.

productListVM has a init function which initializes the grid by calling the DataTables.NET DataTable function. It passes in a number of configuration options to define how the grid will look and behave. “serverSide”: true enables server side processing, “processing”: true tells DataTable.NET to display a “Processing..” message when the grid is being updated, and “ajax”: “/home/datatableget” defines the data-source for the Ajax requests. These 3 options are all that are required to implement server side paging. The grid will respond to all relevant user interaction – sorting, filtering, and paging – by automatically requesting the relevant data from the server and updating the grid. No other view model coding is required, and this is the beauty of server side processing with DataTables.NET. Compare this with KoGrid where it is necessary to write code to define subscriptions to a number of events and to construct the request data.

productListVM also has a refresh function, which forces a reload of the grid. The refresh button is assigned a click event handler which call the this function.

And finally, everything thing is kicked off, with the grid being initialized, by calling productListVM.init().

Advertisements

3 thoughts on “Server side paging with DataTables.js and MVC

  1. Hi, Ross!
    I’m sending You again my comment about Your “Server side paging with DataTables.js and MVC” post (August 1, 2015); this is not a sort of stalking, simply I’m not sure I succeeded to send You my previous post.
    So, and first of all, my congratulations for Your excellent job; I downloaded its code, the last version as in “https://github.com/RossWhitehead/ServerSidePagingDataTables” (April 21, 2016) and put it at work, just after having set up a simple “SQLServer Express 2014” database (the one generated by Your entity model) and loaded some test data (I didn’t find any database test together with your source code; did I miss anything, Ross?).

    Now, my questions (and related help requests…):

    1. while trying to sort the column labeled “Category” (sort is enabled here too), I get the following Ajax error:
    “DataTables warning: table id=data-table Ajax – error. For more information about this error, please see http://datatables.net/tn/7”.
    Useless to say, I didn’t get any hint from that web page, because of my lack of expertise in javascript / jquery /…

    2. in “/Home/Index.cshtml” view, while the element You defined works in a perfect “responsive” way (as well as the “show” dropdownlist and the “search” textbox), the same seems not to occur with the element (i.e.: the element where the list of products get displayed). The effect I get (I’m operating in Internet Explorer V.11.212.10586.0) is the element keeps displaying beyond the right boundary of the element.

    Any ideas to help me or did I do anything wrong with Your (absolutely excellent, I insist) material?
    Thank You in advance and forgive me for my so “chatty” post!

    Francesco – Anzio (ROMA) – ITALY

    Like

    • Hi Francesco,

      “1. while trying to sort the column labeled “Category” (sort is enabled here too), I get the following Ajax error:
      “DataTables warning: table id=data-table Ajax – error. For more information about this error, please see http://datatables.net/tn/7”.
      Useless to say, I didn’t get any hint from that web page, because of my lack of expertise in javascript / jquery /…”

      My bad, I failed to notice that the sorting was not working with the category column. The reason why is that it is a foreign key column, and so the “Column” field in the client-side data object needs to be mapped to the ProductCategory.Name field in the server-side Product object. Unfortunately, I have not been able to think of an extensible way of doing this, and so I have settled on simply replacing the column name when constructing the sort expression, as shown below –
      orderByString += (column.Data == “Category” ? “ProductCategory.Name” : column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? ” asc” : ” desc”);
      When I get a spare moment I will see if I can come up with a better solution.

      “2. in “/Home/Index.cshtml” view, while the element You defined works in a perfect “responsive” way (as well as the “show” dropdownlist and the “search” textbox), the same seems not to occur with the element (i.e.: the element where the list of products get displayed). The effect I get (I’m operating in Internet Explorer V.11.212.10586.0) is the element keeps displaying beyond the right boundary of the element.”

      This is an interesting one. Datatables.js is adding a fixed width style, width:1098px, to the table element. This is overriding the width:100% that I apply as part of the .data-table class. To fix this I have moved the width:100% style inline.

      And finally, with regard to the database. The project has code-first migrations enabled, and the test data is in the Seed method of the Migrations/Configuration class. So all you should need to do is execute the “update-database” command in the package manager console. The database will then be created on the default database instance – which is (LocalDB)\MSSQLLocalDB if you are using Visual Studio 2015. If you are using an earlier version of Visual Studio then the database instance may be something other than MSSQLLocalDB. If this is the case then you would need to modify the defaultConnectionFactory element in the web.config to match.

      I have updated the code examples and the repo with the aforementioned fixes.

      Thanks for pointed out the issues with the code. And I’m pleased that you found the post useful. I have benefited greatly over the years from the community’s blogs, so one of my goals for my blog is to give something back to the community.

      Regards,
      Ross

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s