One of the many things I love about WPF is its native support for UI virtualisation. Bea Stollnitz used this to produce an excellent data virtualisation solution some time ago (which I might add I used with great success in a large-scale WPF project last year). Recently I’ve been getting back into web development proper (aka drowning in JavaScript) and was asked to come up with a way of presenting large amounts of data in a table without the user having to go to the effort of clicking on page numbers to move through it. Given that paged grids are the generally accepted way of presenting large volumes of data on the web, I was interested to see how this could be done without recourse to browser plug-ins (specifically Silverlight). What I discovered is that it’s not an easy thing to do but then a colleague put me on to the excellent DataTables, created by Allan Jardine – a man who must surely never sleep, given the effort he’s put into both creating the control and supporting it. I can’t recommend this control enough – having had the ordeal of dealing with a certain component vendor who assured me their product could do data virtualisation (as opposed to just UI virtualisation) only to discover that it couldn’t – DataTables has saved me from a lot of pain and suffering.
The only difficulty is that I’m working with ASP.NET MVC as a back-end, while most of the examples on the DataTable’s website use PHP. Also, I have requirements for row selection (and persistence of this across page reloads), check box columns, and master/detail tables. Go forward a few months and I’ve managed to put together some MVC-specific helper classes and write a few plug-ins to handle these requirements. I should really publish it to GitHub or CodePlex, but that would involve me having to maintain codebases and all sorts of other things that I’d love to do if I had any spare hours in the day (although I’ll probably do it anyway). So instead I’ve knocked up a sample project that should provide some useful helpers and plug-ins for anyone in a similar boat.
The sample project can be found here. It’s a Visual Studio 2010 solution (but can be opened using the Express editions if you build the projects separately and re-reference them) with a SQL file that needs to be run to provide the sample data. It demonstrates a data-virtualised row-selectable master table with 1000 rows and a similarly data-virtualised detail table with selectable rows and a check box column. I strongly recommend downloading the sample as it drastically shortens the learning and development curve involved in getting MVC working happily with DataTables. I’m assuming some familiarity with DataTables (did I mention the documentation is excellent?) but usage can be easily inferred from the sample project.
MVC server-side helpers
The server-side helpers consist of:
- A model binder to convert a request from a client-side DataTable into an object (of type ClientRequest) that can be easily interrogated to build the return dataset.
- A response generator which converts the result dataset into the ‘correct’ type of JSON for the client-side DataTable.
- A JSON generator for converting server-side model classes into client-side column definitions (specifically the DataTable’s aoColumns object).
In addition there are some LINQ helpers, the main purpose of which is to provide slightly neater factoring of data access code (since the amount of code needed to implement them is quite a lot). They are, to use a turn of phrase, “beyond the scope of this post”. Basically the helper classes are just some scaffolding to get MVC to return the data that’s been asked for in the right format.
Firstly we need to add the model binder to the RegisterRoutes method of Global.asax.cs:
public static void RegisterRoutes(RouteCollection routes)
{
// routes and other stuff here
ModelBinders.Binders.Add(typeof(ClientRequest), new ClientRequestModelBinder());
}
Then we need to create a controller action to return the data (which uses the ClientRequest model binder to process the incoming request):
public ContentResult GetMyData(ClientRequest model)
{
string json = null;
if (model != null)
{
// get data and total size of dataset
var serverResponse = new ServerResponse<MyModelClass>(
model.Echo, totalCount, true, data);
json = serverResponse.GetJson();
}
return Content(json);
}
Note that the action returns a ContentResult (rather than a JsonResult). This is because DataTables is quite pedantic about the format of the Json that gets returned, hence the use of the ServerResponse<T> helper which creates a string in the correct Json format that can be sent verbatim to the client.
As an optional extra we can also send the column format data (for which we can use the standard Json serialisation):
public JsonResult GetMyModelClassColumnDefs()
{
var columnDefs = ClientColumnGenerator.GetColumnDefs(typeof(MyModelClass));
return Json(columnDefs, JsonRequestBehavior.AllowGet);
}
The main advantage to getting the column definitions this way is that it removes duplication of model definitions (and by extension provides a more maintainable way of keeping client and server-side models synchronised). However the reason I first did it was because it’s less tedious than manually creating all that mDataProp stuff in object literals on the client-side. As a minimum the GetColumnDefs method will return an object literal with the mDataProp value for each public property in the server-side model, however there are a number of attributes that can be placed on the server-side model to provide the values for the following client-side properties:
| aoColumns property |
MVC attribute |
| bSearchable |
Searchable |
| bSortable |
Sortable |
| sClass |
ColumnCssClass |
| sTitle |
Display |
| bVisible |
ColumnVisible |
The column definitions can be retrieved on the client-side by using jQuery’s getJson function. This is how the sample application does it, although it requires some nesting when using master/detail tables.
Client-side plug-ins
The client-side plug-ins are contained in their own script files, however the checkbox and row selector plug-ins make use of the ArrayHelper class (so that will need to be included in any deployment). I also have a number of miscellaneous DataTable helper methods (in the DataTableHelper.js file) which are not necessary for the plug-ins but are used in the sample project. The plug-in files can be downloaded separately here.
I’ve included vsdoc code comments, so Visual Studio 2008/2010 or Expression Web users will get IntelliSense.
Row selection plug-in
Before I start I should point out that this plug-in was ‘inspired’ (as in half-inched) from Iuliandum's method for row selection (as detailed here). All I’ve done is create an sDom wrapper around it.
The row selection plug-in supports three modes:
- Single
Zero or one rows can be selected at any one time
- Single must select
One row must always be selected (unless there are no rows in the table). If the user hasn't selected a row then the first row will be selected by default.
- Multiple
Zero or more rows can be selected concurrently
It also has support for bStateSave and if so configured will remember selected rows between page reloads.
To start you need to create an instance of RowSelectionInfo. The first parameter is an array listing all the key fields (an array facilitates using composite keys), the second parameter is the selection mode (as described above), the third parameter is the CSS class to apply to the row when it’s selected, and the fourth parameter is the callback function for when a row is selected:
var rowSelector = new RowSelector.RowSelectionInfo(['Id'],
RowSelector.SelectionModes.SingleMustSelect, 'selected',
function (nRow, aData, isSelected, oTable) {
// do something on row selected. Callback arguments as follows:
// nRow: The DOM selector for the row
// aData: The underlying data in the row
// isSelected: indicates whether the row was selected or unselected
// oTable: a reference to the owning DataTable
});
Then just pass it into the selectionInfo property of the DataTable’s initialisation object and include an ‘R’ in the sDom parameter (so that DataTables knows to initialise the plug-in):
var invoiceDataTable = invoiceTable.dataTable({
"sScrollY": "400px",
"sScrollX": "100%",
"sAjaxSource": invoiceDataSource,
"bServerSide": true,
"sDom": 'rtiSR',
"aoColumns": invoiceColumnDefs,
"bStateSave": true,
"selectionInfo": rowSelector
});
By encapsulating handling of row selection in a separate object it becomes easier to make use of closures when dealing with the inherently asynchronous nature of a DataTable with a server-side source. The RowSelectionInfo object exposes functions for getting at the key values of the currently selected rows and (in the case of single row selection) a method for getting at the underlying data.
Master/detail plug-in
This plug-in handles the plumbing of keeping two tables with a common field(s) synchronised with each other – so that when rows in the master table are selected, the detail table is filtered accordingly. The reason this is usually so awkward is because the detail table can’t be instantiated until the master table has finished initialisation. The plug-in ensures that all tables are set up in the right order (and thus has support for nested tables – so a detail table could be the master table for another detail table). Note: this plug-in won’t work without the row selector plug-in.
First you need to define the link keys. These are the fields common to both tables on which the detail table will be filtered. As the plug-in supports linking on multiple fields, these take the form of an array (of MasterDetail.LinkKey objects):
var linkKeys = [new MasterDetail.LinkKey('Id', 'ParentId')];
Then declare an instance of MasterDetail.LinkSettings, in which the first parameter is the link keys, the second parameter is the jQuery selector for the HTML table, and the last parameter is the initialisation object that you would normally pass in if you were using the standard DataTable constructor:
var linkSettings = new MasterDetail.LinkSettings(linkKeys, $('#myTable'), {
"sAjaxSource": lineItemsDataSource,
"bServerSide": true,
"sDom": 'rtiSRC',
"aoColumns": lineItemColumnDefs
});
Once this is done just pass it into the masterDetailInfo property of the master table’s initialisation object and include an ‘M’ in the sDom property (after the ‘R’ for the row selector):
var masterTable = $('#myMasterTable').dataTable({
"sAjaxSource": masterTableSource,
"bServerSide": true,
"sDom": 'rtiSRM',
"aoColumns": columnDefs,
"bStateSave": true,
"selectionInfo": masterSelector,
"masterDetailInfo": linkSettings
});
Checkbox column plug-in
Slightly less useful than the other two, but handy nonetheless is the check box plug-in. It allows you to render one column as a checkbox and keep track of which rows the checkbox has been selected on. The actual rendering of the checkbox is just an override on the native DataTables fnRender function, but the plug-in has the added benefit of encapsulating the tracking.
To use the plug-in, first declare an instance of CheckBoxSelectionInfo. The first parameter is the column in which the checkbox should appear (it won’t render the actual data), the second is the key field array, the third is an optional CSS class to be applied to the actual checkbox, and the last one is the on checked/unchecked callback:
var checkBoxColumn = new CheckBoxColumnn.CheckBoxSelectionInfo(
0, ['Id'], 'checkboxcolumn', function (nRow, aData, isSelected, oTable) {
// Callback arguments as follows:
// nRow: The DOM selector for the row
// aData: The underlying data in the row
// isSelected: indicates whether the checkbox was selected or unselected
// oTable: a reference to the owning DataTable
});
Then just pass that into the DataTable’s initialisation object’s checkBoxColumnInfo property and include a ‘C’ in the sDom property:
var dataTable = $('#myTable').dataTable({
"sAjaxSource": dataSourceUrl,
"bServerSide": true,
"sDom": 'rtiSC',
"aoColumns": columnDefs,
"bStateSave": true,
"checkBoxColumnInfo": checkBoxColumn
});
As with the row selector plug-in, the CheckBoxSelectionInfo object exposes functions for getting at the key values of the checked rows.
