jQuery datatables server-side processing using asp.net web services
Step 1 : Add a Web Service to your Demo project. Name it EmployeeService.asmx. 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.Script.Serialization;
using System.Web.Services;
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;
}
}
}
Step 1 : Add a WebForm to your Demo project.
Step 2 : Copy and paste the following HTML and jQuery code on the webform. Notice that we have set bServerSide option to true. This will tell the jQuery datatables plugin to use server-side processing. We also have set sAjaxSource to EmployeeService.asmx/GetEmployees. This option tells the jQuery datatables plugin about the external source from where the data needs to be loaded. Finally we have set sServerMethod option to post. This will tell the jQuery datatables plugin to issue a post request to the web service method.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-1.11.2.js"></script>
<link rel="stylesheet" type="text/css"
href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js">
</script>
<script type="text/javascript">
$(document).ready(function () {
$('#datatable').DataTable({
columns: [
{ 'data': 'Id' },
{ 'data': 'FirstName' },
{ 'data': 'LastName' },
{ 'data': 'Gender' },
{ 'data': 'JobTitle' }
],
bServerSide: true,
sAjaxSource: 'EmployeeService.asmx/GetEmployees',
sServerMethod: 'post'
});
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
<div style="width: 900px; border: 1px solid black; padding: 3px">
<table id="datatable">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Job Title</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Job Title</th>
</tr>
</tfoot>
</table>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
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;
}
}
}
Step 1 : Add a WebForm to your Demo project.
Step 2 : Copy and paste the following HTML and jQuery code on the webform. Notice that we have set bServerSide option to true. This will tell the jQuery datatables plugin to use server-side processing. We also have set sAjaxSource to EmployeeService.asmx/GetEmployees. This option tells the jQuery datatables plugin about the external source from where the data needs to be loaded. Finally we have set sServerMethod option to post. This will tell the jQuery datatables plugin to issue a post request to the web service method.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-1.11.2.js"></script>
<link rel="stylesheet" type="text/css"
href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js">
</script>
<script type="text/javascript">
$(document).ready(function () {
$('#datatable').DataTable({
columns: [
{ 'data': 'Id' },
{ 'data': 'FirstName' },
{ 'data': 'LastName' },
{ 'data': 'Gender' },
{ 'data': 'JobTitle' }
],
bServerSide: true,
sAjaxSource: 'EmployeeService.asmx/GetEmployees',
sServerMethod: 'post'
});
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
<div style="width: 900px; border: 1px solid black; padding: 3px">
<table id="datatable">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Job Title</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Job Title</th>
</tr>
</tfoot>
</table>
</div>
</form>
</body>
</html>
Comments
Post a Comment