jQuery datatables server-side processing using asp.net web services

Suggested Videos
Part 109 - jQuery datatables stored procedure for paging sorting and searching
Part 110 - asp.net generic handler return json
Part 111 - jQuery datatables server-side processing example asp.net



In this video we will discuss implementing server-side processing for jQuery datatables plugin using asp.net web services. This is continuation to Part 111. Please watch Part 111 from jQuery tutorial before proceeding.



Here is the request and response cycle
jQuery datatables plugin sends the request to the ASP.NET web service

The ASP.NET web service will then call the stored procedure in the database. Retrieves the data and converts it to JSON format.

The JSON formatted data will then be displayed on the page by the jQuery datatables plugin



Step 1 : Add a Web Service to your Demo project. Name it EmployeeService.asmx. Copy and paste the following code.

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Serialization;

using System.Web.Services;

namespace Demo

{

    [WebService(Namespace = "http://tempuri.org/")]

    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    [System.ComponentModel.ToolboxItem(false)]

    [System.Web.Script.Services.ScriptService]

    public class EmployeeService : System.Web.Services.WebService

    {

        [WebMethod]

        public void GetEmployees(int iDisplayLength, int iDisplayStart, int iSortCol_0,

            string sSortDir_0, string sSearch)

        {

            int displayLength = iDisplayLength;

            int displayStart = iDisplayStart;

            int sortCol = iSortCol_0;

            string sortDir = sSortDir_0;

            string search = sSearch;

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            List<Employee> listEmployees = new List<Employee>();

            int filteredCount = 0;

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlCommand cmd = new SqlCommand("spGetEmployees", con);

                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramDisplayLength = new SqlParameter()

                {

                    ParameterName = "@DisplayLength",

                    Value = displayLength

                };

                cmd.Parameters.Add(paramDisplayLength);

                SqlParameter paramDisplayStart = new SqlParameter()

                {

                    ParameterName = "@DisplayStart",

                    Value = displayStart

                };

                cmd.Parameters.Add(paramDisplayStart);

                SqlParameter paramSortCol = new SqlParameter()

                {

                    ParameterName = "@SortCol",

                    Value = sortCol

                };

                cmd.Parameters.Add(paramSortCol);

                SqlParameter paramSortDir = new SqlParameter()

                {

                    ParameterName = "@SortDir",

                    Value = sortDir

                };

                cmd.Parameters.Add(paramSortDir);

                SqlParameter paramSearchString = new SqlParameter()

                {

                    ParameterName = "@Search",

                    Value = string.IsNullOrEmpty(search) ? null : search

                };

                cmd.Parameters.Add(paramSearchString);

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Employee employee = new Employee();

                    employee.Id = Convert.ToInt32(rdr["Id"]);

                    filteredCount = Convert.ToInt32(rdr["TotalCount"]);

                    employee.FirstName = rdr["FirstName"].ToString();

                    employee.LastName = rdr["LastName"].ToString();

                    employee.Gender = rdr["Gender"].ToString();

                    employee.JobTitle = rdr["JobTitle"].ToString();

                    listEmployees.Add(employee);

                }

            }

            var result = new

            {

                iTotalRecords = GetEmployeeTotalCount(),

                iTotalDisplayRecords = filteredCount,

                aaData = listEmployees

            };

            JavaScriptSerializer js = new JavaScriptSerializer();

            Context.Response.Write(js.Serialize(result));

        }

        private int GetEmployeeTotalCount()

        {

            int totalEmployeeCount = 0;

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlCommand cmd = new

                    SqlCommand("select count(*) from tblEmployees", con);

                con.Open();

                totalEmployeeCount = (int)cmd.ExecuteScalar();

            }

            return totalEmployeeCount;

        }

    }

}

Step 1 : Add a WebForm to your Demo project. 

Step 2 : Copy and paste the following HTML and jQuery code on the webform. Notice that we have set bServerSide option to true. This will tell the jQuery datatables plugin to use server-side processing. We also have set sAjaxSource to EmployeeService.asmx/GetEmployees. This option tells the jQuery datatables plugin about the external source from where the data needs to be loaded. Finally we have set sServerMethod option to post. This will tell the jQuery datatables plugin to issue a post request to the web service method.

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script src="jquery-1.11.2.js"></script>

    <link rel="stylesheet" type="text/css"

        href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />

    <script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js">

    </script>

    <script type="text/javascript">

        $(document).ready(function () {

            $('#datatable').DataTable({

                columns: [

                    { 'data': 'Id' },

                    { 'data': 'FirstName' },

                    { 'data': 'LastName' },

                    { 'data': 'Gender' },

                    { 'data': 'JobTitle' }

                ],

                bServerSide: true,

                sAjaxSource: 'EmployeeService.asmx/GetEmployees',

                sServerMethod: 'post'

            });

        });

    </script>

</head>

<body style="font-family: Arial">

    <form id="form1" runat="server">

        <div style="width: 900px; border: 1px solid black; padding: 3px">

            <table id="datatable">

                <thead>

                    <tr>

                        <th>Id</th>

                        <th>First Name</th>

                        <th>Last Name</th>

                        <th>Gender</th>

                        <th>Job Title</th>

                    </tr>

                </thead>

                <tfoot>

                    <tr>

                        <th>Id</th>

                        <th>First Name</th>

                        <th>Last Name</th>

                        <th>Gender</th>

                        <th>Job Title</th>

                    </tr>

                </tfoot>

            </table>

        </div>

    </form>

</body>

</html>