Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD generic handler return json

Suggested Videos
Part 107 - jQuery datatables individual column search
Part 108 - jQuery datatable show hide columns
Part 109 - jQuery datatables stored procedure for paging sorting and searching

This is continuation to Part 109. Please watch Part 109 from jQuery tutorial before proceeding.

In this video we will discuss implementing generic handler that calls a stored procedure and return JSON data. In our next video we will discuss, how to display the JSON data using jQuery datatables plugin.

Here are the steps to create the generic handler

Step 1 : Create new 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

                SqlParameter paramDisplayStart = new SqlParameter()
                    ParameterName = "@DisplayStart",
                    Value = displayStart

                SqlParameter paramSortCol = new SqlParameter()
                    ParameterName = "@SortCol",
                    Value = sortCol

                SqlParameter paramSortDir = new SqlParameter()
                    ParameterName = "@SortDir",
                    Value = sortDir

                SqlParameter paramSearchString = new SqlParameter()
                    ParameterName = "@Search",
                    Value = string.IsNullOrEmpty(search) ? null : search

                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();

            var result = new
                iTotalRecords = GetEmployeeTotalCount(),
                iTotalDisplayRecords = filteredCount,
                aaData = listEmployees

            JavaScriptSerializer js = new JavaScriptSerializer();

        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);
                totalEmployeeCount = (int)cmd.ExecuteScalar();
            return totalEmployeeCount;

        public bool IsReusable
                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.

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

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.

Testing the Generic Handler : When you navigate to the following URL, you should get Female employees sorted by Id column in ascending order

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"}]}

In our next video, we will discuss how to display the JSON data using jQuery datatables plugin.

jQuery tutorial for beginners

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.