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.

Answer 2

Since you are using LINQ to SQL, paginate is really easy:

var c = new MyDataContext("your string");

c.Employees.Skip(pageIndex * pageSize).Take(pageSize);

This code will effectively paginate on the server

I haven't used the DataTables jQuery plug-in, but I assume you use AJAX to get the data (since you are not using MVC), so simply send as parameters the current page index, and the number of rows per page - page size, and that's it

To fulfill the requirement, you would need to order your query on the server as well, so you would need to send the order criteria to the server and apply the order.

To order on the server based on a string, check the following question:

Dynamic LINQ OrderBy on IEnumerable<T>

Answer 3

Little late to the party, but this is worth sharing :)

Employees
    .OrderBy(sortColumn + " " + sortOrder)
    .Skip(pageNo * pageSize)
    .Take(pageSize)
    .ToList();