Tags
The main DataTables.js API call is straightforward:
function SetupDataTable(data) {
var c = JSON.parse(data);
var url = ResolveUrl("~/Search/JsonResults");
table = $("#SearchResultsTable").DataTable({
"pagingType": "full_numbers",
"pageLength": 10,
"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
"processing": true,
"serverSide": true,
"ajax": url,
"columns": c,
});
Aside from calling the API to render the DataTable, this JavaScript function makes a request to the /Search/JsonResults controller action when a page load is triggered. The controller action, in turn, will fetch the subsequent x number of records from the source. Set the ‘serverside‘ attribute to ‘true‘ for this.
The (abbreviated) controller action for this originally looked something like:
Ordering results in DataTables should be easy, yes? Actually, no, it wasn’t, since the application here used a single DataTable instance to present tabled data for doctors, organisations, third-party providers, other personnel, sites, tests, etc. In this case, the data fields/columns are defined by the JSON response from the controller, and they’ll change according to the dataset set being selected by the user.
This was my initial code, simply to get a working LINQ query and to create a placeholder for a dynamic variable:
var rows = results.DataSet.AsEnumerable().OrderBy(r =>
r.Field("Organisation Postcode")).Skip(model.start - 1).Take(model.length);
The above is a LINQ statement to sort the data (ascending) by Organisation Postcode – I picked that variable arbitraily from the JSON response. We use DataSet to create an in-memory representation of the data being fetched, so the DataAdapter would limit the read volume to the number of records being requested by DataTables.js. LINQ is used to query this in-memory data instead of the database itself.
What we need to do now is replace the ‘Organisation Postcode‘ with something that could dynamically represent any column name in the JSON response. The LINQ queries I ended up with were:
var sortedResults = order.column == 0 ? results.DataSet.AsEnumerable() :
order.dir == "desc" ? results.DataSet.AsEnumerable().OrderByDescending(o=> o.ItemArray[order.column] is DBNull ? DateTime.MinValue : o.ItemArray[order.column]) :
results.DataSet.AsEnumerable().OrderBy(o => o.ItemArray[order.column] is DBNull ? DateTime.MinValue : o.ItemArray[order.column]);
And the Skip() and Take() operations have been shifted to the row creation loop:
Here we have roughly the same thing as before:
results.DataSet.AsEnumerable().OrderBy()
Passing a somewhat different LINQ statement:
o => o.ItemArray[order.column] is DBNull ? DateTime.MinValue : o.ItemArray[order.column]
In the first part, ‘o => o.ItemArray[order.column]‘, ‘o‘ represents the DataRow in the DataSet. It means pick value ‘order.column‘ from the ItemArray, and order the output by that value.
The local variable is derived from:
var order = new Order() { column = int.Parse(Request["order[0][column]"]), dir = Request["order[0][dir]"] };
This line, in turn, creates a new instance of the following class:
public class Order
{
public int column { get; set; }
public string dir { get; set; }
}
Now, it might be the case that null values are returned for a column, which screws the ability to pick that column as a sorting criteria, so we use the following to populate the null fields with whatever is derived from DateTime.MinValue:
is DBNull ? DateTime.MinValue
The above is a short-hand way of creating an if/then statement. If the value is null, set the value to DateTime.Min.
Note: Parsing JSON Response to Get Columns
There is another method in the SearchController:
Set the variable ‘result‘ simply as the ‘[‘ character to begin with, to mark the beginning of an array.
In the next instruction, if the result is not ‘[‘, in other words, if there’s already something in the array, we append a comma to mark the end of the last element before executing the next instruction. If we’re not at the start of an empty array, ‘result‘ is appended with:
{ "data": "{0}", column.logical_name }
The ‘column.logical_name‘ refers to a string coming from the database table that identifies it as a column name.
After the array has been populated, ‘]‘ is appended to ‘result‘, to mark the end of the array, and it is returned to the calling function as a JSON response.
