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.