It has been ages since I have posted a software development post. The last week, I have been working on some MVC 4 code and had to figure out how to do server-side support for the most excellent DataTables package. This, of course, triggers off AJAX requests whenever data is required to populate the on-screen client-side gridview table.
If you are using the DataTables package prior to v10 or use it in compatibility mode then you don’t have many problems because the JSON generated to the AJAX service is not built with arrays and complex data. This compatibility is triggered by using the sAjaxSource parameter or by setting $.fn.dataTable.ext.legacy.ajax to true. However, what is the point in a new project to depend on backwards compatibility that may go away in the next version of the library?
The server side support for DataTables is documented here. Information on how to use it in v10+ mode with MVC is slim on the net so one has to cobble together a solution based upon several different sources especially if one plans to create a simple ApiController to handle the AJAX callbacks. I am sharing what I have discovered for whatever it is worth to anyone facing the same hurdles I did and hope that it does help.
First of all, here is the page code for the DataTables on the website:
.
.
.
<!-- Kick off rendering of DataTable -->
<script type="text/javascript" language="javascript">
$(document).ready(function () {
$('#CountryTable').DataTable({
serverSide: true,
ajax: {
url: '/api/data',
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: function(data)
{
return data = JSON.stringify(data);
}
}
});
});
</script>
<h2>Countries</h2>
<table id="CountryTable" class="display" cellspacing="0" width="800px">
<thead>
<tr>
<th>ID</th>
<th>Code</th>
<th>Country name</th>
<th>Currency</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
.
.
.
Let me share a couple comments concerning the code above. Notice that DataTables wraps the standard table (CountryTable) which, as you will notice does not have any body items. This, and the ajax verb in the Javascript code, will trigger DataTables to work in the server-side mode. Notice that the ajax function is defined with a type of POST (we will capture this on the server side) and the contentType MUST be specified otherwise the string will not be interpreted by the ASP.NET side as being JSON. One of the most common mistakes in working with JSON in either direction is to miss setting the JSON content type and the resulting string is interpreted as just plain text by the AJAX mechanism.
So, how to capture and process this AJAX request in MVC 4? There are several options, of course, but I chose to vector through the standard ApiController.
public class DataController : ApiController
{
public HttpResponseMessage Post(Newtonsoft.Json.Linq.JObject param)
{
return HandleRequest(DataTableRequestModel.DecodeDataTableRequest(param));
The AJAX call to /api/data will vector to look for a controller named DataController in MVC. This controller extends ApiController and handles the POST request by exposing a Post method. The method could be named something else and merely be decorated with an [System.Web.Mvc.HttpPost] attribute. Dealer’s choice on that one.
Notice that I capture the parameter as a JObject. It could have been created like this:
public HttpResponseMessage Post(DataTableRequestModel param)
which would have decoded the non-complex items automatically using the Model class below but there is no way to decode the complex items out of the box. If one feels really adventurous, it is possible to write your own binder code but since the JSON from DataTables is not really too complex, I chose to handle it externally. The JObject is initialized with the JSON from the call (since the content type was set to application/json) and so the data is ready to be plucked from the object. In the Models folder, I created a generic DataTableRequestModel with the fields expected from the request:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MVCGridTester.Models
{
// See article by Chris Laforet (claforet.wordpress.com)
// Sample request sent JSON stringified:
//
//{"draw":1,"columns":[{"data":0,"name":"","searchable":true,"orderable":true,"search":
//{"value":"","regex":false}},{"data":1,"name":"","searchable":true,"orderable":true,"search":
//{"value":"","regex":false}},{"data":2,"name":"","searchable":true,"orderable":true,"search":
//{"value":"","regex":false}}],"order":
//[{"column":0,"dir":"asc"}],"start":0,"length":10,"search":{"value":"","regex":false}}
//
/// <summary>
/// Class that encapsulates common parameters sent by DataTables plugin
/// </summary>
public class DataTableRequestModel
{
/// <summary>
/// Since the DataTableRequest includes arrays of data elements (columns, order), it cannot be
/// correctly parsed out in its entirety by the native API which only handles single root-level
/// elements. This static call can take the raw JSON object and populate the DataTableRequestModel
/// correctly.
/// </summary>
/// <param name="param">The passed in JSON object from the POST</param>
/// <returns>A DataTableRequestModel populated with the correct data</returns>
public static DataTableRequestModel DecodeDataTableRequest(Newtonsoft.Json.Linq.JObject param)
{
dynamic json = param; // recast the parameter as a dynamic object
DataTableRequestModel dtr = new DataTableRequestModel();
dtr.draw = json.draw;
dtr.start = json.start;
dtr.length = json.length;
dynamic columns = json.columns;
if (columns != null)
{
List<DTRColumn> list = new List<DTRColumn>();
foreach (dynamic token in columns.Children())
{
DTRColumn c = new DTRColumn();
c.data = token.data;
c.name = token.name;
c.searchable = token.searchable;
c.orderable = token.orderable;
dynamic columnSearch = token.search;
if (columnSearch != null)
{
DTRSearch s = new DTRSearch();
s.value = columnSearch.value;
s.regex = columnSearch.regex;
c.search = s;
}
list.Add(c);
}
dtr.columns = list;
}
dynamic order = json.order;
if (order != null)
{
List<DTROrder> list = new List<DTROrder>();
foreach (dynamic token in order.Children())
{
DTROrder o = new DTROrder();
o.column = token.column;
o.dir = token.dir;
list.Add(o);
}
dtr.order = list;
}
dynamic search = json.search;
if (search != null)
{
DTRSearch s = new DTRSearch();
s.value = search.value;
s.regex = search.regex;
dtr.search = s;
}
return dtr;
}
/// <summary>
/// Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables
/// </summary>
public int draw { get; set; }
/// <summary>
/// Paging first record indicator. This is the start point in the current data set (0 index based - i.e. 0 is the first record).
/// </summary>
public int start { get; set; }
/// <summary>
/// Number of records that the table can display in the current draw. It is expected that the number of records returned
/// will be equal to this number, unless the server has fewer records to return. Note that this can be -1 to
/// indicate that all records should be returned
/// </summary>
public int length { get; set; }
/// <summary>
/// Comma separated list of orderable column data.
/// </summary>
public IEnumerable<DTROrder> order { get; set; }
/// <summary>
/// Comma separated list of column data
/// </summary>
public IEnumerable<DTRColumn> columns { get; set; }
/// <summary>
/// Global search value. To be applied to all columns which have searchable as true.
/// </summary>
public DTRSearch search { get; set; }
}
/// <summary>
/// Encodes an Order from within the Data Table Request
///
/// "order":
/// [
/// {"column":0,"dir":"asc"}
/// ]
/// </summary>
public class DTROrder
{
public const string ORDER_ASCENDING = "asc";
public const string ORDER_DESCENDING = "desc";
public int column { get; set; }
public string dir { get; set; }
public bool IsAscending
{
get
{
if (dir != null && dir.CompareTo(ORDER_ASCENDING) == 0)
return true;
return false;
}
}
}
/// <summary>
/// Encodes a Search within a Column from the Data Table Request
///
/// "search": {"value":"","regex":false}
/// </summary>
public class DTRSearch
{
public string value { get; set; }
public bool regex { get; set; }
}
/// <summary>
/// Encodes a Column from the Data Table Request
///
/// "columns":
/// [
/// {"data":0,"name":"","searchable":true,"orderable":true,
/// "search": {"value":"","regex":false}
/// },
/// {...}
/// ]
/// </summary>
public class DTRColumn
{
public string data { get; set; }
public string name { get; set; }
public bool searchable { get; set; }
public bool orderable { get; set; }
public DTRSearch search { get; set; }
}
}
Not a lot of complication here. Notice how the array and complex types are handled by simple container classes. In order to populate the data, it is merely a matter of walking the JSON string and loading the contents of the model. Many of the ideas in the parser were developed based upon this blog article by Rick Strahl so if you want to delve deeper into the how and the why everything works, give it a read.
At the end of the process, one has an object containing every element in the DataTable’s request. These items can be actioned by the data manipulation code (e.g. selecting, sorting, filtering) and returned as a JSON string. The implementation of this data manipulation and the construction of the return string are left to the reader.
So, that’s all there is to it. Happy coding!
