Sql server, .net and c# video tutorial: asp.net 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 asp.net 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 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.

<addname="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

{

publicclassEmployee

{

publicint Id { get; set; }

publicstring FirstName { get; set; }

publicstring LastName { get; set; }

publicstring Gender { get; set; }

publicstring 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

{

publicclassEmployeeDataHandler : IHttpHandler

{

publicvoid 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 = newList<Employee>();

int filteredCount = 0;

using (SqlConnection con = newSqlConnection(cs))

{

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

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramDisplayLength = newSqlParameter()

{

ParameterName = "@DisplayLength",

Value = displayLength

};

cmd.Parameters.Add(paramDisplayLength);

SqlParameter paramDisplayStart = newSqlParameter()

{

ParameterName = "@DisplayStart",

Value = displayStart

};

cmd.Parameters.Add(paramDisplayStart);

SqlParameter paramSortCol = newSqlParameter()

{

ParameterName = "@SortCol",

Value = sortCol

};

cmd.Parameters.Add(paramSortCol);

SqlParameter paramSortDir = newSqlParameter()

{

ParameterName = "@SortDir",

Value = sortDir

};

cmd.Parameters.Add(paramSortDir);

SqlParameter paramSearchString = newSqlParameter()

{

ParameterName = "@Search",

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

};

cmd.Parameters.Add(paramSearchString);

con.Open();

SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())

{

Employee employee = newEmployee();

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

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

}

privateint GetEmployeeTotalCount()

{

int totalEmployeeCount = 0;

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

using (SqlConnection con = newSqlConnection(cs))

{

SqlCommand cmd = new

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

con.Open();

totalEmployeeCount = (int)cmd.ExecuteScalar();

}

return totalEmployeeCount;

}

publicbool IsReusable

{

get

{

returnfalse;

}

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

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

jQuery tutorial for beginners

Sql server, .net and c# video tutorial

asp.net generic handler return json

No comments:

Post a Comment