jQuery DataTables Plugin Meets C#

Tuesday, January 19, 2010

Over the weekend, I was doing some work on the internal CMS we use over at eagleenvision.net and I wanted to scrap my custom table implementation for a table system that would use JSON to return data rather than have the data be statically allocated on the page. Basically I wanted to have the ability to refresh, etc for editing purposes. My little table marker was too much code for something so simple.

I’m a HUGE fan of jQuery, especially of the recent changes in jQuery 1.4. We have already made a significant investment in jQuery code for our CMS and I didn’t want to simply add another library or framework into the mix… by that I mean I didn’t want to throw in the Ext framework, which specialized in UI controls rather than general purpose JavaScript.

I stumbled upon the jQuery DataTables plugin. It has a lot of great features… one of which is the ability to have server-side processing of your data. The examples on the site are written with PHP, as are the downloadable demos. I don’t use PHP, I use ASP.NET :). So I had to write my own library to process the incoming request. DataTables has a set of pre-defined request variables that are passed to the server for processing. A successful implementation will take all these variables into account and send the correct data back per these variables. (To see a complete list, check out the server-side usage page).

Our friend, IQueriable

If you’re a C# developer, there’s no way you don’t already know about LINQ… (double negatives… oops…. every C# knows about LINQ, or they’re not really current with technology… that’s better :) ). This includes VB people as well. IQueriable is the fundamental type of LINQ that drives the functionality of the various sequence operators. In my implementation of DataTables processing, I wanted to leverage LINQ so that you could throw this thing an IQueriable from Linq2Sql, Linq to NHibernate, Entity Framework, LightSpeed, in-memory list, or ANYTHING that has IQueriable functionality and it would just work.

How to output

The DataTables plugin accepts either JSON or XML… whichever jQuery will parse. My opinion is never use XML with JavaScript. It’s slower and there’s no point to using XML over JSON… especially in .NET where there are built-in JSON serializers. Having said that, you could certainly use XML… although I haven’t tested my code for this, I think (in theory) it will work the same. He’s the output type, which should be serialized into JSON or XML, which I will cover in a minute.

publicclass FormatedList

{

public FormatedList()

{

}

publicint sEcho { get; set; }

publicint iTotalRecords { get; set; }

publicint iTotalDisplayRecords { get; set; }

public List<List<string>> aaData { get; set; }

publicstring sColumns { get; set; }

publicvoid Import(string[] properties)

{

sColumns = string.Empty;

for (int i = 0; i < properties.Length; i++)

{

sColumns += properties[i];

if (i < properties.Length - 1)

sColumns += ",";

}

}

}

Basically the only interesting thing here is the output of the columns. I made a custom Import method that just takes a list of properties and forms the column string that DataTables will parse. Other than that the code here is just basic property holding.

ASP.NET MVC

Readers of my blog and twitter will know I am also a HUGE fan of ASP.NET MVC. I don’t think I’ll ever return to ASP.NET WebForms. But who knows. Anyways, here’s how you will output the thing in MVC

public ActionResult List()

{

IQueriable<User> users = Session.Linq<User>();

if (Request["sEcho"] != null)

{

var parser = new DataTableParser<User>(Request, users);

return Json(parser.Parse());

}

return Json(users);

}

You’ll notice that I referenced DataTableParser, which I will get to in a minute. This takes an HttpRequestBase (or an HttpRequest) and an IQueriable of whatever type. It will output a new FormattedList in the parse method, which you will return via JSON (which is serialized in the Json method for MVC).

ASP.NET Webservices

While I don’t claim to be an expert at ASP.NET Webservice, this I can handle :). He’s how would would do the same thing in ASP.NET Webservices.

using System.Web.Script.Serialization;

using System.Linq;

...

publicclass MyWebservice : System.Web.Services.WebService

{

publicstring MyMethod()

{

// change the following line per your data configuration

IQueriable<User> users = Session.Linq<User>();

response.ContentType = "application/json";

if(Request["sEcho"] != null)

{

var parser = new DataTableParser<User>(Request, users);

returnnew JavaScriptSerializer().Serialize(parser.Parse());

}

returnnew JavaScriptSerializer().Serialize(users);

}

}

This is the same code… it just uses webservices and the JavaScriptSerializer (which MVC uses under the covers) to serialize the FormatedList object.

It should be noted that you should ALWAYS check if the request is a DataTables request (which is what that sEcho business is all about).

The Parser

Now is the time to show you my parser. I have taken out my code comments to keep this short on my blog… but you can download my code from here and the comments should explain what is going on.

Caveat

Currently there’s a bug here, that I need to research :). If you have a boolean property and apply a sort on it, you’ll get an exception because I am trying to cast it as an object with the Expression.Lambda<Func<T, object>> call. I’ll look into this and update this blog post accordingly. If you can provide any help, that would be great :)

Conclusion

This is just a simple example of parsing a request and mutating IQueriable acordingly. I hope this helps someone out there who would like to use C# with the DataTables plugin. Again, you can download my code from here with full comments.

16 Comments

Thank for the great post!
I download the code only see the server side file, can you please also post a client side code?
Again great job and thank very much!

thanks a lot for this piece of code!!! theres a library on codeplex for datatables but your code is much more simpler and easier to adapt so i will continue with your code as a base.. also works perfectly with Subsonic.. just in case someone wants to try this combination :)

The only thing I´m having troubble sorting out is to get the generic search to work with null values (at least I´m guessing this is my problem).

When calling:
Expression.Property(paramExpression, property), "ToString"
in ApplyGenericSearch the code breaks and i have not been able to figure out a way to solve this...
My idea is to check for null and then return an empty string if so but i cant figure out how to do that with expression trees.

Is it supposed to work with DataTables 1.7.5 and/or EF4 ? Also, I get the following error when called, whatever which table I query or filter or sort or anything : (Most of my Primary keys are GUID, btw)