Sunday, June 3, 2012

Ajax data paging with dataTables.net jQuery plugin in asp.net MVC 3

In this post we will be discussing how to do ajax based server side data paging with dataTables.net jQuery plugin in asp.net MVC 3. To continue this lets check following post –

http://datatables.net/examples/data_sources/ajax.html

This is a basic way to access ajax data paging. In this sample all the data needed is retrieved and paging is done in browser memory. But we need more sophisticated approach. That is separate ajax call for each page index change. We can follow the following post for this-

http://datatables.net/examples/data_sources/server_side.html

This is a PHP based code. Let’s do in MVC. Before starting, let’s do some background study on the same.

First case: Let’s see the request on the IE developer tool network tab. If we visit request header and request value we can get data like below-

/examples/examples_support/server_processing.php?sEcho=1&iColumns=5&sColumns=&iDisplayStart=0&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&…………….. The bolded text query strings are the required ones. The additional query string can be used are we need but these are out of scope for the current sample.

Second case: Now let’s go to the Response Body. We can get the result like below-

{"sEcho": 1, "iTotalRecords": 57, "iTotalDisplayRecords": 57, "aaData": [ ["Gecko","Firefox 1.0","Win 98+ / OSX.2+","1.7","A"],["Gecko","Firefox 1.5","Win 98+ / OSX.2+","1.8","A"],["Gecko","Firefox 2.0","Win 98+ / OSX.2+","1.8","A"],["Gecko","Firefox 3.0","Win 2k+ / OSX.3+","1.9","A"],["Gecko","Camino 1.0","OSX.2+","1.8","A"],["Gecko","Camino 1.5","OSX.3+","1.8","A"],["Gecko","Netscape 7.2","Win 95+ / Mac OS 8.6-9.2","1.7","A"],["Gecko","Netscape Browser 8","Win 98SE+","1.7","A"],["Gecko","Netscape Navigator 9","Win 98+ / OSX.2+","1.8","A"],["Gecko","Mozilla 1.0","Win 95+ / OSX.1+","1","A"]] }

Here we can see that it returning the sEcho, iTotalRecords, iTotalDisplayRecords, aaData where first three values are integer type and last one is the array of data rows. We can visualize this in the image below-



Now if we think this in terms of MVC model binding things will get clear. We can use a model data class that except the required input parameters and return a JSON result of the desired format. Lets start with the source code-

ViewModel-
    public class TableParameter
    {
        public string sEcho { get; set; }
        public int iDisplayStart { get; set; }
        public int iDisplayLength { get; set; }
    }
In case of the parameter we are using only three here as explained in the first case. We can use other parameters as per our need.

View-
@{
    ViewBag.Title = "Pagination_With_Data_Table_issue";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>
    Pagination_With_Data_Table_issue</h2>
    
<style type="text/css" title="currentStyle">
   @@import "http://datatables.net/release-datatables/media/css/demo_page.css";
   @@import "http://datatables.net/release-datatables/media/css/demo_table.css";
  </style>
<script type="text/javascript" language="javascript" src="http://datatables.net/release-datatables/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="http://datatables.net/release-datatables/media/js/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8">
    $(document).ready(function () {
        $('#example').dataTable({
            "bServerSide": true,
            "bProcessing": true,
            "sPaginationType": "full_numbers",
            "sAjaxSource": '@Url.Action("Pagination_With_Data_Table_issue_getData")'
        });
    });
</script>
<div id="dynamic">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
 <thead>
  <tr>
   <th width="20%">Rendering engine</th>
   <th width="25%">Browser</th>
   <th width="25%">Platform(s)</th>
   <th width="15%">Engine version</th>
   <th width="15%">CSS grade</th>
  </tr>
 </thead>
 <tbody>
 </tbody>
 <tfoot>
  <tr>
   <th>Rendering engine</th>
   <th>Browser</th>
   <th>Platform(s)</th>
   <th>Engine version</th>
   <th>CSS grade</th>
  </tr>
 </tfoot>
</table>
</div>
While initialization of the table plugin, we are using bServerSide and bProcessing to true, sPaginationType to full_number and finally sAjaxSource to controller action.

Controller action-
        public ActionResult Pagination_With_Data_Table_issue()
        {
            return View();
        }

        public ActionResult Pagination_With_Data_Table_issue_getData(TableParameter param)
        {
            var data = new[]{   
                new []{"Trident test","Internet Explorer 4.0","Win 95+","4","X"},
             new []{"Trident","Internet Explorer 5.0","Win 95+","5","C"},
             new []{"Trident","Internet Explorer 5.5","Win 95+","5.5","A"},
             new []{"Trident","Internet Explorer 6","Win 98+","6","A"},
             new []{"Trident","Internet Explorer 7","Win XP SP2+","7","A"},
             new []{"Trident","AOL browser (AOL desktop)","Win XP","6","A"},
             new []{"Gecko","Firefox 1.0","Win 98+ / OSX.2+","1.7","A"},
             new []{"Gecko","Firefox 1.5","Win 98+ / OSX.2+","1.8","A"},
             new []{"Gecko","Firefox 2.0","Win 98+ / OSX.2+","1.8","A"},
             .
                .
                .
                .
                .
            };

            var pagedData = data.Skip(param.iDisplayStart).Take(param.iDisplayLength);

            return Json(new
            {
                sEcho = param.sEcho,
                iTotalRecords = data.Count(),
                iTotalDisplayRecords = data.Count(),
                aaData = pagedData
            }, JsonRequestBehavior.AllowGet);
        }
In this case we are using the input parameter for filtering the correct data. Finally the return JSON matches the format explained in the second case. The array of data we have prepared form the following link-

http://datatables.net/examples/examples_support/json_source.txt

This explains everything. Let me know for any query. You can download the code here.

5 comments:

  1. Hello Anup,This is nice post,i have followed this post to implement the same.but when my repsonse is written to the webpage.it shows the blank response string instead of my page and table.although correct format of aadata is being passed to the client.

    ReplyDelete
  2. {"sEcho":"1","iTotalRecords":20,"iTotalDisplayRecords":20,"aaData":[["# 007 A 24 Hour Locksmith","Las Vegas","Locks & Locksmiths","89119-1671","7028735027"],["# 007 Day Fast Locksmith","Las Vegas","Locks & Locksmiths","89128-4329","7027273418"],["# 1 & B\u0027Yond Gift Shop","Cape Coral","Gift Shops","33909-2264","2397728273"],["# 1 24 Hour Available Lcksmth","Las Vegas","Locks & Locksmiths","89117-2769","7027273622"],["# 1 Best Copyright Agency","Houston","Attorneys","77266-7352","7132782214"],["# 1 Call Bail Bond","Grand Haven","Bonds-Bail","49417","6168440911"],["# 1 Call Bail Bond Agency","Saginaw","Bonds-Bail","48601","9897572245"],["# 1 China","Cairo","Restaurants","39828-1603","2293788828"],["# 1 China Buffet","Kennett","Restaurants","63857-2531","5738888989"],["# 1 Chinese Bbq Restaurant","Eleele","Restaurants","96705-9997","8083356888"],["# 1 Designer Touch Unisex","Norfolk","Beauty Salons","23505-2803","7575833020"],["# 1 Diamonds Chicago Inc","Chicago","Jewelers-Retail","60603-3527","7733832440"],["# 1 Laundromat","Union","Laundries-Self Service","63084-1908","6365830813"],["# 1 Locksmith 24 Hour","Columbia","Locks & Locksmiths","21044",""],["# 1 Locksmith 24 Hour","Gwynn Oak","Locks & Locksmiths","21207","4105519677"],["# 1 Locksmith 24 Hour","Catonsville","Locks & Locksmiths","21228","4107477322"],["# 1 Mobile Dj Svc","Las Vegas","Music & Live Entertainment","89108","7026360397"],["# 1 Nails","Hollywood","Manicuring","33024-4017","9548930016"],["# 1 Nails","Yorkville","Manicuring","60560-1952","6305524433"]]}

    ReplyDelete
    Replies
    1. Hi,
      Download the source code give in the post and execute and then see what you are missing.

      Delete
  3. Thanks a lot for the post. It's 100% working!
    Sometimes the jQuery library we link to could be conflicting with the ones in the master layout.cshtml page. Such cases we can probably add a view without a master and try this code. That could be working (for those who have been having issues with the rendering.) These are the newest JavaScript files for dataTable to load:
    //code.jquery.com/jquery-1.11.1.min.js
    //cdn.datatables.net/1.10.5/js/jquery.dataTables.min.js
    //cdn.datatables.net/1.10.5/css/jquery.dataTables.css
    Try this and see. Good luck!
    -kickAssDeveloper

    ReplyDelete