JQuery DataTables server-side pagination

Question

In my web-app I'm using JQuery DataTables plug-in to show data retrieved from database.

I'm currently using client-side pagination, but data in my tables are growing a lot, and loading in ASP.NET pages is now becoming a bit slow. So I was planning to switch to server-side pagination.

I know that DataTables plug-in supports it, but searching around I haven't found notting clear about implementing it.

My main doubt is: if I implement pagination on server-side I also have to implement ordering, or I can delegate it to client-side?

Have you ever experienced this?

NOTE I'm using Linq to SQL to connect to my DB

Answer 1

The existing answers might apply to an old version of dataTable, but current versions (I am using 1.10+) pass the start record and length so anything suggesting pageNo * pageSize is going to give incorrect results.

First simple "manual" approach

The accepted answer was also very complicated for what I wanted to do so, after some debugging, I found that the page size and start record are simply passed as Http Request values named start and length. The text search is passed as search[value] The sort order is passed in a member named order[0][column] and the sort direction in order[0][dir] etc.

The basic code I used to sort and filter looks like this:

Get the paging, sorting and filtering values from the HTTP Request object:

int startRec = 0;
int.TryParse(Request["start"], out startRec);
int pageSize = 10;
int.TryParse(Request["length"], out pageSize);
var search = Request["search[value]"];
var order = Request["order[0][column]"];
var direction = Request["order[0][dir]"];

var query = this._dataStore.Records.AsQueryable();

Apply (case insensitive) search first:

if (!string.IsNullOrWhiteSpace(search))
{
    query = query.Where(x => x.Label.ToLower().Contains(search.ToLower()));
}

Then apply any sorting:

switch (order)
{
    // My id column
    case "0":
        query = (direction == "desc") ? query.OrderByDescending(x => x.Id) : query.OrderBy(x => x.Id);
        break;
    // My label column
    case "1":
        query = (direction == "desc") ? query.OrderByDescending(x => x.Label) : query.OrderBy(x => x.Label);
        break;
}

Finally apply the paging:

query = query.Skip(startRec).Take(pageSize);

The correct records are now ready to return.

Update (using "Datatables.net for MVC5")

Once I understood the basics of server-side dataTables, it was time to start looking for existing plugins/utils to simplify this code. The most appropriate one I have found so far, for MVC 5, is the Datatables.net for MVC5 nuget package.

1. Install the NuGet Package

2. Change the controller Action to use a DataTablesBinder to provide a IDataTablesRequest interface

e.g.

 public JsonResult Table([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestmodel)

3. Apply any search filter first:

e.g.

if (!string.IsNullOrEmpty(requestmodel.Search.Value))
{
    query = query.Where(x => x.CompanyTypeName.Contains(requestmodel.Search.Value) || x.CompanyTypeDescription.Contains(requestmodel.Search.Value));
}

4. The apply any sorting:

e.g.

foreach (var sort in requestmodel.Columns.GetSortedColumns())
{
    switch (sort.Name)
    {
        case "CompanyTypeDescription":
            query = sort.SortDirection == Column.OrderDirection.Ascendant ? query.OrderBy(x => x.CompanyTypeDescription) : query.OrderByDescending(x => x.CompanyTypeDescription);
            break;
        case "CompanyTypeName":
        default:
            query = sort.SortDirection == Column.OrderDirection.Ascendant ? query.OrderBy(x => x.CompanyTypeName) : query.OrderByDescending(x => x.CompanyTypeName);
            break;
    }
}

5. Then apply the paging using Skip and Take as before:

e.g.

var result = query.Skip(requestmodel.Start).Take(requestmodel.Length).Select(x => new { x.CompanyTypeName, x.CompanyTypeDescription });

6. And finally return the JSON result using the DataTablesResponse object:

e.g.

return Json(new DataTablesResponse(requestmodel.Draw, result, query.Count(), base.RefSureContext.CompanyType.Count()), JsonRequestBehavior.AllowGet);

This simplified all the searching, sorting & paging into a nice easy to repeat pattern.

The documentation for the addin is here.