Server-Side Data in jQuery DataTables with ASP.NET
The jQuery DataTables plugin is my default option whenever I need to display tabular data on a webpage. This is a great jQuery - not to be confused with this jQuery - tool that can be applied to a standard, well-formed HTML table and takes care of all the paging, sorting etc that you may need to apply to any tabular data.
It is a very versatile and easy-to-use plugin that can be easily extended with a number of readily available add-ons. It also supports theming, as you would expect from any established jQuery tool. With all of these bells and wistles, what's not to like about such a nandy little plugin?
I recently had to use DataTables in a legacy ASP.NET WebForms project in an effort to improve the overall UI experience. Unfortunately, this was also the first time that I managed to push the plugin to it's limits. Apparently when the data exceeds a certain threshold used within older browsers (IE8) and along slow ASP.NET controls (see Repeater), the DataTables may struggle to process the data in a timely fashion. As a result you can experience page hungs while the IE process eats more and more memory. In my case, I managed to hit the jackpot with all three parametersI ended up with a couple of broken pages that required a fix.
Thankfully, DataTables come with a handy server-side processing fallback in case client-side processing is not appropriate, as is in this case. So, what does server-side processing mean? By configuring DataTables to use this option, most of the table data manipulation is delegated to the server. Sorting, filtering and paging need to be managed on the server using the framwork you have at your disposal (ASP.NET, PHP, Ruby etc). The benefit of using this approach is that you can really optimise the data before it reaches the client, hence speeding up the overall performance of the site. In addition, since DataTables use ajax the user is not hit by repeated postbacks for every action (i.e filtering, paging etc). In this post I will show you the steps required to implent server-side processing on your ASP.NET WebForms website in a few steps:
1. The HTML
This may be the easy part, nonetheless, you need to ensure that your HTML table is properly defined. This is particularly important if you are using the DataTables plugin.
<table id="tblData" clientidmode="Static" class="hover">
<thead>
<tr class="gridStyle">
<th>UserId</th>
<th>Name</th>
<th>Address</th>
<th>Age</th>
<th>View Details</th>
</tr>
</thead>
<tbody></tbody>
</table>
2. The Javascript
The first part was an easy, albeit an important one. Moving on, there are two things required in order to use DataTables with server-side processing. First we need to initialise the plugin and then we need to define the right parameters such as the ajax and callback methods to request and process the server data.
$.ajaxSetup({
cache: false
});
var table = $('#tblData').DataTable({
"filter": false,
"pagingType": "simple_numbers",
"orderClasses": false,
"order": [[0, "asc"]],
"info": false,
"scrollY": "450px",
"scrollCollapse": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "WebService.asmx/GetTableData",
"fnServerData": function (sSource, aoData, fnCallback) {
aoData.push({ "name": "roleId", "value": "admin" });
$.ajax({
"dataType": 'json',
"contentType": "application/json; charset=utf-8",
"type": "GET",
"url": sSource,
"data": aoData,
"success": function (msg){
var json = jQuery.parseJSON(msg.d);
fnCallback(json);
$("#tblData").show();
},
error: function (xhr, textStatus, error){
if (typeof console == "object") {
console.log(xhr.status + "," + xhr.responseText + "," + textStatus + "," + error);
}
}
});
},
fnDrawCallback: function () {
$('.image-details').bind("click", showDetails);
}
});
function showDetails() {
//so something funky with the data
}
In order to understand what's happening here, it's better if we break it down into smaller pieces. The very first part of the javascript ensures that no ajax queries are cached on the page. Some older browsers are notorious with caching queries (silently), hence returning the same data for a certain period and causing you to lose your mind trying to debug unrelated issues. With this out of the way, we can now initialize our datatable. The first few lines of the initialisation are pretty standard: paging, ordering, size, filter etc. The 4 important properties are:
- bProcessing: true -> instructs the datatable to display a "Processing" message while the ajax call is being executed
- bServerSide: true -> informs the datatable that the data will be coming from the server. If this parameter is set, then sAjaxSource also needs to be defined
- fnServerData: ... -> this anonymous method allows you to override the defaul ajax call and gives you the ability to provide your own implementation, which may be more applicable to your application
The benefit of the fnServerData is that you get the ability to perform a lot of refinements and control the overall dataflow. You can find some more datails here. In this example, I wanted to pass an additional parameter to the GET method. To pass one or more extra parameters to the ajax call, you can use the aoData object. This is an object used internally by DataTables to pass vital information to the server such as sorting, paging, table state etc. Luckily this object is also exposed in order to allow you to push additional data, which comes quite handy if the built-in ajax process doesn't cut it. Each value you pass needs to be in the form of a key/value pair:
aoData.push({ "name": "roleId", "value": "admin" });
In this case the key is "roleId" and the value is "admin").
The ajax call also has a few important parameters you need to be aware of.
- dataType -> must be json
- data -> must be your aoData object
- url ->
sSourceis yoursAjaxSourcewhich is passed as a parameter in the anonymous method
Finally, the fnDrawCallback parameter is initialised to ensure that the click event for each image on each row is bound correctly. If you have any jQuery events bound to elements in your table, then you have to bind them as part of the fnDrawCallback DataTable event. If you fail to do this, your jQuery events will break after the first ajax call.
3. Server-Side code
You may have noticed that instead of using a WebAPI I have an .asmx web service. Ideally, a WebAPI would have been my first choice, but since this service was already in place, I decided to roll with it. There is also a User class which I haven't included as it's not vital for this tutorial.
[WebService(Namespace = "http://yourawesomesite.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
[WebMethod(EnableSession = true)]
[ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
public string GetTableData()
{
var echo = int.Parse (HttpContext.Current.Request.Params["sEcho"]);
var displayLength = int.Parse(HttpContext.Current.Request.Params["iDisplayLength"]);
var displayStart = int.Parse(HttpContext.Current.Request.Params["iDisplayStart"]);
var sortOrder = HttpContext.Current.Request.Params["sSortDir_0"].ToString(CultureInfo.CurrentCulture);
var roleId = HttpContext.Current.Request.Params["roleId"].ToString(CultureInfo.CurrentCulture);
var records = GetRecordsFromDatabaseWithFilter();
if (records == null)
{
return string.Empty;
}
var orderedResults = sortOrder == "asc"
? records.OrderBy(o => o.SequenceNumber)
: records.OrderByDescending(o => o.SequenceNumber);
var itemsToSkip = displayStart == 0
? 0
: displayStart + 1;
var pagedResults = orderedResults.Skip(itemsToSkip).Take(displayLength).ToList();
var hasMoreRecords = false;
var sb = new StringBuilder();
sb.Append(@"{" + "\"sEcho\": " + echo + ",");
sb.Append("\"recordsTotal\": " + records.Count + ",");
sb.Append("\"recordsFiltered\": " + records.Count + ",");
sb.Append("\"iTotalRecords\": " + records.Count + ",");
sb.Append("\"iTotalDisplayRecords\": " + records.Count + ",");
sb.Append("\"aaData\": [");
foreach (var result in pagedResults)
{
if (hasMoreRecords)
{
sb.Append(",");
}
sb.Append("[");
sb.Append("\"" + result.ID + "\",");
sb.Append("\"" + result.Name + "\",");
sb.Append("\"" + result.Address + "\",");
sb.Append("\"" + result.Age + "\",");
sb.Append("\"<img class='image-details' src='images/details-icon.png' runat='server' height='16' width='16' alt='View Details'/>\"");
sb.Append("]");
hasMoreRecords = true;
}
sb.Append("]}");
return sb.ToString();
}
}
This code may seem a bit weird but there is some logic behind it. First of all, you need to make yourself familiar with the DataTables server-side documentation because it is important to understand the parameters sent back and forth with every request. Read the details here. Notice that the aaData parameter expects an array of arrays with an entry for each column on the datatable. This is particularly important because if your data format is not right your ajax call will fail :(
Finally you need to ensure that your response contains all the necessary parameters, as per the documenation, in order to allow your DataTable to render the data correctly.
Sample Code
II created a small ASP.NET WebForms project that implements the code found in this post. You can download from GitHub. There are some very small, subtle differences between the code in the blog and the GitHub project so make sure you let me know if you come across any issues.
I hope this helps you get your DataTable to work with your ASP.NET website using server-side processing. Let me know if you experience any issues and if you have found a better way to implement this.
P.S Make sure you follow me on Twitter @christosmatskas for more up-to-date news, articles and tips.