jQuery Tutorial for Beginners

jQuery datatables server side processing using asp net web services

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2015/08/jquery-datatables-server-side_8.html

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

Web Service Code

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;
}
}
}