jQuery DataTables Plugin Meets C#

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.

public class FormatedList
{
    public FormatedList()
    {
    }
    public int sEcho { get; set; }
    public int iTotalRecords { get; set; }
    public int iTotalDisplayRecords { get; set; }
    public List<List<string>> aaData { get; set; }
    public string sColumns { get; set; }
    public void 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;
...
public class MyWebservice : System.Web.Services.WebService
{
    public string 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);
            return new JavaScriptSerializer().Serialize(parser.Parse());
        }
        
        return new 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.

public class DataTableParser<T>
{
    private const string INDIVIDUAL_SEARCH_KEY_PREFIX = "sSearch_";
    private const string INDIVIDUAL_SORT_KEY_PREFIX = "iSortCol_";
    private const string INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX = "sSortDir_";
    private const string DISPLAY_START = "iDisplayStart";
    private const string DISPLAY_LENGTH = "iDisplayLength";
    private const string ECHO = "sEcho";
    private const string ASCENDING_SORT = "asc";
    private IQueryable<T> _queriable;
    private readonly HttpRequestBase _httpRequest;
    private readonly Type _type;
    private readonly PropertyInfo[] _properties;
    public DataTableParser(HttpRequestBase httpRequest, IQueryable<T> queriable)
    {
        _queriable = queriable;
        _httpRequest = httpRequest;
        _type = typeof(T);
        _properties = _type.GetProperties();
    }
    public DataTableParser(HttpRequest httpRequest, IQueryable<T> queriable)
        : this(new HttpRequestWrapper(httpRequest), queriable)
    { }
    
    public FormatedList Parse()
    {
        var list = new FormatedList();
        list.Import(_properties.Select(x => x.Name).ToArray());
        
        list.sEcho = int.Parse(_httpRequest[ECHO]);
        
        list.iTotalRecords = _queriable.Count();
        
        ApplySort();
        
        int skip = 0, take = 10;
        int.TryParse(_httpRequest[DISPLAY_START], out skip);
        int.TryParse(_httpRequest[DISPLAY_LENGTH], out take);
        
        list.aaData = _queriable.Where(ApplyGenericSearch)
                                .Where(IndividualPropertySearch)
                                .Skip(skip)
                                .Take(take)
                                .Select(SelectProperties)
                                .ToList();
                                
        list.iTotalDisplayRecords = list.aaData.Count;
        return list;
    }
    private void ApplySort()
    {
        foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
        {
            int sortcolumn = int.Parse(_httpRequest[key]);
            if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                break;
                
            string sortdir = _httpRequest[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)];
            
            var paramExpr = Expression.Parameter(typeof(T), "val");
            var propertyExpr = Expression.Lambda<Func<T, object>>(Expression.Property(paramExpr, _properties[sortcolumn]), paramExpr);
            
            if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
                _queriable = _queriable.OrderBy(propertyExpr);
            else
                _queriable = _queriable.OrderByDescending(propertyExpr);
        }
    }
    
    private Expression<Func<T, List<string>>> SelectProperties
    {
        get
        {
            // 
            return value => _properties.Select
                                        (
                                            prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
                                        )
                                       .ToList();
        }
    }
    
    private Expression<Func<T, bool>> IndividualPropertySearch
    {
        get
        {
            var paramExpr = Expression.Parameter(typeof(T), "val");
            Expression whereExpr = Expression.Constant(true); // default is val => True
            foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
            {
                int property = -1;
                if (!int.TryParse(_httpRequest[key].Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property) 
                    || property >= _properties.Length || string.IsNullOrEmpty(_httpRequest[key]))
                    break; // ignore if the option is invalid
                string query = _httpRequest[key].ToLower();
                
                var toStringCall = Expression.Call(
                                    Expression.Call(
                                        Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
                                    typeof(string).GetMethod("ToLower", new Type[0]));
                
                whereExpr = Expression.And(whereExpr, 
                                           Expression.Call(toStringCall, 
                                                           typeof(string).GetMethod("Contains"), 
                                                           Expression.Constant(query)));
                
            }
            return Expression.Lambda<Func<T, bool>>(whereExpr, paramExpr);
        }
    }
    
    private Expression<Func<T, bool>> ApplyGenericSearch
    {
        get
        {
            string search = _httpRequest["sSearch"];
            
            if (string.IsNullOrEmpty(search) || _properties.Length == 0)
                return x => true;
                
            var searchExpression = Expression.Constant(search.ToLower());
            var paramExpression = Expression.Parameter(typeof(T), "val");
            
            var propertyQuery = (from property in _properties
                                let tostringcall = Expression.Call(
                                                    Expression.Call(
                                                        Expression.Property(paramExpression, property), "ToString", new Type[0]),
                                                        typeof(string).GetMethod("ToLower", new Type[0]))
                                select Expression.Call(tostringcall, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();
                                
            Expression compoundExpression = propertyQuery[0];
            
            for (int i = 1; i < propertyQuery.Length; i++)
                compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);
                
            return Expression.Lambda<Func<T, bool>>(compoundExpression, paramExpression);
        }
    }
}

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.