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">
  <thead>
    <tr>
      <th>Name</th>
      <th>Age</th>
   </tr>
  </thead>
  <tbody>
    <tr>
      <td>John Doe</td>
      <th>25</th>
    </tr>
  </tbody>
</table>
$(function(){
  $('#PeopleListTable').dataTable();
});

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:

sEcho:35
iColumns:7
sColumns:
iDisplayStart:0
iDisplayLength:10
mDataProp_0:FirstName
mDataProp_1:LastName
mDataProp_2:BirthDateFormatted
mDataProp_3:BirthDate
mDataProp_4:Weight
mDataProp_5:Height
mDataProp_6:Children
sSearch:
bRegex:false
sSearch_0:
bRegex_0:false
bSearchable_0:true
sSearch_1:
bRegex_1:false
bSearchable_1:true
sSearch_2:
bRegex_2:false
bSearchable_2:true
sSearch_3:
bRegex_3:false
bSearchable_3:true
sSearch_4:
bRegex_4:false
bSearchable_4:true
sSearch_5:
bRegex_5:false
bSearchable_5:true
sSearch_6:
bRegex_6:false
bSearchable_6:true
iSortCol_0:1
sSortDir_0:asc
iSortingCols:1
bSortable_0:true
bSortable_1:true
bSortable_2:true
bSortable_3:true
bSortable_4:true
bSortable_5:true
bSortable_6:true

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.

Sorting
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.

Filtering
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
sEcho:35
iColumns:7
sColumns:
iDisplayStart:0
iDisplayLength:10
mDataProp_0:FirstName
mDataProp_1:LastName
sSearch: john
iSortCol_0:1
sSortDir_0:asc

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.

Conclusion
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:
https://github.com/garvincasimir/csharp-datatables-parser

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s