Tuesday, November 5, 2013

Server side paging, filtering and sorting using datatables

Nothing better than writing some code to explain a feature. This post shows how to do server-side paging in a table using datatables.net
The idea is to display a table with a large amount of data (68k rows). As it's not a good practice to load all at once, it' better to implement server-side paging.

First download datatables.net from here and reference them in your html file.

In our sample we are using a table Tasks with 3 fields, ie, TaskID, TaskName, Complete
public class Task
    public int TaskID { get; set; }
    public string TaskName { get; set; }
    public bool Complete { get; set; }
    public static List<Task> GetAllTasks()
        //returns all tasks in the table
public class TaskManager
    public static List<Task> GetAllTasks()
        return Task.GetAllTasks();   

Lets create the HTML markup first

<table id="taskTable">
            <th>Task ID</th>
            <th>Task Name</th>

Now lets wire up datatable to the table

var oTable = $('#taskTable').dataTable({
    "aoColumns": [
                { "sName": "TaskID" },
  { "sName": "TaskName" },
                { "sName": "Complete",
                    "bSearchable": false,
                    "bSortable": false
                { "sName": "Actions",
                    "bSearchable": false,
                    "bSortable": false,
                    //this is to render custom content in the column
                    "fnRender": function (oObj) {
                        var row_button = 'Edit |' +
                            'Details | ' +
                        return row_button;
    "bInfo": false,
    "bLengthChange": false,
    "bProcessing": true,
    "bServerSide": true,
    //fires when table initialization completes
    "fnInitComplete": function (oSettings, json) {
    "sAjaxSource": "/Task/DataTableAjaxHandler",
    //enables tooltip on left top of the table
    "sDom": '<"toolbar">frtip',
    "sPaginationType": "bootstrap"
//adding a button to tool tip
    .html('Add Task')
    .css("text-align", "left");
// adding a css class to filter textbox
$(".dataTables_filter input").addClass("form-control");

Here as you can see TaskID and TaskName are sortable and searchable.
Now, the model that is passed from datatables to server-side

public class DataTablesViewModel
    /// Request sequence number sent by DataTable, same value must be returned in response
    public string sEcho { get; set; }

    /// Text used for filtering
    public string sSearch { get; set; }

    /// Number of records that should be shown in table
    public int iDisplayLength { get; set; }

    /// First record that should be shown(used for paging)
    public int iDisplayStart { get; set; }

    /// Number of columns in table
    public int iColumns { get; set; }

    /// Number of columns that are used in sorting
    public int iSortingCols { get; set; }

    /// Comma separated list of column names
    public string sColumns { get; set; }

Finally, the controller method

public ActionResult DataTableAjaxHandler(DataTablesViewModel param)
    var allTasks = TaskManager.GetAllTasks();
    IEnumerable<Task> filteredTasks;
    if (!string.IsNullOrEmpty(param.sSearch))
        //Optionally check whether the columns are searchable at all 
        var isTaskIDSearchable = Convert.ToBoolean(Request["bSearchable_0"]);
        var isTaskNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]);

        filteredTasks = allTasks.Where(tsk => 
                        isTaskIDSearchable && Convert.ToString(tsk.TaskID).Contains(param.sSearch.ToLower()) ||
                        isTaskNameSearchable && tsk.TaskName.ToLower().Contains(param.sSearch.ToLower()));
        filteredTasks = allTasks;
    var isTaskIDSortable = Convert.ToBoolean(Request["bSortable_0"]);
    var isTaskNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
    var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
    Func<Task, string> orderingFunction = (tsk => 
                            sortColumnIndex == 0 && isTaskIDSortable ? Convert.ToString(tsk.TaskID) :
                            sortColumnIndex == 1 && isTaskNameSortable ? tsk.TaskName :
    var sortDirection = Request["sSortDir_0"]; // asc or desc
    if (sortDirection == "asc")
        filteredTasks = filteredTasks.OrderBy(orderingFunction);
        filteredTasks = filteredTasks.OrderByDescending(orderingFunction);

    var displayedTasks = filteredTasks.Skip(param.iDisplayStart).Take(param.iDisplayLength);
    var result = from dt in displayedTasks
                    select new[] { 
    return Json(new
        sEcho = param.sEcho,
        iTotalRecords = allTasks.Count(),
        iTotalDisplayRecords = filteredTasks.Count(),
        aaData = result

That's all to it. Happy coding!

Related Posts :

4 comments on "Server side paging, filtering and sorting using datatables"

Add your comment. Please don't spam!
Subscribe in a Reader
Georges Damien on December 22, 2014 at 1:34 PM said...

Good article ! thanks !

sourav mondal on December 5, 2015 at 1:17 AM said...


Here we will see followings with ASP.NET MVC as server side...
Part 1: Implement jQuery Datatable (Basic initialization) in ASP.NET MVC application.
Part 2: jQuery Datatable server side pagination and sorting in ASP.NET MVC
Part 3: Implement custom multicolumn server-side filtering in jQuery dataTables

Unknown on December 7, 2015 at 8:59 PM said...

I was trying to find the way to do server-side pagination. The example in datatable.net did not work for me . Your solution is perfectly working . Thank you very much. If you would be in Istanbul , I could buy you a drink :) Thanks a lot !

HARUN ACAR on December 7, 2015 at 9:03 PM said...

I was trying to find the way to do server-side pagination. The example in datatable.net did not work for me . Your solution is perfectly working . Thank you very much. If you would be in Istanbul , I could buy you a drink :) Thanks a lot !

Post a Comment