Grid in MVC using Datatables

6:32 AM Shashank Tiwary 0 Comments

I rewrite this article to add some more information about how to use data tables (a Jquery plugin) in a MVC project. As because it is going to be a big article therefore I decided to split this into two small articles. The first article demonstrate you to convert a simple HTML table into a nice looking data grid and also add data loading and searching power from server side. And then in second article we will write a simple plugin for data tables for enhancing the search functionality. As we step further with this article I also recommand you to look parallel at the data tables site for more in depth information.

What Exactly DataTables Is ?

"DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table ."

Below is a small code sample that attach the data tables to a normal html table.

$(document).ready(function(){
$("#tableid").datatable();
});

Don't forget to include the script and css file for the datatable.

<link href="@Url.Content("~/media/css/jquery.dataTables_themeroller.css")" rel="stylesheet" type="text/css" />

<script src="@Url.Content("~/media/js/jquery.dataTables.min.js")" type="text/javascript"></script>

Look at jsbin cast...
JS Bin

Now as we have nice grid we now try to bind our grid using a ajax call and also use the MVCContrib Grid to create table.

@{Html.Grid(Model).Columns(column =>
  {
      column.For(c => c.DebtorName).Named("Debtor");
      column.For(c => c.Iban);
      column.For(c => c.Bic);
      column.For(c => c.IsDeleted).Named("Hidden");
  }).Attributes(id => "tableid").Render();
 }

Setting option for data tables to populate data from server using Ajax Post call.

oTable = $('#tableid').dataTable({
                "bProcessing": true,
                "bServerSide": true,
                "sServerMethod": "POST",
                "sAjaxSource": "/data/debtors",
                "aoColumnDefs": [
                    { "mDataProp": "Debtor", "aTargets": [0] },
                    { "mDataProp": "Iban", "aTargets": [1] },
                    { "mDataProp": "Bic", "aTargets": [2] }
                ],
                "aaSorting": [[1, 'desc']]
               });

Action code

[Post]
public JsonResult debtors()
{
   return View(GetAllDebtor().Select(d=> new {Debtor = c.debitor,Iban=c.iban,Bic=c.bic}));
}

Here setting bProcessing option to true tells data table that it should automatically call server to load data when page load in browser. The property aoColumnDefs use to define columns behaviour. The most important property mDataProp use to define JSON data (sent by the server) property name that data tables should use to populate the column.
Datatables support long range of option from custom data column rendering to custom data loading. For short example mRender is property which take funtion object that execute for every row in table when data populated by data tables.

Now we are proceed on development of custom model binder for our data table. This will be useful to leverage the sorting, searching and paging functionality on server side. When Data tables request data from the server it passes some useful parameter to server on the basis of which server can filter, sort and most important can page data. I would suggest you to use some tool like firebug to trace these parameters for more understanding. Below is our code for custom model binder,

public class GridRequestModelBinder : ModelBinderBase, IModelBinder
    {
        public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            GridRequest GridrequestObject = new GridRequest();
            // iColumns no of cols
            GridrequestObject.iColumns = Convert.ToInt32(GetValue(bindingContext, "", "iColumns"));
            //iDisplayLength
            GridrequestObject.iDisplayLength = Convert.ToInt32(GetValue(bindingContext, "", "iDisplayLength"));

            //iDisplayStart
            GridrequestObject.iDisplayStart = Convert.ToInt32(GetValue(bindingContext, "", "iDisplayStart"));

            //bregex
            GridrequestObject.bRegex = Convert.ToBoolean(GetValue(bindingContext, "", "bRegex"));

            //bregex_params
            GridrequestObject.bRegex_params = GetValue(bindingContext, "_", "bRegex", GridrequestObject.iColumns);

            //bSearchable

            GridrequestObject.bSearchable_params = GetValue(bindingContext, "_", "bSearchable", GridrequestObject.iColumns);

            //iSortingCols ** 1
            // iSortingCols 
            GridrequestObject.iSortingCols = Convert.ToInt32(GetValue(bindingContext, "", "iSortingCols"));
            //iSortCol_param * 1
            GridrequestObject.iSortCol_params = GetValue(bindingContext, "_", "iSortCol", GridrequestObject.iSortingCols);

            //bSortable
            GridrequestObject.bSortable_params = GetValue(bindingContext, "_", "bSortable", GridrequestObject.iColumns);

            //sSortDir_params * 1
            GridrequestObject.sSortDir_params = GetValue(bindingContext, "_", "sSortDir", GridrequestObject.iSortingCols);
            //mDataProp_params
            GridrequestObject.mDataProp_params = GetValue(bindingContext, "_", "mDataProp", GridrequestObject.iColumns);

            //sColumns 
            GridrequestObject.sColumns = GetValue(bindingContext, "", "sColumns");
            //sEcho 
            GridrequestObject.sEcho = Convert.ToInt32(GetValue(bindingContext, "", "sEcho"));
            //sSearch
            GridrequestObject.sSearch = GetValue(bindingContext, "", "sSearch");
            // sSearch_params
            GridrequestObject.mDataProp_params = GetValue(bindingContext, "_", "mDataProp", GridrequestObject.iColumns);

            // sSearch_params
            GridrequestObject.sSearch_params = GetSearchParams(bindingContext, GridrequestObject.mDataProp_params);

            return GridrequestObject;
        }

        private Dictionary<string,string> GetSearchParams(ModelBindingContext bindingContext, Dictionary<string,string> Propdictionary)
        {
            Dictionary<string,string> RetDicxs = new Dictionary<string,string>();
            for (int i = 0; i < Propdictionary.Count; i++)
            {
                RetDicxs.Add(Propdictionary["mDataProp_" + i.ToString()], GetValue(bindingContext, "_" + i.ToString(), "sSearch"));
            }
            return RetDicxs;
   }

public class ModelBinderBase
    {
        public string GetValue(ModelBindingContext context, string postfix, string key)
        {
            ValueProviderResult vpr = context.ValueProvider.GetValue(key + postfix);
            return vpr == null ? null : vpr.AttemptedValue;
        }

        public Dictionary<string,string> GetValue(ModelBindingContext context, string postfix, string key, int MaxParam)
        {
            Dictionary<string,string> RetDicxs = new Dictionary<string,string>();
            for (int i = 0; i < MaxParam; i++)
            {
                RetDicxs.Add(key + postfix + i.ToString(), GetValue(context, postfix + i.ToString(), key));
            }
            return RetDicxs;
        }
    }
public class GridRequest
    {
        public bool bRegex { get; set; } // Regular expression for _sSearch Text
        public Dictionary<string,string> bRegex_params { get; set; } // Regular expression for _sSearch[i] Text

        private string _sSearch; // text of search box
        public bool bSearchable { get; set; } // do client want to search
        public string sSearch
        {
            get { return _sSearch; }
            set { _sSearch = value == null ? "" : value; }
        }
        public Dictionary<string,string> bSearchable_params { get; set; } // do client want to search on particular column with different search text
        public Dictionary<string,string> sSearch_params; // search on particular column with different search text

        public int iSortingCols { get; set; }
        public Dictionary<string,string> iSortCol_params { get; set; }
        public Dictionary<string,string> bSortable_params { get; set; }
        public Dictionary<string,string> sSortDir_params { get; set; }

        public int iColumns { get; set; }
        public string sColumns { get; set; }

        public int sEcho { get; set; }
        public int iDisplayStart { get; set; } // Start Index
        public int iDisplayLength { get; set; } // Complete number of records

        public Dictionary<string,string> mDataProp_params { get; set; }
    }

Now we cooked our model binder for data tables its time to use it. For that all I want to say is we already done the hard work. Please don't forget to add the model binder at Application start.

ModelBinders.Binders.Add(new typeof(GridRequest), new GridRequestModelBinder());

Below is a simple code for enabling search on data table from server side.

public JsonResult debtors(GridRequest greq)
{
  return View(GetAllDebtor()
 .Where(d=>d.debitor.Contains(greq.sSearch)||d.iban.Contains(greq.sSearch)||d.bic.Contains(greq.sSearch)
 .Select(d=> new {Debtor = c.debitor,Iban=c.iban,Bic=c.bic}));
}

The above code simply search all the columns for the entered query. Data tables use sSearch parameter to send the query text to the server. After expending several hour you may notice that data table only provide single text box for querying data. This gives the point of birth for my second article on writing a plugin for enhancing search functionality for the data tables. Data table provide a large set of API that make way lot easier to write plugin for additional functionality on it. The first part is completed here and we will meet on second part later.