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
//DAL
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
    }
}
//BLL
public class TaskManager
{
    public static List<Task> GetAllTasks()
    {
        return Task.GetAllTasks();   
    }
}

Lets create the HTML markup first

<table id="taskTable">
    <thead>
        <tr>
            <th>Task ID</th>
            <th>Task Name</th>
            <th>Complete</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>


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 | ' +
                            'Delete';
                        return row_button;
                    }
                }
      ],
    "bInfo": false,
    "bLengthChange": false,
    "bProcessing": true,
    "bServerSide": true,
    //fires when table initialization completes
    "fnInitComplete": function (oSettings, json) {
        $("#jQueryAjaxmodal").modal("hide");
    },
    "sAjaxSource": "/Task/DataTableAjaxHandler",
    //enables tooltip on left top of the table
    "sDom": '<"toolbar">frtip',
    "sPaginationType": "bootstrap"
});
//adding a button to tool tip
$("div.toolbar")
    .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()));
    }
    else
    {
        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);
    else
        filteredTasks = filteredTasks.OrderByDescending(orderingFunction);

    var displayedTasks = filteredTasks.Skip(param.iDisplayStart).Take(param.iDisplayLength);
    var result = from dt in displayedTasks
                    select new[] { 
                        Convert.ToString(dt.TaskID),
                        dt.TaskName, 
                        Convert.ToString(dt.Complete),
                        ""
                };
    return Json(new
    {
        sEcho = param.sEcho,
        iTotalRecords = allTasks.Count(),
        iTotalDisplayRecords = filteredTasks.Count(),
        aaData = result
    },
    JsonRequestBehavior.AllowGet);
}

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

http://www.dotnetawesome.com/2015/11/implement-jquery-datatable-in-aspnet-mvc.html

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