DataTables with WebForms

The Datatables jQuery plugin is a powerful plugin for showing data in a table format. Zack Owens gives a good example of doing DataTables Server Side processing with C# on his blog. However this is written for MVC pages and does not work with Webforms and Page methods. This tutorial shows how to adapt his code to work in this scenario.

I use a web form design layout, primarily because I haven't learnt MVC yet. Someday I will, but for now Webforms does what I want so I'm sticking with what I know. However I am looking to move the presentation into Javascript with jQuery, and the DataTables control provides a lot of functionality, especially when combined with Zack Owens generic backend parser, which magically handles sorting and filtering (paginating appears to be a bit off though!).

The main problem applying this to a Page Method (not sure about .asmx Web servies methods but it may be the same. Page methods are Web services combined into standard .aspx pages). See here for a tutorial on page methods.
A page methods expects to send a recieve data in a specific format (design to integrate with Microsoft's ScriptManager client side framework). MVC is more forgiving and works with the standard Datatables method, but we need to make some small conversions to make it work for us. Fortunately this is very easy, by overriding the “fnServerData” function when we initialise our DataTable.

(function() {

    var helpers = (function() {
    });

    helpers.formatDataTableData = function(aoData) {
        var r = {};
        var x;
        for (x in aoData) {
            r[aoData[x].name] = aoData[x].value
        }
        return { tableParams: r };
    }



    window.helpers = helpers;
})();

$('#DataTable').dataTable({
            "bServerSide": true,
            "sAjaxSource": 'Page.aspx/GetData',
            "fnServerData": function(sSource, aoData, fnCallback) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: sSource,
                    data: JSON.stringify(helpers.formatDataTableData(aoData)),
                    dataType: "json",
                    success: function(msg) { fnCallback(msg.d) }
                });
            }
        });
[

Here we have a helper function (you can put this in another file) that takes the name+value array format of aoData (which is normally used for parsing into a GET URL parameter string) and converts it into a standard Javascript object that with conver to JSON nicely.

When we initialise our table, we set it so we are using server side processing, we set our AJAX source to the GetData page method, then we override the fnServerData function. This makes a custom AJAX call with required “POST” type and “application/json” content type, the Data is formatting with the helper and then converted to JSON (using json2.js). Page methos always put the return data in a d field of the returned variable, so on success we must unbox to give the data back to DataTables.

Looking at the helper method you will see that we wrap up all the data into tableParams field. This allows us to access it in our Page Method:

[WebMethod()]
public static object GetData(Dictionary<string, object> tableParams)
{
    IQueriable<User> users = Session.Linq<User>();
    if(tableParams.ContainsKey("sEcho"))
    {
        var parser = new DataTableParser<User>(tableParams, users);
        return parser.Parse();
    }
    return users;

This shows that our table parameters appear in the tableParams, which we pass on to the DataTableParser. Note that you don't need to serialise the resulting object yourself as the Page Method will do that for you.

public class DataTable
    {
        public DataTable()
        {
        }
        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 += ",";
            }
        }
    }
    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 SEARCH = "sSearch";
        private const string ASCENDING_SORT = "asc";
        private IQueryable<T> _queriable;
        private readonly Dictionary<string, object> _tableParams;
        private readonly Type _type;
        private readonly System.Reflection.PropertyInfo[] _properties;
        public DataTableParser(Dictionary<string, object> tableParams, IQueryable<T> queriable)
        {
            _queriable = queriable;
            _tableParams = tableParams;
            _type = typeof(T);
            _properties = _type.GetProperties();
        }

        public DataTable Parse()
        {
            var list = new DataTable();
            list.Import(_properties.Select(x => x.Name).ToArray());

            list.sEcho = (int)_tableParams[ECHO];

            list.iTotalRecords = _queriable.Count();

            ApplySort();

            int skip = 0, take = list.iTotalRecords;
            if (_tableParams.ContainsKey(DISPLAY_START))
                skip = (int)_tableParams[DISPLAY_START];
            if (_tableParams.ContainsKey(DISPLAY_LENGTH))
                take = (int)_tableParams[DISPLAY_LENGTH];

            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 _tableParams.Keys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
            {
                int sortcolumn = (int)_tableParams[key];
                if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                    break;

                string sortdir = _tableParams[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)].ToString();

                var paramExpr = Expression.Parameter(typeof(T), "val");
                var propertyExpr = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(paramExpr, _properties[sortcolumn]), typeof(object)), 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 _tableParams.Keys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
                {
                    int property = -1;
                    if (!int.TryParse(_tableParams[key].ToString().Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property)
                        || property >= _properties.Length || string.IsNullOrEmpty(_tableParams[key].ToString()))
                        break; // ignore if the option is invalid
                    string query = _tableParams[key].ToString().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
            {


                if (!_tableParams.ContainsKey(SEARCH) || _properties.Length == 0)
                    return x => true;

                string search = _tableParams[SEARCH].ToString();

                if (String.IsNullOrEmpty(search))
                    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);
            }
        }
    }

This is the same as Zack Owen's code except it works with a dictionary object instead of the request object. That's all there is to it!