Extending jQuery Datatable with server side processing and pagination.

Hi All,

Once again i came up with a blog for jQuery Datatable as i really liked this control and love to extend its functionality for .NET, in my previous blog i have bound jQuery Data table with dataset at once which might not be very useful then data is so large as fetching complete data in one hit is not a good job.

So Today we will do some bServerSide processing and make this data table more useful in fetching records from database with pagination and sorting enabled i am still not able to coupe with free text search in returned results but ill share that soon as well.

first step would be to create a handler / web service which we would be passing in the argument sAjaxSource of datatable.

Remember that the response should be a JSON formated text as datatable only reads JSON in adata parameter.

Lets start with the Handler.

<%@ WebHandler Language="C#" Class="Search" %>

using System;
using System.Web;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using System.Linq;
using System.Data;
using DAL.Modules;
using DAL;

public class Search : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        // Those parameters are sent by the plugin
        var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
        var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
        var iSortCol = int.Parse(context.Request["iSortCol_0"]);
        var iSortDir = context.Request["sSortDir_0"];


        IEnumerable<SearchResults> myResults = SearchResults.GetResults(iDisplayStart, iDisplayLength);

        // Define an order function based on the iSortCol parameter
        Func<SearchResults, object> order = rslt =>
        {
            if (iSortCol == 0)
                return rslt.Project_Name;
            else if (iSortCol == 1)
                return rslt.Project_Goal;
            else if (iSortCol == 2)
                return rslt.Proposed_Budget;
            else if (iSortCol == 3)
                return rslt.Status;
            else
                return rslt.Creation_Date;
        };

        // Define the order direction based on the iSortDir parameter
        if ("desc" == iSortDir)
        {
            myResults = myResults.OrderByDescending(order);
        }
        else
        {
            myResults = myResults.OrderBy(order);
        }


        // Remove Skip and Take when doing Custom Pagination from Database. 
        var result = new
        {
            iTotalRecords = myResults.Count(),
            iTotalDisplayRecords = myResults.Count(),
            aaData = myResults
                .Select(p => new[] { p.Project_Name, p.Project_Goal, p.Proposed_Budget, p.Status, p.Creation_Date, p.View_Details })
                .Skip(iDisplayStart)
                .Take(iDisplayLength)

        };

        var serializer = new JavaScriptSerializer();
        var json = serializer.Serialize(result);
        context.Response.ContentType = "application/json";
        context.Response.Write(json);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

So parsing JSON would require a class structure.

public class SearchResults
{
    public string Project_Name { get; set; }
    public string Project_Goal { get; set; }
    public string Status { get; set; }
    public string Proposed_Budget { get; set; }
    public string Creation_Date { get; set; }
    public string View_Details { get; set; }

    public static IEnumerable<SearchResults> GetResults(int RecordId, int count)
    {
        DataSet ds = null;// Get you Dataset from Database by passing the two parameters for pagination. 

        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                yield return new SearchResults
                {
                    Project_Name = Helper.ParseString(dr["Project_Name"]),
                    Project_Goal = Helper.ParseString(dr["Project_Goal"]),
                    Status = Helper.ParseString(dr["Status"]),
                    Proposed_Budget = Helper.ParseString(dr["Proposed_Budget"]),
                    Creation_Date = Helper.ParseString(DateTime.Now),
                    View_Details = "<a href='#'>View</a>"
                };
            }
    }
}

Now its time for the final step calling it in ASPX page.

the javascript for table would be

<script type="text/javascript">
        $(function () {
            $('#.datatable').dataTable({
                'bServerSide': true,
                "iDisplayLength": 2,
                "sPaginationType": "full_numbers",
                'sAjaxSource': '/Handlers/Search.ashx',
                "bFilter": false
            });
        });
    </script>

now finally we come to the last step which all of you must be familiar of is the HTML table which will turn into the jQuery Datatable.

<table cellpadding="0" cellspacing="0" border="0" class="datatable">
                    <thead>
                        <tr>
                            <th>
                                Project Name
                            </th>
                            <th>
                                Project Goal
                            </th>
                            <th>
                                Proposed Budget
                            </th>
                            <th>
                                Status
                            </th>
                            <th>
                                Creation Date
                            </th>
                            <th>
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td colspan="5" class="dataTables_empty">
                                <img alt="Please Wait..." src="/images/loader.png" />
                            </td>
                        </tr>
                    </tbody>
                </table>

That's All if you find any query please leave a comment.

Happy Coding.