There are several JQuery plug-ins that provide the basic functionality of an html grid: paging, sorting, etc. In essence these plug-ins provide a consolidated display of the data and typically means of which to interact with the data (update, insert, delete, etc). I recently had the opportunity to work with the DataTables.net grid and have been very impressed.
The following post describes the steps I used to make the DataTables grid work in an ASP.NET Webforms environment using a WCF Service as an endpoint. I’ll also go over some of the pitfalls I ran into which required a bit of ‘tweaking’ to get things working smoothly. One cool thing was that for every roadblock, a workaround was quickly discovered. This is huge when investing any time or resources on any third party plug-in or control.
First up, if you are not aware of what the DataTables is, it’s a JQuery Plug-in that written by Allan Jardine. According to the DataTables website:
“DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table. “
It offers a plethora of features including pagination, filtering, sorting, auto loading of data via Ajax, Fully theme-able by CSS, etc. The documentation on the DataTables website is thorough and the quality excellent. There are also forums…good stuff. Additionally DataTables is licensed dually under the GPL v2 License and the BSD license for those who inquire…
Here is an example of Datatables with built in JQuery UI theme support:
The WCF Service
Datatables allows you to either have client-side paging, sorting and searching, which is the default, or you can do it on the server. Doing it on the client is the simplest and easiest way to get up and running with Datatables, however you will quickly find that it leads to larger page sizes and longer initial load times as all of the data must be piped from the server before Datatables is ready. Performing the actions on the server makes for much a much snappier and scalable UI, but it adds complexity.
In order to use a WCF service with DataTables, you have to both honor the signature that DataTables expects and the data must be returned in a format that DataTables expects. Fortunately this is relatively easy to do; you simple need to a) map the HTTP variables from the GET request and b) generate a JSON string matches a specific format.
The HTTP variables that DataTables will post are as follows:
Note that the websiteId and categoryId are custom variables and that for each column specified, additional variables will be posted for the following fields with a consecutive numerical postfix: bSearchable_0, bSearchable_1, bSearchable_2, etc.
For my example the following WCF service method was used:
Note that I only specified a subset of the parameters in the WCF method that Datatables posts with its GET request:
Too be honest, I do not know why Datatables sends so many (looks like one variable per option per column for most), but from the WCF service method perspective they are optional. Also, note that Datatables uses some form of Hungarian notation to qualify the variables type. While a bit confusing at first, it did help a bit when debugging. The variables that we did specify in the WCF service method signature will be used for sorting, filtering, paging, etc.
Datatables expects to receive a JSON object with a two dimensional array called “aData” from this service. Here is an example of the server output that Datatables will receive:
Where “d” is the wrapper type appended by WCF and from the Datatables website:
Basically, note that the service must correctly compute and return the “iTotalRecords”, “iTotalDisplayRecords” and the data in “aaData”. Also, the length of each data returned must match the DataTables jQuery definition provided on the web page where the data will be displayed.
Here is the complete code listing for the internals of the WCF service method:
Note that depending your architecture, this code may look considerable different. However the steps are same:
- Query the datastore based upon the passed in parameters
- Calculated paging values
- Column to search by
- Column to sort by
- Create a JSON return result matching the JSON structure defined above.
Another thing to note here is that Datatables does not send column names; it sends the ordinal of the column with respect to the table. In order to sort by a column or filter by a column, you need to resolve the ordinal to a column name of your datastore:
The GetSortFieldFormOrder(int order) returns a string column name based upon the ordinal of the column so that the datastore (in this case via a sproc call) can sort the data by the correct column.
The Convert(object record) method makes an array from the values of properties of an object: each becomes an “aaData” record that is returned and used by Datatables on the client.
Just an architecture note here: in my opinion there is too much “busy” work going on here at the service level. Ideally this will be refactored into your entities so that the behavior is encapsulated outside of your data “delivery”.
The Client Code
The client JQuery code for Datatables can be very simple or rather complex depending upon the formatting and features your situation requires. Below you will see the code that I used and I will explain the major initialization required for using Datatables with a WCF service.
Lines 4-6 I get a reference to the DOM element I will load into using a typical JQuery selector. Let me explain the relevant settings:
A. I am using the Datatables built in support for JQuery UI Themes
B. I am setting the “bServerSide” flag to true so that Datatables will use AJAX to get the data source
C. The URL of the AJAX method to call for loading data
D. Since I want to set some custom variables to post to my service method and I want to override the default HTTP POST method that Datatables uses for invoking the AJAX method, I am overriding the “fnServerData” callback with a custom implementation.
E. Datatables uses POST be default, I overridden the AJAX method to use a GET
F. The results from the server are then passed into the fnCallBack callback and Datatables can now render the results.
The target for the selector is a div element on an ASP.NET UserControl:
