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

view raw ob-datatables-serverside-1.cs hosted with ❤ by GitHub

Lets create the HTML markup first

	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 = '<a href="http://www.blogger.com/Tasks/Edit/' + Obj.aData[0] + '">Edit</a> |' +
	'<a href="http://www.blogger.com/Tasks/Details/' + Obj.aData[0] + '">Details</a> | ' +
	'<a class="deleteTask" data-taskid=" + oObj.aData[0] + " href="">Delete</a>';
	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": '&lt;"toolbar"&gt;frtip',
	"sPaginationType": "bootstrap"
	});
	//adding a button to tool tip
	$("div.toolbar")
	.html('<a add="" ask="" class="btn btn-primary" href="http://www.blogger.com/@Url.Action(">Add Task</a>')
	.css("text-align", "left");
	// adding a css class to filter textbox
	$(".dataTables_filter input").addClass("form-control");

view raw ob-datatables-serverside.js hosted with ❤ by GitHub

Now lets wire up datatable to the table

<table id="taskTable">

<thead>

<tr>

<th>Task ID</th>

<th>Task Name</th>

<th>Complete</th>

<th>Actions</th>

</tr>

</thead>

<tbody></tbody>

</table>

view raw ob-datatables-serverside.html hosted with ❤ by GitHub

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
	{
	/// <summary>
	/// Request sequence number sent by DataTable, same value must be returned in response
	/// </summary>
	public string sEcho { get; set; }
	/// <summary>
	/// Text used for filtering
	/// </summary>
	public string sSearch { get; set; }
	/// <summary>
	/// Number of records that should be shown in table
	/// </summary>
	public int iDisplayLength { get; set; }
	/// <summary>
	/// First record that should be shown(used for paging)
	/// </summary>
	public int iDisplayStart { get; set; }
	/// <summary>
	/// Number of columns in table
	/// </summary>
	public int iColumns { get; set; }
	/// <summary>
	/// Number of columns that are used in sorting
	/// </summary>
	public int iSortingCols { get; set; }
	/// <summary>
	/// Comma separated list of column names
	/// </summary>
	public string sColumns { get; set; }
	}

view raw ob-datatables-serverside-2.cs hosted with ❤ by GitHub

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);
	}

view raw ob-datatables-serverside-3.cs hosted with ❤ by GitHub

That's all to it. Happy coding!

Related Posts :

datatables

server-side paging

jquery

• Remote validation using jQuery Validate
• Overriding jQuery ajaxStart and ajaxStop locally
• JQuery Time