How can I implement jQuery DataTables plugin using C#, ASP.NET, SQL Server side processing?
This version is for older SqlServer, For newer version ,try the other one.
This technique uses stored procedure, you can improve the performance by using other methods than #temp Main features are
- Stored Procedure
- Injection Free and easily adaptable sql structure
- Ajax
Functional and Very Useful
Step 1:(HTML)
<link href="../Content/css/datatables.min.css" rel="stylesheet" />
<script src="../Scripts/datatables.min.js"></script>
<script src="../Scripts/jQuery-2.1.4.min.js"></script>
<script>
$(document).ready(function () {
if ($.fn.dataTable.isDataTable('#tbl_category')) {
t.destroy();
}
t = $("#tbl_category").DataTable({
processing: true,
serverSide: true,
info: true,
ajax: {
url: '../Ajax/Category?option=GetAllAdminCategory&user_srno='+user_srno,
data: function (data) {
delete data.columns;
}
},
columns: [
{ "data": "abc" },
{ "data": "name" },
{ "data": "baseDiscount" },
{ "data": "additionalDiscount" },
{ "data": "specialDiscount" },
{
"render": function (data, type, full, meta) {
return '<a class="btn btn-warning" onClick="editdata(' + full.srno + ',\'' + full.name + '\',\'' + full.baseDiscount + '\',\'' + full.additionalDiscount + '\',\'' + full.specialDiscount + '\',\'' + full.specialDiscount + '\')" href="javascript://">Edit</a> <a class="btn btn-danger" onClick="deletePhantom(' + full.srno + ',\'DELETE\')" href="javascript://">Remove</a>';
}
}
],
order: [[0, 'desc']],
select: true,
dom: 'lfrtip',
responsive: true,
buttons: true
});
t.on('order.dt search.dt', function () {
t.column(0, { search: 'applied', order: 'applied' }).nodes().each(function (cell, i) {
cell.innerHTML = i + 1;
});
}).draw();
});
</script>
<table id="tbl_category" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Base Discount</th>
<th>Additional Discount</th>
<th>Special Discount</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<th>#</th>
<th>Name</th>
<th>Base Discount</th>
<th>Additional Discount</th>
<th>Special Discount</th>
<th>Action</th>
</tr>
</tfoot>
</table>
STEP :2 (Stored Procedure)
Create procedure [dbo].[category_post]
@srno int=null, -- from here
@user_srno int=null,
@catSrno int=null,
@name varchar(200)=null,
@baseDiscount numeric(18,2)=null,
@additionalDiscount numeric(18,2)=null,
@specialDiscount numeric(18,2)=null,
@status int null,
@Action_by int null,
@option varchar(20) = null, -- to here personnel parameters
@orderColumn int =null,
@orderDir varchar(20)=null,
@start int =null,
@limit int =null,
@searchKey varchar(20) -- personnel parameter
as
BEGIN
select IDENTITY(int,1,1) as SnoID, null as abc,specialDiscount, additionalDiscount, baseDiscount, name,cast(srno as varchar(20)) as srno
--this method is userful for all sql server version (it can be made better by using fetch)
into #tempCategory
from categoryStd where [status] not in(4,14) and categoryStd.name like '%'+@searchKey+'%'
declare @to as int = @start+@limit
select * from #tempCategory where SnoID>@start and SnoID<=@to
order by
CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN #tempCategory.[name] END DESC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN #tempCategory.[name] END ASC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN #tempCategory.[name] END DESC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN #tempCategory.[name] END ASC
select count(*) from #tempCategory
END
STEP:3 (AJAX Page) C# form
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using AppBlock;
using System.Data.SqlClient;
using Newtonsoft.Json;
namespace alfest.Ajax
{
public partial class Category : System.Web.UI.Page
{
string mode, option, user, limit, start, searchKey, orderByColumn, orderByDir, estMstSrno, pnlsrno, draw, jsonString;
CommonClass cmnCls = new CommonClass();
protected void Page_Load(object sender, EventArgs e)
{
mode = Request.QueryString["mode"] == null ? "" : Request.QueryString["mode"].ToString();
option = Request.QueryString["option"] == null ? "" : Request.QueryString["option"].ToString();
limit = Request.QueryString["length"] == null ? "" : Request.QueryString["length"].ToString();
start = Request.QueryString["start"] == null ? "" : Request.QueryString["start"].ToString();
user = Request.QueryString["user"] == null ? "" : Request.QueryString["user"].ToString();
searchKey = Request.QueryString["search[value]"] == null ? "" : Request.QueryString["search[value]"].ToString();
orderByColumn = Request.QueryString["order[0][column]"] == null ? "" : Request.QueryString["order[0][column]"].ToString();
orderByDir = Request.QueryString["order[0][dir]"] == null ? "" : Request.QueryString["order[0][dir]"].ToString();
estMstSrno = Request.QueryString["estMstSrno"] == null ? "" : Request.QueryString["estMstSrno"].ToString();
pnlsrno = Request.QueryString["pnlsrno"] == null ? "" : Request.QueryString["pnlsrno"].ToString();
draw = Request.QueryString["draw"] == null ? "" : Request.QueryString["draw"].ToString();
// Cls_Category CatgObj = new Cls_Category();
// CatgObj.orderColumn = Convert.ToInt32(orderByColumn);
// CatgObj.limit = Convert.ToInt32(limit);
// CatgObj.orderDir = orderByDir;
// CatgObj.start = Convert.ToInt32(start);
// CatgObj.searchKey = searchKey;
// CatgObj.option = "GetAllAdminCategory";
// or user your own method to get data (just fill the dataset)
// DataSet ds = cmnCls.PRC_category(CatgObj);
dynamic newtonresult = new
{
status = "success",
draw = Convert.ToInt32(draw == "" ? "0" : draw),
recordsTotal = ds.Tables[1].Rows[0][0],
recordsFiltered = ds.Tables[1].Rows[0][0],
data = ds.Tables[0]
};
jsonString = JsonConvert.SerializeObject(newtonresult);
Response.Clear();
Response.ContentType = "application/json";
Response.Write(jsonString);
}
}
}
FINAL RESULT :
A working example of JQuery DataTables in C#, ASP.NET, SQL Server side processing with ajax and webservices.
http://jquerydatatablessamp.codeplex.com/
https://github.com/benni12/jquerydatatablesExample
(posted it twice to make it easier for someone looking to find it)
Start off by linking to DataTables.Net, Allan has done an amazing job with this plugin.
I've been using this plugin for about one year. When I first attempted to implement it in C# and .NET with server side processing, ajax and webservices, there was little documentation and no working examples so I decided to put one together.
The source code provided is of a Visual Studio 2012 (tested in Ultimate 2012 and Pro 2012) website project. I threw this together as a simple working example in hopes of helping someone else out there trying to put something like this together. It's something I wish I had a year ago.
The database mdf is included in the App_Data folder. I am currently running an instance of MS SQL Server Express 2012.
Required install SQL Server Express 2012 or other standard or better full version installs of MS SQL Server. Tested on Standard 2008 R2 and 2012 Express.
Implementation in MVC, Entity Framework , Stored Procedure with Latest fetch offset pagination
Step 1 - HTML
<table id="tbl_category" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Base Discount</th>
<th>Additional Discount</th>
<th>Special Discount</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<th>#</th>
<th>Name</th>
<th>Base Discount</th>
<th>Additional Discount</th>
<th>Special Discount</th>
<th>Action</th>
</tr>
</tfoot>
</table>
@*<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs/dt-1.10.13/r-2.1.1/sc-1.4.2/datatables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/v/bs/dt-1.10.13/r-2.1.1/sc-1.4.2/datatables.min.js"></script>*@
<link href="~/Scripts/JqueryDatatable/Datatable.css" rel="stylesheet" />
<script src="~/Scripts/JqueryDatatable/DataTable.js"></script>
<script>
$(document).ready(function() {
if ($.fn.dataTable.isDataTable('#tbl_category')) {
t.destroy();
}
t = $("#tbl_category").DataTable({
processing: true,
serverSide: true,
info: true,
ajax: {
url: '../Client/SearchMis',
data: function (data) {
delete data.columns;
}
},
scrollY: 300,
deferRender: true,
scroller: true,
columns: [
{ "data": "abc" },
{ "data": "name" },
{ "data": "Address" },
{ "data": "name" },
{ "data": "name" },
{
"render": function (data, type, full, meta) {
return '<a class="btn btn-warning" onClick="editdata(' + full.name + ',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\',\'' + full.name + '\')" href="javascript://">Edit</a> <a class="btn btn-danger" onClick="deletePhantom(' + full.name + ',\'DELETE\')" href="javascript://">Remove</a>';
}
}
],
order: [[0, 'desc']],
select: true,
dom: 'lfrtip',
responsive: true,
buttons: true
});
t.on('order.dt search.dt', function () {
t.column(0, { search: 'applied', order: 'applied' }).nodes().each(function (cell, i) {
cell.innerHTML = i + 1;
});
}).draw();
});
</script>
Step 2 - C#
using EmployeeTrackingSystemAndMIS.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Mvc;
namespace EmployeeTrackingSystemAndMIS.Controllers
{
public class ClientController : Controller
{
private EmployeeTrackingSystemAndMISEntities db = new EmployeeTrackingSystemAndMISEntities();
public string SearchMis()
{
string limit, start, searchKey, orderColumn, orderDir, draw, jsonString;
limit = Request.QueryString["length"] == null ? "" : Request.QueryString["length"].ToString();
start = Request.QueryString["start"] == null ? "" : Request.QueryString["start"].ToString();
searchKey = Request.QueryString["search[value]"] == null ? "" : Request.QueryString["search[value]"].ToString();
orderColumn = Request.QueryString["order[0][column]"] == null ? "" : Request.QueryString["order[0][column]"].ToString();
orderDir = Request.QueryString["order[0][dir]"] == null ? "" : Request.QueryString["order[0][dir]"].ToString();
draw = Request.QueryString["draw"] == null ? "" : Request.QueryString["draw"].ToString();
var parameter = new List<object>();
var param = new SqlParameter("@orderColumn", orderColumn);
parameter.Add(param);
param = new SqlParameter("@limit", limit);
parameter.Add(param);
param = new SqlParameter("@orderDir", orderDir);
parameter.Add(param);
param = new SqlParameter("@start", start);
parameter.Add(param);
param = new SqlParameter("@searchKey", searchKey);
parameter.Add(param);
var CompanySearchList = db.Database.SqlQuery<CompanySearch>("EXEC SearchCompany @orderColumn,@limit,@orderDir,@start,@searchKey ", parameter.ToArray()).ToList();
dynamic newtonresult = new
{
status = "success",
draw = Convert.ToInt32(draw == "" ? "0" : draw),
recordsTotal = CompanySearchList.FirstOrDefault().TotalCount,
recordsFiltered = CompanySearchList.FirstOrDefault().TotalCount,
data = CompanySearchList
};
jsonString = JsonConvert.SerializeObject(newtonresult);
return jsonString;
}
private class CompanySearch
{
public int TotalCount { get; set; }
public string abc { get; set; }
public string Address { get; set; }
public int? ClientID { get; set; }
public int? EmployeeID { get; set; }
public string name { get; set; }
public int CompanyID { get; set; }
}
}
}
Step 3 - Stored Procedure
USE [EmployeeTrackingSystemAndMIS]
GO
/****** Object: StoredProcedure [dbo].[category_post] Script Date: 22-02-2017 10:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].SearchCompany
@orderColumn int ,
@limit int,
@orderDir varchar(20),
@start int,
@searchKey varchar(20)
as
BEGIN
declare @to as int = @start+@limit
select TotalCount = COUNT(c.CompanyID) OVER(), null as abc,c.Address,c.ClientID ,c.EmployeeID , name,
c.CompanyID
from CompanyTbl c where c.Name like '%'+@searchKey+'%'
order by
CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN c.[name] END DESC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN c.[name] END ASC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN c.[name] END DESC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN c.[name] END ASC
OFFSET @start ROWS
FETCH NEXT @to ROWS ONLY
End