Introduction
This article is focused on "Deferred loading of data" using Datatables.net in your ASP.NET MVC projects.
Datatables.net plugin is one of the most useful and responsive plugins that one can use for presenting data in a tabular format. This plugin has all the required features like sorting, filtering, searching and pagination out of the box. However, when it comes to load data dynamically, i.e., deferred loading of data, we need to implement the server side functionality for it to work. Let us quickly get into an action to implement it using ASP.NET MVC and jQuery.
Background
When you open the Facebook page, only the few recent posts will be popped up in the screen. And as you scroll down through the page, you will have additional posts/content added up to the existing page on run time. This way, Facebook delivers data only when it is demanded by the user. This can also be referred to as "Deferred loading of data".
Deferred loading is the way of loading only partial or required data in the UI.
Datatables.net is a free and open source plug-in you can find at http://www.datatables.net for implementing Deferred loading of data.
Our goal here is to load data from the database to HTML table as shown below using ASP.NET MVC and datatables.net plugin.
You need not worry about creating paging, sorting and filtering. Datatables.net takes care of all these functions by default. However, you can still override this functionality, but it is not covered in this article.
#Step 1: Creating HTML
The HTML code below contains an HTML table element with thead and tfoot tags defined. Note, the table for which you are going to apply datatables plugin should contain the thead and tfoot tags as shown below or otherwise, the plugin will not work correctly.
Hint: The JavaScript call $('#example').DataTable() binds the datatable plugin to the existing HTML #example table.
Note: The columns: [{ data: "OrgNumber" }, { data: "Name" }, { data: "Phone" }, { data: "Fax" }] must be defined in order to display the required columns and ensure the number of <th> columns in HTML table matches with the columns defined in DataTable() property.
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript"
src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet"
href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />
</head>
<body>
<div>
<table id="example" class="display" style="width:100%;">
<thead>
<tr>
<th>OrgNumber</th>
<th>Name</th>
<th>Phone</th>
<th>Fax</th>
</tr>
</thead>
<tfoot>
<tr>
<th>OrgNumber</th>
<th>Name</th>
<th>Phone</th>
<th>Fax</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>
}
<script>
$(document).ready(function () {
$('#example').DataTable({
processing: true,
serverSide: true,
searchable: true,
ajax: {
url: '@Url.Action("GetOrganizations")',
type: 'POST',
dataSrc: 'data',
},
columns: [{ data: "OrgNumber" },
{ data: "Name" }, { data: "Phone" }, { data: "Fax" }]
});
});
</script>
#Step 2: Creating MVC Action Method
The action method GetOrganizations accepts four parameters [draw, length, search and start] and returns the JsonResult.
The parameters defined in the action method are the ones posted by datatables to the server based on which we can implement logic for paging, filtering, etc. More information can be found here.
public JsonResult GetOrganizations(int length, int start)
{
List<Organization> organizations = new List<Organization>();
using (SqlConnection conn = new SqlConnection
(ConfigurationManager.AppSettings["sqlconn"].ToString())
{
conn.Open();
SqlCommand cmd = new SqlCommand();
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendFormat("select top({0}) * from (select org.*, row_number()
over (order by CreatedDate DESC) as [row_number] from Organization org) org", length);
sbSQL.AppendFormat(" where row_number >{0}", start );
string searchVal = HttpContext.Request.Form["search[value]"];
if (!string.IsNullOrEmpty(searchVal))
{
sbSQL.AppendFormat(" and Name like '%{0}%' or
OrganizationNumber like '%{0}%'", searchVal);
}
cmd.CommandText = sbSQL.ToString();
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
organizations.Add(new Organization()
{
Name = reader["Name"].ToString(),
Fax = reader["Fax"].ToString(),
Phone = reader["Phone"].ToString(),
OrgNumber = reader["OrganizationNumber"].ToString()
});
}
reader.Close();
conn.Close();
}
var response = new { data = organizations, recordsFiltered = 10000, recordsTotal = 10000};
return Json(response, JsonRequestBehavior.AllowGet);
}
In the above example, recordsFiltered and recordsTotal is manually set to 10000 just for illustration purposes. This value should be actual number of records available in your table.
Points of Interest
Hope this article helps you in some way on implementing Load on demand functionality in your project.
Please leave a comment in case you have any clarifications. I'll try to answer them. You could also refer to this datatables site for troubleshooting.
