Using jQuery DataTables with ASP.NET Mvc for server-side filtering, sorting and paging

Using jQuery DataTables with ASP.NET Mvc for server-side filtering, sorting and paging

Let’s get started by first explaining what jQuery DataTables is. DataTables is a jQuery plugin that gives you filtering, sorting and paging functionality for your data represented in an html table. This functionality can be done in two ways; one is client-side and the second is server-side. A lot of developers are working with result sets that are in the thousands, if not millions, and relying on client-side filtering, sorting, and paging is very slow. My main focus in this article will be the DataTables server side processing functionality.

Let’s start off by creating an empty ASP.NET Mvc 3 project called jQuery.DataTables.Web that uses IIS Express and that has Windows Authentication disabled and that allows Anonymous Authentication. Now let’s add the jquery.datatables NuGet package to the Mvc project. Next create a Class Library called jQuery.DataTables.Mvc which will contain all of our code that will help us in receiving the request from DataTables and sending the response from the server. We’ll need to have code to represent a DataTables request from the client, a response from the server and have a custom ModelBinder that will be in charge of binding our DataTables Request object.

Add a new class to the jQuery.DataTables.Mvc project called JQueryDataTablesModel which represents our DataTables request. You’ll notice an enum and another class inside of the code below. The SortingDirection enum describes the sorting direction for the column and the SortedColumn class represents the direction and actual property to sort by. I’ve tried to keep the request object as close to the server side usage documentation as possible for demo purposes. I usually only use the collections associated with sorting. That has just been my need so far from the plugin, but you very well may want to use the built in searching on the individual columns etc.

Please refer to http://datatables.net/usage/server-side for examples and explanations of a DataTables Request and what is expected in the response from the server. This will help give you an understanding of what my code is trying to accomplish.

using System.Collections.Generic;
using System.Collections.ObjectModel;

namespace jQuery.DataTables.Mvc
{
    /// <summary>
    /// Represents the jQuery DataTables request that is sent for server
    /// side processing.
    /// <para>http://datatables.net/usage/server-side</para>
    /// </summary>
    public class JQueryDataTablesModel
    {
        /// <summary>
        /// Gets or sets the information for DataTables to use for rendering.
        /// </summary>
        public int sEcho { get; set; }

        /// <summary>
        /// Gets or sets the display start point.
        /// </summary>
        public int iDisplayStart { get; set; }

        /// <summary>
        /// Gets or sets the number of records to display.
        /// </summary>
        public int iDisplayLength { get; set; }

        /// <summary>
        /// Gets or sets the Global search field.
        /// </summary>
        public string sSearch { get; set; }

        /// <summary>
        /// Gets or sets if the Global search is regex or not.
        /// </summary>
        public bool bRegex { get; set; }

        /// <summary>
        /// Gets or sets the number of columns being display (useful for getting individual column search info).
        /// </summary>
        public int iColumns { get; set; }

        /// <summary>
        /// Gets or sets indicator for if a column is flagged as sortable or not on the client-side.
        /// </summary>
        public ReadOnlyCollection<bool> bSortable_ { get; set; }

        /// <summary>
        /// Gets or sets indicator for if a column is flagged as searchable or not on the client-side.
        /// </summary>
        public ReadOnlyCollection<bool> bSearchable_ { get; set; }

        /// <summary>
        /// Gets or sets individual column filter.
        /// </summary>
        public ReadOnlyCollection<string> sSearch_ { get; set; }

        /// <summary>
        /// Gets or sets if individual column filter is regex or not.
        /// </summary>
        public ReadOnlyCollection<bool> bRegex_ { get; set; }

        /// <summary>
        /// Gets or sets the number of columns to sort on.
        /// </summary>
        public int? iSortingCols { get; set; }

        /// <summary>
        /// Gets or sets column being sorted on (you will need to decode this number for your database).
        /// </summary>
        public ReadOnlyCollection<int> iSortCol_ { get; set; }

        /// <summary>
        /// Gets or sets the direction to be sorted - "desc" or "asc".
        /// </summary>
        public ReadOnlyCollection<string> sSortDir_ { get; set; }

        /// <summary>
        /// Gets or sets the value specified by mDataProp for each column. 
        /// This can be useful for ensuring that the processing of data is independent 
        /// from the order of the columns.
        /// </summary>
        public ReadOnlyCollection<string> mDataProp_ { get; set; }

        public ReadOnlyCollection<SortedColumn> GetSortedColumns()
        {
            if (!iSortingCols.HasValue)
            {
                // Return an empty collection since it's easier to work with when verifying against
                return new ReadOnlyCollection<SortedColumn>(new List<SortedColumn>());
            }

            var sortedColumns = new List<SortedColumn>();
            for (int i = 0; i < iSortingCols.Value; i++)
            {
                sortedColumns.Add(new SortedColumn(mDataProp_[iSortCol_[i]], sSortDir_[i]));
            }

            return sortedColumns.AsReadOnly();
        }
    }

    /// <summary>
    /// Represents a sorted column from DataTables.
    /// </summary>
    public class SortedColumn
    {
        private const string Ascending = "asc";

        public SortedColumn(string propertyName, string sortingDirection)
        {
            PropertyName = propertyName;
            Direction = sortingDirection.Equals(Ascending) ? SortingDirection.Ascending : SortingDirection.Descending;
        }

        /// <summary>
        /// Gets the name of the Property on the class to sort on.
        /// </summary>
        public string PropertyName { get; private set; }

        public SortingDirection Direction { get; private set; }

        public override int GetHashCode()
        {
            var directionHashCode = Direction.GetHashCode();
            return PropertyName != null ? PropertyName.GetHashCode() + directionHashCode : directionHashCode;
        }

        public override bool Equals(object obj)
        {
            if (obj == null)
            {
                return false;
            }

            if (GetType() != obj.GetType())
            {
                return false;
            }

            var other = (SortedColumn)obj;

            if (other.Direction != Direction)
            {
                return false;
            }

            return other.PropertyName == PropertyName;
        }
    }

    /// <summary>
    /// Represents the direction of sorting for a column.
    /// </summary>
    public enum SortingDirection
    {
        Ascending,
        Descending
    }
}

Now we’ll need a way of binding our model since the collections being sent by DataTables are not following the proper formatting for the DefaultModelBinder. The reason is that DataTables sends non-complex collections in the request with a name of "CollectionName_index". The ASP.NET Mvc DefaultModelBinder expects non-complex collections to just be passed with a name of "CollectionName" then it searches the value providers for values associated with "CollectionName". A good article on this was posted by Phil Haack a few years ago at http://haacked.com/archive/2008/10/23/model-binding-to-a-list.aspx.

Add a new class to the jQuery.DataTables.Mvc project called JQueryDataTablesModelBinder.

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Web.Mvc;

namespace jQuery.DataTables.Mvc
{
    public class JQueryDataTablesModelBinder : IModelBinder
    {
        #region Private Variables (Request Keys)

        private const string iDisplayStartKey = "iDisplayStart";
        private const string iDisplayLengthKey = "iDisplayLength";
        private const string iColumnsKey = "iColumns";
        private const string sSearchKey = "sSearch";
        private const string bEscapeRegexKey = "bRegex";
        private const string bSortable_Key = "bSortable_";
        private const string bSearchable_Key = "bSearchable_";
        private const string sSearch_Key = "sSearch_";
        private const string bEscapeRegex_Key = "bRegex_";
        private const string iSortingColsKey = "iSortingCols";
        private const string iSortCol_Key = "iSortCol_";
        private const string sSortDir_Key = "sSortDir_";
        private const string sEchoKey = "sEcho";
        private const string mDataProp_Key = "mDataProp_";

        private ModelBindingContext _bindingContext;

        #endregion

        #region IModelBinder Members

        public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            if (bindingContext == null)
            {
                throw new ArgumentNullException("bindingContext");
            }
            _bindingContext = bindingContext;

            //Bind Model
            var dataTablesRequest = new JQueryDataTablesModel();
            dataTablesRequest.sEcho = GetA<int>(sEchoKey);
            if (dataTablesRequest.sEcho <= 0)
            {
                throw new InvalidOperationException("Expected the request to have a sEcho value greater than 0");
            }

            dataTablesRequest.iColumns = GetA<int>(iColumnsKey);
            dataTablesRequest.bRegex = GetA<bool>(bEscapeRegexKey);
            dataTablesRequest.bRegex_ = GetAList<bool>(bEscapeRegex_Key);
            dataTablesRequest.bSearchable_ = GetAList<bool>(bSearchable_Key);
            dataTablesRequest.bSortable_ = GetAList<bool>(bSortable_Key);
            dataTablesRequest.iDisplayLength = GetA<int>(iDisplayLengthKey);
            dataTablesRequest.iDisplayStart = GetA<int>(iDisplayStartKey);
            dataTablesRequest.iSortingCols = GetANullableValue<int>(iSortingColsKey);

            if (dataTablesRequest.iSortingCols.HasValue)
            {
                dataTablesRequest.iSortCol_ = GetAList<int>(iSortCol_Key);
                dataTablesRequest.sSortDir_ = GetStringList(sSortDir_Key);

                if (dataTablesRequest.iSortingCols.Value 
                    != dataTablesRequest.iSortCol_.Count)
                {
                    throw new InvalidOperationException(string.Format("Amount of items contained in iSortCol_ {0} do not match the amount specified in iSortingCols which is {1}",
                        dataTablesRequest.iSortCol_.Count, dataTablesRequest.iSortingCols.Value));
                }

                if (dataTablesRequest.iSortingCols.Value
                    != dataTablesRequest.sSortDir_.Count)
                {
                    throw new InvalidOperationException(string.Format("Amount of items contained in sSortDir_ {0} do not match the amount specified in iSortingCols which is {1}",
                        dataTablesRequest.sSortDir_.Count, dataTablesRequest.iSortingCols.Value));
                }
            }
            dataTablesRequest.sSearch = GetString(sSearchKey);
            dataTablesRequest.sSearch_ = GetStringList(sSearch_Key);
            dataTablesRequest.mDataProp_ = GetStringList(mDataProp_Key);

            return dataTablesRequest;
        }

        #endregion

        #region Methods

        /// <summary>
        /// Retrieves an IList of strings from the ModelBindingContext based on the key provided.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        private ReadOnlyCollection<string> GetStringList(string key)
        {
            var list = new List<string>();
            bool hasMore = true;
            int i = 0;
            while(hasMore)
            {
                var newKey = (key + i.ToString());

                // No need to use a prefix since data tables will not prefix the request names        
                var valueResult = _bindingContext.ValueProvider.GetValue(newKey);

                if (valueResult == null)
                {
                    // If valueResult is still null then we know the value is not in the ModelBindingContext
                    // cease execution of this forloop
                    hasMore = false;
                    continue;
                }

                list.Add((string)valueResult.ConvertTo(typeof(string)));

                i++;
            }

            return list.AsReadOnly();
        }

        private ReadOnlyCollection<T> GetAList<T>(string key) where T : struct
        {
            var list = new List<T>();
            bool hasMore = true;
            int i = 0;
            while (hasMore)
            {
                var newKey = (key + i.ToString());

                var valueResult = _bindingContext.ValueProvider.GetValue(newKey);

                if (valueResult == null)
                {
                    // If valueResult is still null then we know the value is not in the ModelBindingContext
                    // cease execution of this forloop
                    hasMore = false;
                    continue;
                }

                list.Add((T)valueResult.ConvertTo(typeof(T)));
                i++;
            }

            return list.AsReadOnly();
        }

        /// <summary>
        /// Retrieves a string from the ModelBindingContext based on the key provided.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        private string GetString(string key)
        {       
            var valueResult = _bindingContext.ValueProvider.GetValue(key);

            if (valueResult == null)
            {
                return null;
            }

            return (string)valueResult.ConvertTo(typeof(string));
        }

        private T GetA<T>(string key) where T : struct
        {       
            var valueResult = _bindingContext.ValueProvider.GetValue(key);

            if (valueResult == null)
            {
                return new T();
            }

            return (T)valueResult.ConvertTo(typeof(T));
        }

        private T? GetANullableValue<T>(string key) where T : struct
        {        
            var valueResult = _bindingContext.ValueProvider.GetValue(key);

            if (valueResult == null)
            {
                return null;
            }

            return (T?)valueResult.ConvertTo(typeof(T));
        }

        #endregion
    }
}

You’ll notice that I am looking for the DataTables request name/value pairs through the BindingModelContext.ValueProvider property. By default this property is a ValueProviderCollection class built in to ASP.NET Mvc. When I use the ModelBindingContext.ValueProvider.GetValue method, Mvc makes a Linq to Objects call to find the value inside of one of the following IValueProvider: ChildActionValueProvider, FormValueProvider, RouteDataValueProvider, QueryStringValueProvider, and HttpFileCollectionValueProvider. I highly recommend that you all download the Mvc 3 source code and look in to each one of the classes that I just noted. For the purposes of this demo I will show the source for the ValueProviderCollection:

namespace System.Web.Mvc {
    using System;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.Linq;

    public class ValueProviderCollection : Collection<IValueProvider>, IValueProvider, IUnvalidatedValueProvider {

        public ValueProviderCollection() {
        }

        public ValueProviderCollection(IList<IValueProvider> list)
            : base(list) {
        }

        public virtual bool ContainsPrefix(string prefix) {
            return this.Any(vp => vp.ContainsPrefix(prefix));
        }

        public virtual ValueProviderResult GetValue(string key) {
            return GetValue(key, skipValidation: false);
        }

        public virtual ValueProviderResult GetValue(string key, bool skipValidation) {
            return (from provider in this
                    let result = GetValueFromProvider(provider, key, skipValidation)
                    where result != null
                    select result).FirstOrDefault();
        }

        internal static ValueProviderResult GetValueFromProvider(IValueProvider provider, string key, bool skipValidation) {
            // Since IUnvalidatedValueProvider is a superset of IValueProvider, it's always OK to use the
            // IUnvalidatedValueProvider-supplied members if they're present. Otherwise just call the
            // normal IValueProvider members.

            IUnvalidatedValueProvider unvalidatedProvider = provider as IUnvalidatedValueProvider;
            return (unvalidatedProvider != null) ? unvalidatedProvider.GetValue(key, skipValidation) : provider.GetValue(key);
        }

        protected override void InsertItem(int index, IValueProvider item) {
            if (item == null) {
                throw new ArgumentNullException("item");
            }
            base.InsertItem(index, item);
        }

        protected override void SetItem(int index, IValueProvider item) {
            if (item == null) {
                throw new ArgumentNullException("item");
            }
            base.SetItem(index, item);
        }

    }
}

Our next objective is to format the response from the server to satisfy the requirements from jQuery DataTables which expects a JSON object to be returned to it. Here is an example of a JSON response from my demo:

{ "iTotalRecords":11,
"iTotalDisplayRecords":11,
"sEcho":1,
"aaData":[{"FirstName":"Dan","LastName":"Callahan","PhoneNumber":"(123) 555-5552","Age":35,"Birthday":"\/Date(218955600000)\/"},
{"FirstName":"Tom","LastName":"Gun","PhoneNumber":"(123) 555-5559","Age":59,"Birthday":"\/Date(-534538800000)\/"},
{"FirstName":"James","LastName":"Halk","PhoneNumber":"(123) 555-5554","Age":21,"Birthday":"\/Date(660027600000)\/"},
{"FirstName":"Jarold","LastName":"Interface","PhoneNumber":"(123) 555-5556","Age":39,"Birthday":"\/Date(86932800000)\/"},
{"FirstName":"Kevin","LastName":"Kentucky","PhoneNumber":"(123) 555-5551","Age":40,"Birthday":"\/Date(92638800000)\/"},
{"FirstName":"Justin","LastName":"Michaels","PhoneNumber":"(123) 555-5555","Age":27,"Birthday":"\/Date(470898000000)\/"},
{"FirstName":"Erich","LastName":"Milton","PhoneNumber":"(123) 555-5558","Age":54,"Birthday":"\/Date(-370724400000)\/"},
{"FirstName":"Mike","LastName":"Peterson","PhoneNumber":"(123) 555-5550","Age":24,"Birthday":"\/Date(568184400000)\/"},
{"FirstName":"Jason","LastName":"Ralph","PhoneNumber":"(123) 555-5557","Age":27,"Birthday":"\/Date(468306000000)\/"},
{"FirstName":"John","LastName":"Thompson21","PhoneNumber":"(123) 555-5545","Age":27,"Birthday":"\/Date(473317200000)\/"}
]}

Add a new class to the jQuery.DataTables.Mvc project called JQueryDataTablesResponse. I’ll leverage the power of generics in order to generate the aaData property that DataTables expects to be an array of JSON objects. I’ll let the JsonResult be in charge of generating the correct output for this collection which it uses the JavaScriptSerializer class in order to serialize the JQueryDataTablesResponse before sending it back in the response.

using System.Collections.Generic;

namespace jQuery.DataTables.Mvc
{
    /// <summary>
    /// Represents the required data for a response from a request by DataTables.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class JQueryDataTablesResponse<T>
    {
        public JQueryDataTablesResponse(IEnumerable<T> items,
            int totalRecords,
            int totalDisplayRecords,
            int sEcho)
        {
            aaData = items;
            iTotalRecords = totalRecords;
            iTotalDisplayRecords = totalDisplayRecords;
            this.sEcho = sEcho;
        }

        /// <summary>
        /// Sets the Total records, before filtering (i.e. the total number of records in the database)
        /// </summary>
        public int iTotalRecords { get; private set; }

        /// <summary>
        /// Sets the Total records, after filtering 
        /// (i.e. the total number of records after filtering has been applied - 
        /// not just the number of records being returned in this result set)
        /// </summary>
        public int iTotalDisplayRecords { get; private set; }

        /// <summary>
        /// Sets an unaltered copy of sEcho sent from the client side. This parameter will change with each 
        /// draw (it is basically a draw count) - so it is important that this is implemented. 
        /// Note that it strongly recommended for security reasons that you 'cast' this parameter to an 
        /// integer in order to prevent Cross Site Scripting (XSS) attacks.
        /// </summary>
        public int sEcho { get; private set; }

        /// <summary>
        /// Sets the data in a 2D array (Array of JSON objects). Note that you can change the name of this 
        /// parameter with sAjaxDataProp.
        /// </summary>
        public IEnumerable<T> aaData { get; private set; }
    }
}

Now we’ll work on setting up a small example in the MVC 3 project that you created at the beginning of the post. Go to the Global.asax.cs file and replace its contents with the following:

using System.Web.Mvc;
using System.Web.Routing;
using jQuery.DataTables.Mvc;

namespace jQuery.DataTables.Web
{
    // Note: For instructions on enabling IIS6 or IIS7 classic mode, 
    // visit http://go.microsoft.com/?LinkId=9394801

    public class MvcApplication : System.Web.HttpApplication
    {
        public static void RegisterGlobalFilters(GlobalFilterCollection filters)
        {
            filters.Add(new HandleErrorAttribute());
        }

        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                "Default", // Route name
                "{controller}/{action}/{id}", // URL with parameters
                new { controller = "Customer", action = "Search", id = UrlParameter.Optional } // Parameter defaults
            );

        }

        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            RegisterGlobalFilters(GlobalFilters.Filters);
            RegisterRoutes(RouteTable.Routes);

            // Lets MVC know that anytime there is a JQueryDataTablesModel as a parameter in an action to use the
            // JQueryDataTablesModelBinder when binding the model.
            ModelBinders.Binders.Add(typeof(JQueryDataTablesModel), new JQueryDataTablesModelBinder());
        }
    }
}

You’ll notice that I updated the default route but also added code to the Application_Start() event that tells MVC when it sees a JQueryDataTablesModel as a parameter for one of my actions to use the JQueryDataTablesModelBinder that we created. Now add a Model called Customer with the following properties and constructors. The overloaded constructor will help with the testing project that I've included in my source code for this.

using System;

namespace jQuery.DataTables.Web.Models
{
    public class Customer
    {
        public Customer(string firstName,
            string lastName,
            string phoneNumber,
            int age,
            DateTime birthday)
        {
            FirstName = firstName;
            LastName = lastName;
            PhoneNumber = phoneNumber;
            Age = age;
            Birthday = birthday;
        }

        public Customer()
        {
        }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string PhoneNumber { get; set; }

        public int Age { get; set; }

        public DateTime Birthday { get; set; }
    }
}

We’re going to create an in-memory collection of Customers that we can do our filtering, sorting and paging against. Let’s create a folder called Code inside of our Mvc 3 project and add a class to it called InMemoryCustomersRepository.

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using jQuery.DataTables.Mvc;
using jQuery.DataTables.Web.Models;

namespace jQuery.DataTables.Web.Code
{
    /// <summary>
    /// Contains sorting helpers for In Memory collections.
    /// </summary>
    public static class CollectionHelper
    {
        public static IOrderedEnumerable<TSource> CustomSort<TSource, TKey>(this IEnumerable<TSource> items, SortingDirection direction, Func<TSource, TKey> keySelector)
        {
            if (direction == SortingDirection.Ascending)
            {
                return items.OrderBy(keySelector);
            }

            return items.OrderByDescending(keySelector);
        }

        public static IOrderedEnumerable<TSource> CustomSort<TSource, TKey>(this IOrderedEnumerable<TSource> items, SortingDirection direction, Func<TSource, TKey> keySelector)
        {
            if (direction == SortingDirection.Ascending)
            {
                return items.ThenBy(keySelector);
            }

            return items.ThenByDescending(keySelector);
        }
    }

    public static class InMemoryCustomersRepository
    {
        private static IList<Customer> GetAllCustomers()
        {
            var customers = new List<Customer>();
            customers.Add(new Customer(firstName: "Justin", lastName: "Michaels", age: 27, phoneNumber: "(123) 555-5555", birthday: Convert.ToDateTime("12/03/1984")));
            customers.Add(new Customer(firstName: "James", lastName: "Halk", age: 21, phoneNumber: "(123) 555-5554", birthday: Convert.ToDateTime("12/01/1990")));
            customers.Add(new Customer(firstName: "Lauren", lastName: "Waddams", age: 22, phoneNumber: "(123) 555-5553", birthday: Convert.ToDateTime("05/09/1990")));
            customers.Add(new Customer(firstName: "Dan", lastName: "Callahan", age: 35, phoneNumber: "(123) 555-5552", birthday: Convert.ToDateTime("12/09/76")));
            customers.Add(new Customer(firstName: "Kevin", lastName: "Kentucky", age: 40, phoneNumber: "(123) 555-5551", birthday: Convert.ToDateTime("12/08/1972")));
            customers.Add(new Customer(firstName: "Mike", lastName: "Peterson", age: 24, phoneNumber: "(123) 555-5550", birthday: Convert.ToDateTime("1/03/1988")));
            customers.Add(new Customer(firstName: "Tom", lastName: "Gun", age: 59, phoneNumber: "(123) 555-5559", birthday: Convert.ToDateTime("1/23/1953")));
            customers.Add(new Customer(firstName: "Erich", lastName: "Milton", age: 54, phoneNumber: "(123) 555-5558", birthday: Convert.ToDateTime("4/03/1958")));
            customers.Add(new Customer(firstName: "Jason", lastName: "Ralph", age: 27, phoneNumber: "(123) 555-5557", birthday: Convert.ToDateTime("11/03/1984")));
            customers.Add(new Customer(firstName: "Jarold", lastName: "Interface", age: 39, phoneNumber: "(123) 555-5556", birthday: Convert.ToDateTime("10/03/1972")));
            customers.Add(new Customer(firstName: "John", lastName: "Thompson21", age: 27, phoneNumber: "(123) 555-5545", birthday: Convert.ToDateTime("12/31/1984")));
            return customers;
        }

        public static IList<Customer> GetCustomers(int startIndex,
            int pageSize,
            ReadOnlyCollection<SortedColumn> sortedColumns,
            out int totalRecordCount,
            out int searchRecordCount, 
            string searchString)
        {
            var customers = GetAllCustomers();

            totalRecordCount = customers.Count;

            if (!string.IsNullOrWhiteSpace(searchString))
            {
                customers = customers.Where(c => c.FirstName.ToLower().Contains(searchString.ToLower())
                    || c.LastName.ToLower().Contains(searchString.ToLower())).ToList();
            }

            searchRecordCount = customers.Count;

            IOrderedEnumerable<Customer> sortedCustomers = null;
            foreach (var sortedColumn in sortedColumns)
            {
                switch (sortedColumn.PropertyName)
                {
                    case "FirstName":
                        sortedCustomers = sortedCustomers == null ? customers.CustomSort(sortedColumn.Direction, cust => cust.FirstName)
                            : sortedCustomers.CustomSort(sortedColumn.Direction, cust => cust.FirstName);
                        break;
                    case "LastName":
                        sortedCustomers = sortedCustomers == null ? customers.CustomSort(sortedColumn.Direction, cust => cust.LastName)
                            : sortedCustomers.CustomSort(sortedColumn.Direction, cust => cust.LastName);
                        break;
                    case "Age":
                        sortedCustomers = sortedCustomers == null ? customers.CustomSort(sortedColumn.Direction, cust => cust.Age)
                            : sortedCustomers.CustomSort(sortedColumn.Direction, cust => cust.Age);
                        break;
                    case "PhoneNumber":
                        sortedCustomers = sortedCustomers == null ? customers.CustomSort(sortedColumn.Direction, cust => cust.PhoneNumber)
                            : sortedCustomers.CustomSort(sortedColumn.Direction, cust => cust.PhoneNumber);
                        break;
                    case "Birthday":
                        sortedCustomers = sortedCustomers == null ? customers.CustomSort(sortedColumn.Direction, cust => cust.Birthday)
                            : sortedCustomers.CustomSort(sortedColumn.Direction, cust => cust.Birthday);
                        break;
                }
            }

            return sortedCustomers.Skip(startIndex).Take(pageSize).ToList();
        }
    }
}

The code is simply filtering on FirstName and LastName based on the global search string that was passed in the request by DataTables. This is pulled from the search textbox that is located on the top right hand side of the grid. I assign the totalRecordCount before doing my filtering in order to show how many records exist currently. Then I do my filtering based on the global search string and assign the searchRecordCount to the total after I have done my filtering. Also, the code has a check on the sorting functionality to see if the IOrderedEnumerable variable called sortedCustomers is null or not. The reason for this is each order by method call returns a newly IOrderedEnumerable of Customers. Since we're unsure of what will be the first column to sort by I add the check to each column that is allowed to be sorted by. After the first sort opeartion, each sort will use the previously preserved collection to do it's corresponding sort on in order to preserve the sorting order.

Now add a new Empty Controller called CustomerController to the MVC project and remove the Index action that it will generate. Add the following code to it which just contains a get and post action called Search. You’ll notice that the post action will be the action that receives the DataTables request and is in charge of sending the properly formatted JSON back in the response.

using System.Web.Mvc;
using jQuery.DataTables.Mvc;
using jQuery.DataTables.Web.Code;
using jQuery.DataTables.Web.Models;

namespace jQuery.DataTables.Web.Controllers
{
    public class CustomerController : Controller
    {
        [HttpGet]
        public ActionResult Search()
        {
            return View("Search");
        }

        [HttpPost]
        public JsonResult Search(JQueryDataTablesModel jQueryDataTablesModel)
        {
            int totalRecordCount;
            int searchRecordCount;
            var customers = InMemoryCustomersRepository.GetCustomers(startIndex: jQueryDataTablesModel.iDisplayStart,
                pageSize: jQueryDataTablesModel.iDisplayLength, sortedColumns: jQueryDataTablesModel.GetSortedColumns(), 
                totalRecordCount: out totalRecordCount, searchRecordCount: out searchRecordCount, searchString: jQueryDataTablesModel.sSearch);

            return Json(new JQueryDataTablesResponse<Customer>(items: customers, 
                totalRecords: totalRecordCount,
                totalDisplayRecords: searchRecordCount,
                sEcho: jQueryDataTablesModel.sEcho));
        }
    }
}

Finally all that is left to do is generate a folder inside of the Views folder called Customer. Let’s add a new strongly typed view called Search that uses the Customer model. We’ll need to declare our html table along with the corresponding columns then we’ll reference jQuery DataTables javascript file along with declaring the customer DataTable. Refer to the DataTables documentation for this at http://datatables.net/release-datatables/examples/data_sources/server_side.html

@{
    ViewBag.Title = "Search";
}
<h2>
    Search</h2>
<table id="customers">
    <thead>
        <tr>
            <th>
                Last Name
            </th>
            <th>
                First Name
            </th>
            <th>
                Age
            </th>
            <th>
                Birthday
            </th>
            <th>
                Phone Number
            </th>
        </tr>
    </thead>
</table>
<script src="@Url.Content("~/Scripts/DataTables-1.9.1/media/js/jquery.dataTables.js")"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#customers').dataTable({
            "bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": document.URL,
            "sServerMethod": "POST",
            "aoColumns": [
            { "mDataProp": "LastName" },
            { "mDataProp": "FirstName" },
            { "mDataProp": "Age" },
            { "mDataProp": "Birthday" },
            { "mDataProp": "PhoneNumber"}],
            "fnRowCallback": function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {
                var birthday = new Date(parseInt(aData.Birthday.replace("/Date(", "").replace(")/", ""), 10));
                $('td:eq(3)', nRow).html(birthday.getMonth() + 1 + "/" + birthday.getDate() + "/" + birthday.getFullYear());
            }
        });
    });
</script>

Our dataTable declaration is telling DataTables to use the default processing overlay while waiting for the response from the server. Also, that the dataTable is using a server side implementation and to use the current url when making the ajax post call. There’s a couple of different ways to define your columns with DataTables which can be found at http://datatables.net/usage/columns. I’ve chosen to define all of the columns and their JSON property names with the mDataProp property. The mDataProp definitions are used to grab the corresponding values out of the JSON response from the server. There's also a fnRowCallBack function that I've used in order to transform the DateTime values that were sent in the JSON. The JSON spec does not account for Date values and therefore Microsoft decided a while back to represent DateTime values in JSON as \/Date(ticks)\/. So in order to get the proper date for the property I must remove the /Date( and the )/ characters which will leave us with new Date(/ticks/). Once I have the Date object created in JavaScript I can retrieve the date to present it on screen.

You should now have filtering, sorting and paging functionality at the server side with jQuery DataTables and MVC. The source code for this can be downloaded from BitBucket at https://bitbucket.org/justinmichaels/jquery.datatables.

asp.netjquerysortingfilteringmvcjquery dataTables
Posted by: Justin Michaels
Last revised: 02 Aug, 2012 11:45 PM

Comments

No comments yet. Be the first!

blog comments powered by Disqus