Passing extra parameters with dataTable.net plugin while server paging is enabled in asp.net MVC 3 and Web form

Recently I have added two posts to implement dataTable.net jQuery plugin with server side paging with MVC3 and asp.net web form. First one is in MVC 3 and second one is in web form. We can find these two posts here-

http://growingtech.blogspot.in/2012/06/ajax-data-paging-with-datatablesnet.html
http://growingtech.blogspot.in/2012/06/ajax-data-paging-with-datatablesnet_06.html

I will request to study these two posts in sequentially to understand this one.

In this post we will be discussing how to pass additional parameters when page index changes happen in dataTable.net. To pass additional parameters in ajax paging we can use fnServerParams function of dataTable. In the MVC post we have discussed how dataTable plugin adds request parameters as querystring. And this function (fnServerParams) provides an input parameter which is nothing but an array of the query string parameters. We can do modification to the array in the function to accomodiate our need of extra parameters. Each array element in the input array is an object of the following form-

{ "name": "name of parameter/querystring", "value": "value of parameter/querystring " }

So, we can add our additional parameter accordingly. Suppose we have following input data as additional parameter-

First Criteria: <input id="txtParameter1" /><br />
Second Criteria : <input id="txtParameter2" />

We can add the these extra parameter like below-

<script type="text/javascript" charset="utf-8">
    $(document).ready(function () {
        $('#example').dataTable({
            "bServerSide": true,
            "bProcessing": true,
            "sPaginationType": "full_numbers",
            "sAjaxSource": 'Datatable Plugin Pagination Issue AjaxPath.aspx',
            "fnServerParams": function (aoData) {
                aoData.push(
                    { "name": "firstcriteria", "value": $("#txtParameter1").val() },
                    { "name": "secondcriteria", "value": $("#txtParameter2").val() }
                );
            }
        });
    });
</script>

In web form:

In web form we can access these parameters like below and use for database operations –

var firstCriteria = Request.QueryString["firstcriteria"];
var secondCriteria = Request.QueryString["secondcriteria"];

In MVC 3-

To use in MVC 3 we can modify data model like below to accommodate our new parameters-

    public class TableParameter
    {
        public string sEcho { get; set; }
        public int iDisplayStart { get; set; }
        public int iDisplayLength { get; set; }
        public string firstcriteria { get; set; }
        public string secondcriteria { get; set; }
    }

Input data will be available in the controller method to use.

Ajax data paging with dataTables.net jQuery plugin in asp.net web form

I have recently added a post on calling ajax data to dataTables.net jQuery plugin with asp.net MVC 3. You can refer the following post-

http://growingtech.blogspot.in/2012/06/ajax-data-paging-with-datatablesnet.html

I will suggest to study the above post before proceeding this post. But the approach does not work with web form. So, how to do it with web form. As per my knowledge ajax paging with dataTable.net does not work properly with pagemethod or web service. We can solve this with an aspx page directly. But please take care of the security constraints as per your requirement.

First add a aspx page separate from your previous page. And change the sAjaxSource of the dataTable plugin like-

"sAjaxSource": 'Datatable Plugin Pagination Issue AjaxPath.aspx'

And in page load of the page use the following code-

public partial class Datatable_Plugin_Pagination_Issue_AjaxPath : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        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 []{"Other browsers","All others","-","-","U"}
            };
        var pagedData = data.Skip(int.Parse(Request.QueryString["iDisplayStart"])).Take(int.Parse(Request.QueryString["iDisplayLength"]));
        System.Web.Script.Serialization.JavaScriptSerializer toJSON = new System.Web.Script.Serialization.JavaScriptSerializer();
        Response.Clear();
        string dataString = toJSON.Serialize(new
        {
            sEcho = Request.QueryString["sEcho"],
            iTotalRecords = data.Count(),
            iTotalDisplayRecords = data.Count(),
            aaData = pagedData
        });
        Response.Write(dataString);
        Response.End();
    }
}

That's all. Problem solved.

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.