jQuery DataTables Plugin and server-side processing in c#

jQuery DataTables Plugin is a table system that would use JSON to return data rather than have the data be statically allocated on the page. It also has the ability to refresh the table only without loading the whole page again.

There are tones of samples for PHP about server-side data processing, the only one for c# is

jQuery DataTables Plugin Meets C#.

Zack Owens wrote a DataTableParser class to process the incoming JSON request which will take 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).

The only limit here is the Parser will output all fields to browser. I changed the DataTableParser class so I can define what fields to be output in parameters like below:

            List users = _biz.GetUsers();

            var parser = new DataTableParser(this.Request, users.AsQueryable());

            return Json(parser.Parse(c => new List(new string[]{c.Name, c.Email, c.Points.ToString()})), JsonRequestBehavior.AllowGet);

In client side, we just display three fields even though the User has other fields like Birthday, etc

<% =Html.Grid(Model.Ranks)
    .Attributes( ID => "userTable" )
    .Columns(
        col =>
        {

            col.For(p => p.Name)
                .Named("Name");

            col.For(p => p.Email)
                .Named("Email");

            col.For(p => p.Points)
                .Named("Points");

        }
    )
%> 

The javascript code for auto refresh:

The whole DataTableParser class:

   /// 

    /// Parses the request values from a query from the DataTables jQuery pluggin
    /// 
    /// List data type
    public class DataTableParser
    {
        /*
         * int: iDisplayStart - Display start point
        * int: iDisplayLength - Number of records to display
        * string: string: sSearch - Global search field
        * boolean: bEscapeRegex - Global search is regex or not
        * int: iColumns - Number of columns being displayed (useful for getting individual column search info)
        * string: sSortable_(int) - Indicator for if a column is flagged as sortable or not on the client-side
        * string: sSearchable_(int) - Indicator for if a column is flagged as searchable or not on the client-side
        * string: sSearch_(int) - Individual column filter
        * boolean: bEscapeRegex_(int) - Individual column filter is regex or not
        * int: iSortingCols - Number of columns to sort on
        * int: iSortCol_(int) - Column being sorted on (you will need to decode this number for your database)
        * string: sSortDir_(int) - Direction to be sorted - "desc" or "asc". Note that the prefix for this variable is wrong in 1.5.x, but left for backward compatibility)
        * string: sEcho - Information for DataTables to use for rendering
         */

        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 _queriable;
        private readonly HttpRequestBase _httpRequest;
        private readonly Type _type;
        private readonly PropertyInfo[] _properties;

        public DataTableParser(HttpRequestBase httpRequest, IQueryable queriable)
        {
            _queriable = queriable;
            _httpRequest = httpRequest;
            _type = typeof(T);
            _properties = _type.GetProperties();
        }

        public DataTableParser(HttpRequest httpRequest, IQueryable queriable)
            : this(new HttpRequestWrapper(httpRequest), queriable)
        { }

        /// 

        /// Parses the  parameter values for the accepted 
        /// DataTable request values
        /// 
        /// Formated output for DataTables, which should be serialized to JSON
        /// 
        ///     In an ASP.NET MVC from a controller, you can call the Json method and return this result.
        ///     
        ///     public ActionResult List()
        ///     {
        ///         // change the following line per your data configuration
        ///         IQueriable users = datastore.Linq();
        ///         
        ///         if (Request["sEcho"] != null) // always test to see if the request is from DataTables
        ///         {
        ///             var parser = new DataTableParser(Request, users);
        ///             return Json(parser.Parse());
        ///         }
        ///         return Json(_itemController.CachedValue);
        ///     }
        ///     
        ///     If you're not using MVC, you can create a web service and write the JSON output as such:
        ///     
        ///     using System.Web.Script.Serialization;
        ///     public class MyWebservice : System.Web.Services.WebService
        ///     {
        ///         public string MyMethod()
        ///         {
        ///             // change the following line per your data configuration
        ///             IQueriable users = datastore.Linq();
        ///             
        ///             response.ContentType = "application/json";
        ///             
        ///             JavaScriptSerializer serializer = new JavaScriptSerializer();
        ///             var parser = new DataTableParser(Request, users);
        ///             return new JavaScriptSerializer().Serialize(parser.Parse());
        ///         }
        ///     }
        /// 
        /// 

        public FormatedList Parse()
        {
            return Parse(this.SelectProperties);
        }
        
        public FormatedList Parse(Expression>> properties)
        {
            var list = new FormatedList();

            // import property names
            list.Import(_properties.Select(x => x.Name).ToArray());

            // parse the echo property (must be returned as int to prevent XSS-attack)
            list.sEcho = int.Parse(_httpRequest[ECHO]);

            // count the record BEFORE filtering
            list.iTotalRecords = _queriable.Count();

            // apply the sort, if there is one
            ApplySort();

            // parse the paging values
            int skip = 0, take = 10;
            int.TryParse(_httpRequest[DISPLAY_START], out skip);
            int.TryParse(_httpRequest[DISPLAY_LENGTH], out take);

            // if -1, take all
            if (take == -1)
                take = list.iTotalRecords;

            // setup the data with individual property search, all fields search,
            // paging, and property list selection
            list.aaData = _queriable.Where(ApplyGenericSearch)
                                    .Where(IndividualPropertySearch)
                                    .Skip(skip)
                                    .Take(take)
                                    .Select(properties)
                                    .ToList();

            // total records that are displayed
            list.iTotalDisplayRecords = list.aaData.Count;

            return list;
        }

        private void ApplySort()
        {
            // enumerate the keys for any sortations
            foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
            {
                // column number to sort (same as the array)
                int sortcolumn = int.Parse(_httpRequest[key]);

                // ignore malformatted values
                if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                    break;

                // get the direction of the sort
                string sortdir = _httpRequest[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)];

                // form the sortation per property via a property expression
                var paramExpr = Expression.Parameter(typeof(T), "val");
                var expression = Expression.Convert(Expression.Property(paramExpr, _properties[sortcolumn]), typeof(Object));
                var propertyExpr = Expression.Lambda>(expression, paramExpr);

                // apply the sort (default is ascending if not specified)
                if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
                    _queriable = _queriable.OrderBy(propertyExpr);
                else
                    _queriable = _queriable.OrderByDescending(propertyExpr);
            }
        }

        /// 

        /// Expression that returns a list of string values, which correspond to the values
        /// of each property in the list type
        /// 
        /// This implementation does not allow indexers
        private Expression>> SelectProperties
        {
            get
            {
                // 
                return value => _properties.Select
                                            (
                                                // empty string is the default property value
                                                prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
                                            )
                                           .ToList();
            }
        }

        /// 

        /// Compound predicate expression with the individual search predicates that will filter the results
        /// per an individual column
        /// 
        private Expression> 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)))
                {
                    // parse the property number
                    int property = -1;
                    if (!int.TryParse(key.Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property) 
                        || property >= _properties.Length || string.IsNullOrEmpty(_httpRequest[key]))
                        continue; // ignore if the option is invalid

                    string query = _httpRequest[key].ToLower();

                    // val.{PropertyName}.ToString().ToLower().Contains({query})
                    var toStringCall = Expression.Call(
                                        Expression.Call(
                                            Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
                                        typeof(string).GetMethod("ToLower", new Type[0]));
                    
                    // reset where expression to also require the current contraint
                    whereExpr = Expression.And(whereExpr, 
                                               Expression.Call(toStringCall, 
                                                               typeof(string).GetMethod("Contains"), 
                                                               Expression.Constant(query)));
                    
                }

                return Expression.Lambda>(whereExpr, paramExpr);
            }
        }

        /// 

        /// Expression for an all column search, which will filter the result based on this criterion
        /// 
        private Expression> ApplyGenericSearch
        {
            get
            {
                string search = _httpRequest["sSearch"];

                // default value
                if (string.IsNullOrEmpty(search) || _properties.Length == 0)
                    return x => true;

                // invariant expressions
                var searchExpression = Expression.Constant(search.ToLower());
                var paramExpression = Expression.Parameter(typeof(T), "val");

                // query all properties and returns a Contains call expression 
                // from the ToString().ToLower()
                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();

                // we now need to compound the expression by starting with the first
                // expression and build through the iterator
                Expression compoundExpression = propertyQuery[0];

                // add the other expressions
                for (int i = 1; i < propertyQuery.Length; i++)
                    compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);

                // compile the expression into a lambda 
                return Expression.Lambda>(compoundExpression, paramExpression);
            }
        }
    }
    
    public class FormatedList
    {
        public FormatedList()
        {
        }

        public int sEcho { get; set; }
        public int iTotalRecords { get; set; }
        public int iTotalDisplayRecords { get; set; }
        public List> 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 += ",";
            }
        }
    }