.NET Development Examined

MVC and DataTables Part II

Monday, November 28, 2011

In the first posting (MVC and DataTables) in this series we got a simple DataTables page up and running in MVC. But we are not done yet!

It’s worth noting that there are several options you can pass to the DataTables constructor. If you want more than just the Previous/Next pagination you can pass “sPaginationType”: “full_numbers”. If you want the layout to follow your jQuery UI theme pass “bJWueryUI”: true. See http://www.datatables.net/usage/options for more details on the options DataTables supports.

If you have a limited number of records you are dealing with this simple process works ok, however if you have a large number of records performance starts to degrade, and you will get a "Flash of Uninitialized JavaScript UI," or FOUJUI as my friend Todd Anglin would call it. Basically you will momentarily see the HTML table in its raw format before the JavaScript kicks in and applies its layout. Try changing the number of records being returned in the controller to a higher value to see this effect.

So how can we prevent the FOUJUI? There are several methods, a common one is to implement a loading page which hides the table while it is being formatted, but sending thousands of records across the wire to build the table is not ideal from other aspects as well. So let’s see how we can implement some AJAX to request only the data being displayed.

We will create another action in the Home controller; I called mine ‘AJAX’. Right click and create a new view, we won’t generate or retrieve any data here so just return a basic view.

In the newly created view we need a table place holder for DataTables to work with, but this time we won’t be using the @foreach to build it out. So create the table layout as shown:

<table class="display" id="example"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Phone</th> </tr> </thead> <tbody> <tr> <td></td> <td></td> <td></td> </tr> </tbody> </table>

We still need to add the jQuery magic, so in the @section top add:

@section top{ <script type="text/javascript" charset="utf-8"> $(document).ready(function () { /* Init the table */ oTable = $('#example').dataTable({ "bServerSide": true, "sAjaxSource": "/Home/GetRecords", "sAjaxDataProp": "aaData", "bProcessing": true, "sPaginationType": "full_numbers", "aoColumns": [ { "mDataProp": "Name" }, { "mDataProp": "Email" }, { "mDataProp": "Phone" } ] }); }); </script> }

You will notice we have added several options to the .dataTable call, let’s examine them one by one. The bServerSide option lets DataTable know it is not responsible for processing any of the information, we are going to handle the sorting, filtering, paging in our controller. The sAjaxSource is the URL where DataTable will make its AJAX calls.

The sAjaxDataProp option lets DataTable know what JSON member to look for data in. aaData is the default, and we must ensure that our Controller matches up with this value when returning information. The bProcessing option tells DataTable to put a nice “processing” wait graphic while data is being retrieved.

The sPaginatinType tells DataTable to use number pagination navigation, not just previous next. The aoColumns defines the columns we want to display.

So far so good, everything makes sense and is pretty intuitive. Let’s move on to our controller and create the GetRecords AJAX result.

But before we get the controller working we need to handle the parameters passed by DataTables to the AJAX URL. I found a post on CodeProject (http://www.codeproject.com/KB/aspnet/JQuery-DataTables-MVC.aspx) which provided a thorough outline and even goes over multi-column sorting. If you want to explore DataTables and MVC further I suggest checking it out. One of the cool things outlined in this article was the creation of a custom class to handle parameters submitted by DataTables to an AJAX URL. So create a new model class with the following:

public class jQueryDataTableParamModel { /// Request sequence number sent by DataTable, /// same value must be returned in response public string sEcho { get; set; } /// Text used for filtering public string sSearch { get; set; } /// Number of records that should be shown in table public int iDisplayLength { get; set; } /// First record that should be shown(used for paging) public int iDisplayStart { get; set; } /// Number of columns in table public int iColumns { get; set; } /// Number of columns that are used in sorting public int iSortingCols { get; set; } /// First sort column numeric index, possible to have /// _1,_2 etc for multi column sorting public int iSortCol_0 { get; set; } /// Sort direction of the first sorted column, asc or desc public string sSortDir_0 { get; set; } /// Comma separated list of column names public string sColumns { get; set; } }

Jovon Popovic commented this class well, so there is not much to add here. Let’s start on our Controller. Open up the Home controller and add a new ActionResult:

public ActionResult GetRecords(jQueryDataTableParamModel param) { List employees = new List(); for (int x = 0; x < 5000; x++) { employees.Add(new SampleEmployee { Name = "Tom " + x, Email = "tom" + x + "@nowhere.com", Phone = "999-999-" + x.ToString().PadLeft(4, '9') }); } List employeesToTake = new List(); employeesToTake = employees.Skip(param.iDisplayStart) .Take(param.iDisplayLength) .ToList(); return Json(new { sEcho = param.sEcho, iTotalRecords = employees.Count(), iTotalDisplayRecords = employees.Count(), aaData = employeesToTake }, JsonRequestBehavior.AllowGet); }

Next Page


Add Comment