Datatable ServerSide Processing With ASP.Net and SQL
- Serverside Processing for ASP.net c# with sql server — DataTables
- Using jQuery DataTables with Server-Side Processing with ASP.NET
- Datatables server side sql server | Microsoft ASP Solutions
- Basic Understanding of Using JQuery DataTable Server Side And Asp.Net
- Jquery Datatables server-side processing using asp.net webforms
- Server Side pagination using jQuery DataTable plugin in ASP.Net using C#
- Server Side AJAX based Paging using jQuery in ASP.Net Bootstrap Datatable
- Jquery Datatables with asp.net core server side processing without MVC
- Datatables with asp.net server side processing without MVC
Datatable ASPX Page Code Below
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataTable.aspx.cs" Inherits="DataTable" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Microsoft ASP Solutions</title>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.1/css/buttons.dataTables.min.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#example').DataTable({
'bProcessing': true,
'bServerSide': true,
'sAjaxSource': '/datatable.ashx',
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<br /><br />
<div class="container">
<div class="row">
<div class="col-md-12">
<table cellpadding="0" cellspacing="0" border="0" class="table table-bordered table-condensed compact display nowrap table-hover" id="example">
<thead>
<tr>
<th style="width: 5%;">RowId
</th>
<th>OrderNo
</th>
<th>ItemCode
</th>
<th>ProdCode
</th>
<th>EntDate
</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</form>
</body>
</html>
Datatable ASHX Page Code Below
public void ProcessRequest(HttpContext context)
{
// Those parameters are sent by the plugin
var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
var iSortCol = int.Parse(context.Request["iSortCol_0"]);
var iSortDir = context.Request["sSortDir_0"];
string search = context.Request["sSearch"];
///////////
//SEARCH (filter)
//- build the where clause
////////
StringBuilder sb = new StringBuilder();
string whereClause = string.Empty;
if (!String.IsNullOrEmpty(search))
{
sb.Append(" WHERE Order_No LIKE '%");
sb.Append(search);
sb.Append("%' OR RowId LIKE '%");
sb.Append(search);
sb.Append("%' OR ITMCode LIKE '%");
sb.Append(search);
sb.Append("%' OR ProdCode LIKE '%");
sb.Append(search);
sb.Append("%' OR EntDate LIKE '%");
sb.Append(search);
sb.Append("%'");
whereClause = sb.ToString();
}
///////////////
sb.Clear();
string orderByClause = string.Empty;
//Check which column is to be sorted by in which direction
for (int i = 0; i < 11; i++)
{
if (context.Request["bSortable_" + i] == "true")
{
sb.Append(context.Request["iSortCol_" + i]);
sb.Append(" ");
sb.Append(context.Request["sSortDir_" + i]);
}
}
orderByClause = sb.ToString();
//Replace the number corresponding the column position by the corresponding name of the column in the database
if (orderByClause.Trim() != "" && orderByClause.Trim() != null)
{
orderByClause = orderByClause.Replace("0", ", RowId");
orderByClause = orderByClause.Replace("1", ", Order_No");
orderByClause = orderByClause.Replace("2", ", ITMCode");
orderByClause = orderByClause.Replace("3", ", ProdCode");
orderByClause = orderByClause.Replace("4", ", EntDate");
//Eliminate the first comma of the variable "order"
orderByClause = orderByClause.Remove(0, 1);
}
else
{
orderByClause = "Order_No ASC";
}
if(orderByClause != " RowId asc " && orderByClause != " RowId desc ")
{
orderByClause = "ORDER BY " + orderByClause;
}
else
{
orderByClause = "ORDER BY Order_No ASC";
}
/////////////
//T-SQL query
//- ROW_NUMBER() is used for db side pagination
/////////////
sb.Clear();
string query = "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS RowNumber,* FROM ( SELECT ( SELECT COUNT(*) FROM Cb_OrderTrnSub {1} ) AS TotalDisplayRows, (SELECT COUNT(*) FROM Cb_OrderTrnSub) AS TotalRows,Order_No,ITMCode,ProdCode,EntDate,RowId FROM Cb_OrderTrnSub {1} ) RawResults ) Results WHERE RowNumber BETWEEN {2} AND {3}";
query = String.Format(query, orderByClause, whereClause, iDisplayStart + 1, iDisplayStart + iDisplayLength);
//Get result rows from DB
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd;
con.Open();
cmd = new SqlCommand(query, con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);
//int data = Convert.ToInt32(cmd.ExecuteScalar());
//SqlDataReader rdrBrowsers = cmd.ExecuteReader();
sb.Clear();
string outputJson = string.Empty;
int totalDisplayRecords = 0;
int totalRecords = 0;
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
if (totalRecords == 0)
totalRecords = Int32.Parse(dt.Rows[i]["TotalRows"].ToString());
if (totalDisplayRecords == 0)
totalDisplayRecords = Int32.Parse(dt.Rows[i]["TotalDisplayRows"].ToString());
if (i != dt.Rows.Count)
{
sb.Append("[");
//sb.Append("\" \",");
sb.Append("\"" + dt.Rows[i]["RowId"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["Order_No"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["ITMCode"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["ProdCode"].ToString() + "\",");
sb.Append("\"" + Convert.ToDateTime(dt.Rows[i]["EntDate"]).ToString("yyyy-MM-dd") + "\",");
sb.Append("\" \"");
sb.Append("],");
}
else
{
sb.Append("[");
//sb.Append("\" \",");
sb.Append("\"" + dt.Rows[i]["RowId"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["Order_No"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["ITMCode"].ToString() + "\",");
sb.Append("\"" + dt.Rows[i]["ProdCode"].ToString() + "\",");
sb.Append("\"" + Convert.ToDateTime(dt.Rows[i]["EntDate"]).ToString("yyyy-MM-dd") + "\",");
sb.Append("\" \"");
sb.Append("]");
}
}
outputJson = sb.ToString();
outputJson = outputJson.Remove(outputJson.Length - 1);
sb.Clear();
//sb.Append("[");
sb.Append("{");
sb.Append("\"iTotalRecords\": ");
sb.Append(totalRecords);
sb.Append(",");
sb.Append("\"iTotalDisplayRecords\": ");
sb.Append(totalDisplayRecords);
sb.Append(",");
sb.Append("\"aaData\": [");
//sb.Append("[");
sb.Append(outputJson);
//sb.Append("]");
sb.Append("]");
sb.Append("}");
outputJson = sb.ToString();
context.Response.ContentType = "application/json";
context.Response.Write(outputJson);
}
Datatable JSON Format To Show Data
{
"iTotalRecords": 57,
"iTotalDisplayRecords": 57,
"aaData": [
[
"Airi",
"Satou",
"Accountant",
"Tokyo",
"28th Nov 08",
"$162,700"
],
[
"Angelica",
"Ramos",
"Chief Executive Officer (CEO)",
"London",
"9th Oct 09",
"$1,200,000"
],
[
"Ashton",
"Cox",
"Junior Technical Author",
"San Francisco",
"12th Jan 09",
"$86,000"
],
[
"Bradley",
"Greer",
"Software Engineer",
"London",
"13th Oct 12",
"$132,000"
],
[
"Brenden",
"Wagner",
"Software Engineer",
"San Francisco",
"7th Jun 11",
"$206,850"
],
[
"Brielle",
"Williamson",
"Integration Specialist",
"New York",
"2nd Dec 12",
"$372,000"
],
[
"Bruno",
"Nash",
"Software Engineer",
"London",
"3rd May 11",
"$163,500"
],
[
"Caesar",
"Vance",
"Pre-Sales Support",
"New York",
"12th Dec 11",
"$106,450"
],
[
"Cara",
"Stevens",
"Sales Assistant",
"New York",
"6th Dec 11",
"$145,600"
],
[
"Cedric",
"Kelly",
"Senior Javascript Developer",
"Edinburgh",
"29th Mar 12",
"$433,060"
]
]
}
Thanks for the solution. I was eagerly searching this for my project.
ReplyDeletemost welcome friend please share to all developers
Delete