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

<link href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js" type="text/javascript"></script>

Default.aspx

<form id="form1" runat="server">
        <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>
    </form>
<script type="text/javascript">
        $(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[0] + '\">' + 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 < aoData.length; i++) {
                if (aoData[i].name == Key) {
                    return aoData[i].value;
                }
            }
        }
    </script>

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<Employee> GetRecordsFromDatabaseWithFilter(string search)
    {
        List<Employee> Employees = new List<Employee>();
        for (int i = 0; i <= 5; i++)
        {
            Employees.Add(new Employee { Email = "psatikunvar7@gmail.com", FirstName = "Pratik", LastName = "Satikunvar", Company = "Apple" + i });
            Employees.Add(new Employee { Email = "psatikunvar7@gmail.com", FirstName = "Pratik", LastName = "Satikunvar", Company = "Microsoft" + i });
            Employees.Add(new Employee { Email = "psatikunvar7@gmail.com", FirstName = "Pratik", LastName = "Satikunvar", Company = "Google" + i });
            Employees.Add(new Employee { Email = "psatikunvar7@gmail.com", FirstName = "Pratik", LastName = "Satikunvar", Company = "Amazon" + i });
        }
        return Employees.Where(a => 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s