C# datatables parser

The jQuery Datatables plugin is a very powerful javascript grid plugin which comes with the following features out of the box:

  • filtering
  • sorting
  • paging
  • jQuery ui themeroller support
  • plugins/extensions
  • Ajax/Remote and local datasource support

Setting up datatables on the client is very simple for basic scenarios. Here is an example of the markup and the initialization code.

<table id="PeopleListTable">
      <td>John Doe</td>

Server Side Processing
The Datatables plugin supports loading table data, paging, sorting and filtering via ajax. Datatables sends a specific set of parameters which the server is expected to process and return the result in json format. Here is a sample of the request parameters sent via ajax:


For a detailed description of each parameter please see the datatables.net documentation

mDataProp_n Parameters
Datatables supports displaying columns in any order in the table by setting the mProp property of a column to a specific property in the json result array. For each column, it sends a parameter in the format ‘DataProp_columnIndex = propertyName’. As we can see in our example above, FirstName is the mProp of the first column in the table. It is important to understand these column index property mappings because the sorting and filtering parameters rely on them being interpreted properly.

Datatables has a global setting called bSort which disables sorting for the entire table. It also has a property called bSortable which enables/disables sorting for a specific column. For each column, the server side script should search for a parameter in the format ‘bSortable_columnIndex = true/false’. Sorting is determined by parameters with the formats ‘iSortCol_sortCount = columnindex’ and ‘sSortDir_sortCount = asc’ where ‘sortCount’ is the order of sorted parameters and ‘asc’ is the direction that the specific column should be sorted.

Datatables has a global setting called bFilter which disables filtering for the entire table. It also has a property called bSearchable which enables/disables filtering for a specific column. For each column, the server side script should search for a parameter in the format ‘bSearchable_columnIndex = true/false’. Filtering works by searching all the searchable columns in a row for any value which contains the filter value in the format ‘sSearch = findMe’. There is also support for filtering on specific columns by using the parameters in the format ‘sSearch_columnIndex = findMe’.

The c# Datatables Processor
The parser is a generic class with implements most of the server side features of the Datatables plugin in a reusable manner with special emphasis on performance. For example, an application which requires grids for people, cities and shopping lists does not require special logic for sorting and filtering each entity type because Datatables dynamically generates the expressions required to support these functions. If our first client side example was configured to use server side processing it would probably look like this:

 <table id="PeopleListTable"></table>
        $(function () {
            var peopleList = $('#PeopleListTable').dataTable({
                bServerSide: true,
                bProcessing: true,
                sServerMethod: "POST",
                sAjaxSource: "@Url.Action("All", "Person")",
                aoColumns: [
                    { mData: "FirstName", sTitle: "First Name" },
                    { mData: "LastName", sTitle: "Last Name"}
public JsonResult All()
    var context = new PeopleEntities();
    var parser = new DataTablesParser<Person>(Request, context.People);

    return Json(parser.Parse());

With the above combination of markup, javascript and 3 lines of server side code you have the ability to render a very rich and responsive grid in little time.

Entity Framework Performance
The parser supports two separate scenarios which are determined by the provider of the Iqueryable supplied to its constructor; The simple case where all/most processing is handled in memory via Linq to Objects and the more complex case where most/all processing is handled on the database server via Linq to SQL. In linq to sql support we ensure all the expressions sent to entity framework are translatable to valid tsql statements. The goal here is to avoid the cost of bringing most/all the data across the wire and into memory for processing. Imagine a grid for a dataset with 2 million records where you pull in all 2 million records from the database only to send 10 to the client.

As an example the following sql statement should be the result of the request it precedes. All the sorting, filtering and paging parameters have been translated and are represented in the tsql statement.

  SELECT TOP (10) [Filter1].[Id] AS [Id], 
                 [Filter1].[FirstName] AS [FirstName], 
                 [Filter1].[LastName] AS [LastName]
 FROM ( SELECT [Extent1].[Id] AS [Id], 
               [Extent1].[FirstName] AS [FirstName], 
               [Extent1].[LastName] AS [LastName], 
               row_number() OVER (ORDER BY [Extent1].[FirstName] ASC) AS [row_number]
               FROM [dbo].[People] AS [Extent1]
               WHERE ([Extent1].[FirstName] LIKE N'%john%') 
                     OR ([Extent1].[LastName] LIKE N'%john%')\r\n)  AS [Filter1]
 WHERE [Filter1].[row_number] > 0
 ORDER BY [Filter1].[FirstName] ASC
sSearch: john

The ‘iDisplayStart’ property determines the start of a page of data and iDisplayLength determines the length of each page of data.

Where is X feature?
The biggest feature missing from the parser is processing individual search filters. Originally, the individual property search and the generic search were implemented as two separate functions. However, I am convinced that the bulk of the logic in the generic search can be generalized to also handle the individual property such. I am open to any ideas on this one. I have also been asked about sorting/filtering on sub properties. This should be possible in linq to objects but I have not been able to look into it.

The parser is definitely a work in progress in the sense that it is always being improved whenever possible but it certainly saves time when using the datatables plugin for grids.

The parser can be added to your project via npm using the following command:

PM> Install-Package DataTablesParser

Please note that the NPM version up until the publishing of this post does not have the most up to date fixes and changes. I plan to update the NPM package as soon as the new changes have been thoroughly tested.

You can get the latest code or send pull requests at the github repository here:


Dotnetnuke Services Framework and AjaxWebLinks

The good people at Dotnetnuke have release a new ctp which allows module developers to add external api to their modules. You can view the announcement made by Bob Kruger here and a short tutorial from Scott Schlesier here.

I created a branch of my AjaxWebLinks sample module based on asmx webservices and converted it to use the new framework. I think there are some things that can be improved but my overall experience has been good so far. I like the fact that the services work without any sort of registration. When the Dotnetnuke application starts it searches for all classes which implement the special routing interface and registers the routes to the services.

One thing that wasn’t mentioned in the tutorial (or I somehow missed it) is your request requires a “TabId” and a “ModuleId” header to check permissions successfully.

Here is a sample Ajax request from my converted module:

        type: "POST",
        beforeSend: function (xhr) {
            xhr.setRequestHeader("ModuleId", AjaxWebLinksModuleID);
            xhr.setRequestHeader("TabId", AjaxWebLinksTabID);
        url: "/DesktopModules/AjaxWebLinks/API/Handler/GetLinks",
        data: {},
        dataType: "json",
        success: function (data) {

        error: function () {

Download the sample module and source here

Jquery AjaxWeblinks Module for Dotnetnuke released

After a very long time I was able to finish the first version of the AjaxWeblinks module which demonstrates a method for using jQuery/Ajax functionality in a Dotnetnuke module. For now, there can only be one instance of the module on any given page. I will release another version soon which will have better scoped and enqueued javascript.

The packages are available here.

Dotnetnuke Ajax WebLinks module on Codeplex

I promised to release the complete code for the Dotnetnuke Ajax WebLinks module I have been working on. It was supposed to demonstrate a method for including jquery ajax functionality in dotnetnuke modules. However, I have not had time to finish it and write up the last two posts. In the mean time I posted the project on codeplex so people can view all the nearly complete source code. When I get some time soon I will finish this one and possibly create a version for Dotnetnuke 6.x if there isn’t already something built in that makes it unnecessary.

It can be found here