Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
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.
Generic Handler Code
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”];
int filteredRows = 0;
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
List[Employee] employees = new List[Employee]();
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())
{
filteredRows = Convert.ToInt32(rdr[“TotalCount”]);
Employee employee = new Employee();
employee.Id = Convert.ToInt32(rdr[“Id”]);
employee.FirstName = rdr[“FirstName”].ToString();
employee.LastName = rdr[“LastName”].ToString();
employee.Gender = rdr[“Gender”].ToString();
employee.JobTitle = rdr[“JobTitle”].ToString();
employees.Add(employee);
}
}
var result = new
{
iTotalRecords = GetEmployeeTotalCount(),
iTotalDisplayRecords = filteredRows,
aaData = employees
};
JavaScriptSerializer js = new JavaScriptSerializer();
context.Response.Write(js.Serialize(result));
}
private int GetEmployeeTotalCount()
{
int totalEmployees = 0;
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“Select count(*) from tblEmployees”, con);
con.Open();
totalEmployees = (int)cmd.ExecuteScalar();
}
return totalEmployees;
}
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.
In our next video, we will discuss how to display the JSON data using jQuery datatables plugin.
