Using Datatables.net JQuery Plug-in with WCF Services

by jmorris 17. May 2010 13:42

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:

  1. Query the datastore based upon the passed in parameters
    1. Calculated paging values
    2. Column to search by
    3. Column to sort by
  2. 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:

 

   kick it on DotNetKicks.com

Tags: , , ,

Comments (21) -

Shun
Shun Mexico
5/26/2010 11:47:39 AM #

Great!!... Could you post the source code to download??

paul mungai
paul mungai Kenya
9/27/2010 10:48:13 PM #

Hi,

Thanks for your post.

Question: How do you add the 'edit' column? Looking forward to your response.

Thanks.

jmorris
jmorris United States
9/28/2010 10:38:03 PM #

@paul - you provide a "fnRowCallback" function:

var oTable;
      $(document).ready(function() {
        oTable = $('#tblBlogs').dataTable({
          "bJQueryUI": true,
          "sPaginationType": "full_numbers",
          "bProcessing": true,
          "bServerSide": true,
          "bAutoWidth": false,
          "fnRowCallback": function(nRow, aData, iDisplayIndex) {
              /* Create a link edit mode */
                  $('td:eq(0)', nRow).html('<a href="/blog/edit/' + aData[0] + '">Edit</a>');
              return nRow;
          },

          "aoColumns": [
          { "sTitle": "Id", "bVisible": true, "sClass": "center" },
          { "sTitle": "Title" },
          { "sTitle": "IsPublished", "sWidth": "125px", "sClass": "center" },
          { "sTitle": "CreatedOn", "sWidth": "125px", "sClass": "center" },
          { "sTitle": "CreatedBy", "sWidth": "125px"}],
          "sAjaxSource": '<%=Url.Action("ListData","Blog")%>'
        });
      } );

I am planning an updated post to show DataTables and ASP.NET MVC, in that post I'll go over some of the more advanced scenarios such as this.

paul mungai
paul mungai Kenya
9/28/2010 11:17:52 PM #

Hi Morris,

Thanks for your prompt response.

BTW, I'm using ASP.NET MVC, - it works Smile

Regards,
Paul.

Dave Robbins
Dave Robbins United States
1/14/2011 9:06:50 AM #

Jeff,

You might be interested in my updates to the paging solution I wrote about at Christmas.  I figured how to allow DataTables.Net to dynamically select the columns / properties from the underlying IQueryable class.  There's also a feature that allows you exclude custom client side columns from search and sort, so you can create an Actions column with href's without interfering the server-side processing.

Here's the link:

activeengine.wordpress.com/.../

jmorris
jmorris United States
1/14/2011 1:20:09 PM #

Thanks dave, i'll check it out!

Dave Robbins
Dave Robbins United States
2/9/2011 6:24:16 PM #

Yet another update to the paging solution for DataTables.Net.  I've added support for multi-column sorting.  I'm glad you guys are finding this useful!!

activeengine.wordpress.com/.../

ebay selling
ebay selling United States
2/25/2011 6:49:36 PM #

Hi Dave
In your example you are working with JQuery editor
can you please share which one it is.

Istria Croatia
Istria Croatia United States
3/9/2011 3:57:47 AM #

Great article about jquery, this is what i need! thanks!

authority formula bonus
authority formula bonus United States
3/11/2011 4:20:26 AM #

JQuery beginner and this info has been most helpful.

Kentucky Web Designer
Kentucky Web Designer United States
3/11/2011 11:09:56 AM #

I'm just now starting to understand JQuery..thanks for the info

Kućni ljubimci
Kućni ljubimci Croatia
3/13/2011 9:48:30 AM #

Wonderful article about jQuery!

jason robertson
jason robertson Australia
4/12/2011 4:16:49 AM #

source would be great works in the asmx examples and MVC example on Code Project this is the only post on the web re WCF also .NET 3.5

jeff
jeff United States
4/22/2011 8:44:25 PM #

A couple of people have asked for the source, I'll try to post it sometime soon.

Darren Brown
Darren Brown United States
4/22/2011 8:29:38 PM #

You mentioned that Datatables uses Hungarian notation for the variable types.  That sounds rather confusing to work with.  Any idea as to how or why it's made this way?  I assume it must be from a Hungarian development team, right?

jason robertson
jason robertson Australia
4/30/2011 12:24:20 AM #

OK I got this working but having used WCF in the past with the .NET Microsoft AJAX library and not jQuery and also having the contstraints of SP2010 it was an 18 round punch on meaning it threw 18 different errors or at least 18 times and took a few hours clearly learning curve on WCF in the context of JQuery and SharePoint Notice this is the GET still working on the POST. Also this is just jQuery and WCF SharePoint another day. Key point is that you will need to use the web.config settings approach in a SharePoint context (I believe at this point). This post from Rick Strahl helped a lot http://www.west-wind.com/WebLog/posts/310747.aspx . For SharePoint more specifically see Salim Maleks WinSmarts site his book is good also or Chris Obriens Posts, many ways to skin the cat I have a focus to use Silverlight and JQuery on the same services and WCF is the future or the now for now so I would not waste time on what is the past and also the learning or knowledge domain is more valuable in my opinion around WCF.

The web.config is or can be tricky to get right my service and classes are in the namespace of DataGrid being the class namespace not the WCF Interface it is unmarked in terms of a namespace attribute as per Jeffs post no namespace arrtibute on the service. Another point is using this in the service. It may be possible to get this working with a host factory like Factory="System.ServiceModel.Activation.WebServiceHostFactory" but that was not my starting point due to my needs. Any way hope this helps someone. Passing the parameters is ok I want to pass the full JSON and all the DataTables .NET grid parameters as JSON and when I solve this I will post the code on Code Project probably tonight

<system.serviceModel>
  <behaviors>  
   <serviceBehaviors>
    <behavior name="DataGrid.ArticleListServiceBehavior">
     <serviceMetadata httpGetEnabled="true" />
     <serviceDebug includeExceptionDetailInFaults="true" />
    </behavior>
    <behavior name="DataGrid.Service1Behavior">
     <serviceMetadata httpGetEnabled="true" />
     <serviceDebug includeExceptionDetailInFaults="true" />
    </behavior>
   </serviceBehaviors>
      <endpointBehaviors>
          <behavior name="DataGrid.ArticleListServiceBehavior">
              <webHttp/>
          </behavior>
          <behavior name="DataGrid.Service1Behavior">
              <webHttp/>
          </behavior>
      </endpointBehaviors>
  </behaviors>
     <services>
         <service behaviorConfiguration="DataGrid.ArticleListServiceBehavior" name="DataGrid.ArticleListService">
             <endpoint address="" binding="webHttpBinding" contract="DataGrid.IArticleListService" behaviorConfiguration="DataGrid.ArticleListServiceBehavior">                
             </endpoint>            
         </service>
        
         <service behaviorConfiguration="DataGrid.Service1Behavior" name="DataGrid.Service1">
             <endpoint address="" binding="webHttpBinding" contract="DataGrid.IService1" behaviorConfiguration="DataGrid.Service1Behavior">                
             </endpoint>            
         </service>
     </services>
</system.serviceModel>

jason robertson
jason robertson Australia
4/30/2011 12:37:14 AM #

While I am here I can't resist commenting on the Hungarian notation its a coding style can't remember why its called that but it involves type names in variables Camel case and other coding styles to help make code easier to maintain and read. If you have ever dealt with code that resuses the same variable with differnet types in the variable at different points in the code and other spaghetti coding approaches or worked on large scale apps this sort of discipline can make life easier. Even when you revisit your own code heaven forbid there are bugs that you have to fix the next year or months after you coded it which can happen onn large projects

aromaterapija
aromaterapija Croatia
9/2/2011 5:30:06 AM #

Thanks for your post!!!

john
john
11/20/2011 12:16:16 AM #

Hi ,

Great post, it gives me good info. Thanks for sharing.

Technology Blog
Technology Blog
11/22/2011 9:04:15 AM #

Just desire to say your article is as surprising. The clearness in your post is simply great and i could assume you\'re an expert on this subject. Well with your permission let me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please continue the enjoyable work.  Thanks !

Andrew Pelt
Andrew Pelt United States
10/31/2013 6:23:49 AM #

The new Zune browser is surprisingly good, but not as good as the iPod's. It works well, but isn't as fast as Safari, and has a clunkier interface. If you occasionally plan on using the web browser that's not an issue, but if you're planning to browse the web alot from your PMP then the iPod's larger screen and better browser may be important.

Pingbacks and trackbacks (6)+

Jeff Morris

Tag cloud

Month List

Page List