Intro
JQuery DataTable definition taken from https://datatables.net/ that DataTables is a plug-in for the jQuery Javascript library.
It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.
Common example of basic usage on JQuery DataTable is not Server side processing. You can imagine it is a data collection method that retrieve all data represent as Json string and stored in the DataTable.
So paging, searching, ordering are fully client side processing. This is handy if the data is not large enough.
On the other hand, Server Side processing in JQuery DataTable take data partially based on user request.
Pagination queries data in between start & end records in that page.
Searching will search data on fields usualy with Sql Like also Ordering will do Sql Order by.
All data taken from database server dynamically.
However, this server side processing could be confusing for someone who just exploring JQuery DataTable.
So this post explains jQuery DataTable server side basic understanding with a simple example.
We will use Newtonsoft component to convert class object to Json string. You need to install it using Nuget package manager.
Example
First of all, we need to include CSS, jQuery and jQuery DataTable javascript in aspx file.
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
And then the html table as DataTable. This table has id example
<table id="example" class="display" cellspacing="0" width="100%"> <thead> <tr> <th>Full Name</th> <th>Phone Number</th> <th>Fax Number</th> <th>Email Address</th> </tr> </thead> </table>
Also append the javascript part to make example table as jquery DataTable
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"ajax": { url: "scripts/server_processing.ashx", type: "post" },
"columns": [
{ "data": "FullName" },
{ "data": "PhoneNumber" },
{ "data": "FaxNumber" },
{ "data": "EmailAddress" }
]
} );
}
);
</script>
Above DataTable component has processing option set to true and serverSide set to true. This means DataTable will work on server side.
The ajax option contains server side script path which pull data from database dynamically and returning Json string. type option is set to post.
columns option is set to array of column in returned Json string.
Putting them together in aspx file:
<%@ Page Language="C#" %>
<html>
<head>
<title>DataTable Example</title>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"ajax": { url: "scripts/server_processing.ashx", type: "post" },
"columns": [
{ "data": "FullName" },
{ "data": "PhoneNumber" },
{ "data": "FaxNumber" },
{ "data": "EmailAddress" }
]
} );
}
);
</script>
</head>
<body>
<form id="form1" runat="server">
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>Full Name</th>
<th>Phone Number</th>
<th>Fax Number</th>
<th>Email Address</th>
</tr>
</thead>
</table>
</form>
</body>
</html>
The example of returned Json string must have structure like below
{
"draw": 1,
"recordsTotal": 57,
"recordsFiltered": 57,
"data": [
[
"Angelica Ramos",
"(555) 123456",
"(555) 345678",
"angelica.ramos@email.com"
],
[
"Ashton Cox",
"(555) 123456",
"(555) 345678",
"ashton.cox@email.com"
],
...
]
}
Now let’s create the server side processing script. For simplicity, it just generic handler file, scripts/server_processing.ashx.
Create a blank ashx just to see what elements or parameters are sent by jquery DataTable.
Using Google Chrome inspect tool, go to network tab in order to see parameters sent from DataTable. This is what I have:
draw:1 columns[0][data]:FullName columns[0][name]: columns[0][searchable]:true columns[0][orderable]:true columns[0][search][value]: columns[0][search][regex]:false columns[1][data]:PhoneNumber columns[1][name]: columns[1][searchable]:true columns[1][orderable]:true columns[1][search][value]: columns[1][search][regex]:false columns[2][data]:FaxNumber columns[2][name]: columns[2][searchable]:true columns[2][orderable]:true columns[2][search][value]: columns[2][search][regex]:false columns[3][data]:EmailAddress columns[3][name]: columns[3][searchable]:true columns[3][orderable]:true columns[3][search][value]: columns[3][search][regex]:false order[0][column]:0 order[0][dir]:asc start:0 length:10 search[value]: search[regex]:false
Parameters that need to consider for basic server side processing are draw as user action count, columns[0][data],.. etc as column name, order[0][column] as sorting column, order[0][dir] as sorting direction,
start as pagination starting record, length as pagination record length, search[value] as a value user input in search box.
These elements sent by ajax and will be captured by our ashx file.
You can see complete details of all parameters sent by DataTable at https://datatables.net/manual/server-side.
This example uses Sql Server 2017 Express and WideWorldImporters sample database. It will retrieve People data.
First of all, please look how above DataTable sent parameters catch in ashx file by content.Request.Form
List<string> columns = new List<string>();
columns.Add("FullName");
columns.Add("PhoneNumber");
columns.Add("FaxNumber");
columns.Add("EmailAddress");
Int32 ajaxDraw = Convert.ToInt32(context.Request.Form["draw"]);
Int32 ajaxRequestStart = Convert.ToInt32(context.Request.Form["start"]);
ajaxRequestStart = ajaxRequestStart + 1;
Int32 ajaxRequestLength = Convert.ToInt32(context.Request.Form["length"]);
object searchby = context.Request.Form["search[value]"];
string sortColumn = context.Request.Form["order[0][column]"];
sortColumn = columns[Convert.ToInt32(sortColumn)];
string sortDirection = context.Request.Form["order[0][dir]"];
Int32 startRecordperPage = ajaxRequestStart;
Int32 finRecordperPage = startRecordperPage + ajaxRequestLength - 1;
And then People Class and DataTableAjaxClas which will be converted to Json string to generate DataTable
public class DataTableAjaxClass
{
public int draw;
public int recordsTotal;
public int recordsFiltered;
public List<People> data;
}
public class People
{
public string FullName;
public string PhoneNumber;
public string FaxNumber;
public string EmailAddress;
}
Please look again at example of returned Json string from server side script. DataTableAjaxClass converted to Json string and its data member is List of People which also converted to Json string already.
Retrieving data from Sql server
SqlConnection conn = new SqlConnection(@"Server=.\SQL2017Dev;Database=WideWorldImporters;User Id=aUser;Password=aPwd");
conn.Open();
string sql = "select temp.* from (select row_number() over(order by " + sortColumn + " " + sortDirection + ") as Rn, FullName, PhoneNumber, FaxNumber, EmailAddress from Application.People Where 1=1 ";
if (searchby != null)
{
sql = sql + "AND fullname like '" + searchby.ToString().Trim() + "%'";
}
sql = sql + ") as temp where Rn between " + startRecordperPage + " and " + finRecordperPage;
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
conn.Close();
List<People> peoples = new List<People>();
foreach (DataRow dr in dt.Rows)
{
People people = new People()
{
FullName = dr["FullName"].ToString(),
PhoneNumber = dr["PhoneNumber"].ToString(),
FaxNumber= dr["FaxNumber"].ToString(),
EmailAddress= dr["EmailAddress"].ToString()
};
peoples.Add(people);
}
Getting total record & RecordTotal function
Int32 recordTotal = RecordTotal(searchby);
Int32 recordFiltered = recordTotal;
...
...
public Int32 RecordTotal(object searchby)
{
SqlConnection conn = new SqlConnection(@"Server=.\SQL2017Dev;Database=WideWorldImporters;User Id=aUser;Password=aPwd");
conn.Open();
string sql = "select count(*) from Application.People Where 1=1 ";
if (searchby != null)
{
sql = sql + "AND fullname like '" + searchby.ToString().Trim() + "%'";
}
SqlCommand comm = new SqlCommand(sql, conn);
Int32 result = Convert.ToInt32( comm.ExecuteScalar());
comm.Dispose();
conn.Close();
return result;
}
recordTotal and recordFiltered variables are commonly set to a same value for server side processing.
Creating Json string for DataTable
DataTableAjaxClass dtAC = new DataTableAjaxClass()
{
draw = ajaxDraw,
recordsFiltered = recordTotal,
recordsTotal = recordTotal,
data = peoples
};
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(dtAC));
We use Newtonsoft component in order to convert class object to Json. Install the Newtonsoft using Nuget Package Manager.
Putting them all together
<%@ WebHandler Language="C#" Class="server_processing" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
public class server_processing : IHttpHandler {
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
List<string> columns = new List<string>();
columns.Add("FullName");
columns.Add("PhoneNumber");
columns.Add("FaxNumber");
columns.Add("EmailAddress");
Int32 ajaxDraw = Convert.ToInt32(context.Request.Form["draw"]);
Int32 ajaxRequestStart = Convert.ToInt32(context.Request.Form["start"]);
ajaxRequestStart = ajaxRequestStart + 1;
Int32 ajaxRequestLength = Convert.ToInt32(context.Request.Form["length"]);
object searchby = context.Request.Form["search[value]"];
string sortColumn = context.Request.Form["order[0][column]"];
sortColumn = columns[Convert.ToInt32(sortColumn)];
string sortDirection = context.Request.Form["order[0][dir]"];
Int32 startRecordperPage = ajaxRequestStart;
Int32 finRecordperPage = startRecordperPage + ajaxRequestLength - 1;
SqlConnection conn = new SqlConnection(@"Server=.\SQL2017Dev;Database=WideWorldImporters;User Id=aUser;Password=aPwd");
conn.Open();
string sql = "select temp.* from (select row_number() over(order by " + sortColumn + " " + sortDirection + ") as Rn, FullName, PhoneNumber, FaxNumber, EmailAddress from Application.People Where 1=1 ";
if (searchby != null)
{
sql = sql + "AND fullname like '" + searchby.ToString().Trim() + "%'";
}
sql = sql + ") as temp where Rn between " + startRecordperPage + " and " + finRecordperPage;
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
conn.Close();
List<People> peoples = new List<People>();
foreach (DataRow dr in dt.Rows)
{
People people = new People()
{
FullName = dr["FullName"].ToString(),
PhoneNumber = dr["PhoneNumber"].ToString(),
FaxNumber= dr["FaxNumber"].ToString(),
EmailAddress= dr["EmailAddress"].ToString()
};
peoples.Add(people);
}
Int32 recordTotal = RecordTotal(searchby);
Int32 recordFiltered = recordTotal;
DataTableAjaxClass dtAC = new DataTableAjaxClass()
{
draw = ajaxDraw,
recordsFiltered = recordTotal,
recordsTotal = recordTotal,
data = peoples
};
context.Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(dtAC));
}
public bool IsReusable {
get {
return false;
}
}
public Int32 RecordTotal(object searchby)
{
SqlConnection conn = new SqlConnection(@"Server=.\SQL2017Dev;Database=WideWorldImporters;User Id=aUser;Password=aPwd");
conn.Open();
string sql = "select count(*) from Application.People Where 1=1 ";
if (searchby != null)
{
sql = sql + "AND fullname like '" + searchby.ToString().Trim() + "%'";
}
SqlCommand comm = new SqlCommand(sql, conn);
Int32 result = Convert.ToInt32( comm.ExecuteScalar());
comm.Dispose();
conn.Close();
return result;
}
}
public class DataTableAjaxClass
{
public int draw;
public int recordsTotal;
public int recordsFiltered;
public List<People> data;
}
public class People
{
public string FullName;
public string PhoneNumber;
public string FaxNumber;
public string EmailAddress;
}
Finish. Just try to browse the aspx file and test it.
Regards,
Agung Gugiaji
Enlighten Application Developer Journals 