asp.net generic handler return json

Here are the steps to create the asp.net generic handler

Step 1 : Create new asp.net web application project. Name it Demo.

Step 2 : Include a connection string in the web.config file to your database.
<add name="DBCS"
      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 3 : Add a class file to the project. Name it Employee.cs. Copy and paste the following code.

namespace Demo
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public string JobTitle { get; set; }
    }
}

Step 4 : Add a new Generic Handler. Name it EmployeeDataHandler.ashx. 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;
using System.Web.Script.Serialization;

namespace Demo
{
    public class EmployeeDataHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            int displayLength = int.Parse(context.Request["iDisplayLength"]);
            int displayStart = int.Parse(context.Request["iDisplayStart"]);
            int sortCol = int.Parse(context.Request["iSortCol_0"]);
            string sortDir = context.Request["sSortDir_0"];
            string search = context.Request["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;
        }

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

Notice that from the Request object we are retrieving the sorting, paging and search parameter values. These will be sent by the jQuery datatables plugin to the server.
iDisplayStart
iDisplayLength
iSortCol_0
sSortDir_0
sSearch

With the above data the server server should return a JSON object, with the following parameters.
iTotalRecords
iTotalDisplayRecords
aaData

If you are wondering how do I know these are the names of the parameters. I got them from the jQuery datatables plugin documention. Please check the link below.
http://legacy.datatables.net/usage/server-side

Testing the Generic Handler : When you navigate to the following URL, you should get Female employees sorted by Id column in ascending order
http://localhost:50625/EmployeeDataHandler.ashx?iDisplayLength=10&iDisplayStart=0&iSortCol_0=0&sSortDir_0=asc&sSearch=Female

Here is the response in JSON format
{"iTotalRecords":14,"iTotalDisplayRecords":4,"aaData":[{"Id":2,"FirstName":"Maria","LastName":"Nicholas","Gender":"Female","JobTitle":"Developer"},{"Id":4,"FirstName":"Mary","LastName":"Quant","Gender":"Female","JobTitle":"Sr. Developer"},{"Id":9,"FirstName":"Sara","LastName":"Solomon","Gender":"Female","JobTitle":"Sr. Developer"},{"Id":13,"FirstName":"Mary","LastName":"Ward","Gender":"Female","JobTitle":"Developer"}]}