Thursday, December 16, 2010

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.

9 comments:

Muhammad Sheraz Lodhi said...

This indeed of great help when working with huge data.
i was looking for a way to do this with JQuery Datatables and this will save a lot of time
thanks

Usman Shabbir said...

Thanks

umar.malik said...

awesome! i have been trying to qorkaround this but in vien..ur blog is a sigh of relief.. great work man!

Rodrigo Dumont said...

Thank you for the tip!

Usman Shabbir said...

Any time :)

prashant said...

Thanks for such a useful tetorial..Awsome work..:)

Rashid Mohammed said...

Awsome work its realy helpfull...thnks

Rashid Mohammed said...

is possible server side filtering also?

kaushal sutariya said...

send full server side datatable code fast get data from server