Jquery Datatable: Implementing Load on demand Server side with PageMethod/WebMethod

Jquery Datatable is one of the best plugin which we can use in alternate of GridView.

Though there are so many examples available at https://www.datatables.net/ for sever side processing with .txt or .php as AjaxSource. But there isn’t full example on achieving Load on demand with server side processing in asp.net by using PageMethod/WebMethod.

So, if you are looking for similar solution, please walk through below sample code.

Important: Before jumping into the code, it can be beneficial if you have some knowledge of datatable terminology and parameters we send to PageMethod/WebMethod or Parameters we return from PageMethod/WebMethod.

Click here for more information on Parameters sent to the server and returned from server.

Click here for more information on Datatable Methods.

CSS & JS

	

Default.aspx



<div id="container">

<table id="JQtblUsers" class="display">

<thead>

<tr>

<th>Email</th>


<th>First Name</th>


<th>Last Name</th>


<th>Company</th>

</tr>

</thead>


<tbody>
</tbody>

</table>

</div>

    

        $(document).ready(function () {
            var oTable = $('#JQtblUsers').dataTable({
                "paging": true,
                "sPaginationType": "full_numbers",
                "bServerSide": true,
                "sAjaxSource": "Default3.aspx/GetUsers",
                "fnServerData": function (sSource, aoData, fnCallback) {
                    logsRequest = $.ajax({
                        type: "POST",
                        url: sSource,
                        data: "{'sEcho': '" + getValueFromArray(aoData, "sEcho") + "', 'sSearch': '" + getValueFromArray(aoData, "sSearch")
                            + "', 'iDisplayLength': '" + getValueFromArray(aoData, "iDisplayLength") + "', 'iDisplayStart': '" + getValueFromArray(aoData, "iDisplayStart")
                            + "', 'iColumns': '" + getValueFromArray(aoData, "iColumns") + "', 'iSortingCols': '" + getValueFromArray(aoData, "iSortingCols")
                            + "', 'sColumns': '" + getValueFromArray(aoData, "sColumns") + "'}",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function (data) {
                            var json = jQuery.parseJSON(data.d);
                            fnCallback(json);
                        }
                    });
                },
                "aoColumns": [
                                {
                                    "sName": "Email",
                                    "bSearchable": true,
                                    "bSortable": true,
                                    "fnRender": function (oObj) {
                                        return '<a href="mailto:' + oObj.aData&#91;0&#93; + '">' + oObj.aData[0] + '</a>';
                                    }
                                },
                                {
                                    "sName": "FirstName",
                                    "bSearchable": true,
                                    "bSortable": true,
                                    "fnRender": function (oObj) {
                                        return oObj.aData[1];
                                    }
                                },
                                {
                                    "sName": "LastName",
                                    "bSearchable": true,
                                    "bSortable": true,
                                    "fnRender": function (oObj) {
                                        return oObj.aData[2];
                                    }
                                },
                                {
                                    "sName": "Company",
                                    "bSearchable": true,
                                    "bSortable": true,
                                    "fnRender": function (oObj) {
                                        return oObj.aData[3];
                                    }
                                }
                ],
            });
        });

        function getValueFromArray(aoData, Key) {
            for (i = 0; i &lt; aoData.length; i++) {
                if (aoData[i].name == Key) {
                    return aoData[i].value;
                }
            }
        }
    

Default.aspx.cs

#region Class

public class Employee
{
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Company { get; set; }
}

#endregion Class
#region Methods

    [WebMethod]
    public static string GetUsers(string sEcho, string sSearch, int iDisplayLength, int iDisplayStart, int iColumns, int iSortingCols, string sColumns)
    {
        var echo = int.Parse(sEcho);
        var displayLength = iDisplayLength;
        var displayStart = iDisplayStart;

        var records = GetRecordsFromDatabaseWithFilter(sSearch);
        if (records == null)
            return string.Empty;

        var itemsToSkip = displayStart == 0 ? 0 : displayStart + 1;
        var pagedResults = records.Skip(itemsToSkip).Take(displayLength).ToList();
        var hasMoreRecords = false;

        var sb = new StringBuilder();
        sb.Append(@"{" + "\"sEcho\": " + echo + ",");
        sb.Append("\"recordsTotal\": " + records.Count + ",");
        sb.Append("\"recordsFiltered\": " + pagedResults.Count + ",");
        sb.Append("\"iTotalRecords\": " + records.Count + ",");
        sb.Append("\"iTotalDisplayRecords\": " + records.Count + ",");
        sb.Append("\"aaData\": [");
        foreach (var result in pagedResults)
        {
            if (hasMoreRecords)
                sb.Append(",");

            sb.Append("[");
            sb.Append("\"" + result.Email + "\",");
            sb.Append("\"" + result.FirstName + "\",");
            sb.Append("\"" + result.LastName + "\",");
            sb.Append("\"" + result.Company + "\"");
            sb.Append("]");
            hasMoreRecords = true;
        }
        sb.Append("]}");
        return sb.ToString();
    }

    public static List GetRecordsFromDatabaseWithFilter(string search)
    {
        List Employees = new List();
        for (int i = 0; i  a.Company.ToLower().Contains(search.ToLower())).ToList();
    }

    #endregion Methods

I hope it was interesting and useful.

Please share any real time issue or feature you have faced or implemented.

Thank You 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s