Featured
ASP.NET Webform - Datatables JQuery plugin Server Side Integration
Last modified: March 21, 2022Step 1
Create an ASP.NET WebFrom application.
Step 2
Install EntityFramework NuGet Package
Step 3
Create Database
Login to Sql Server Management Studio.
create database jqueryDataTable;
Refresh
Run the below SQL script
CREATE TABLE [dbo].[Stock](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Qty] [int] NOT NULL,
CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Stock(Name, Qty)
VALUES
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100),
('Apple Iphone12', 100)
Step 4
Entity Data Model
Create Entity Data Model that connect to SQL Server database
Select your SQL Database
Step 5
RouteConfig
In App_Start folder, you will see "RouteConfig.cs" file, open it and replace it with following code,
public static class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
var settings = new FriendlyUrlSettings();
settings.AutoRedirectMode = RedirectMode.Off; // RedirectMode.Permanent
routes.EnableFriendlyUrls(settings);
}
}
Step 6
We are going to use CDN links to our project.
<link href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
In the Default.aspx page, delete the content and replace with below.
<table id="stockDatatable" class="table table-striped" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Qty</th>
</tr>
</thead>
</table>
To use html table as DataTable Grid, we need to do $("#stockDatatable").DataTable({.
<link href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#stockDatatable").DataTable({
"processing": true, //show processing message
"serverSide": true, //is server side
"filter": true, //allow filter
"ajax": {
"url": "Default.aspx/GetData", //endpoint to get data
"contentType": "application/json",
"type": "GET",
"dataType": "JSON",
"data": function (d) {
return d;
},
"dataSrc": function (json) {
json.draw = json.d.draw;
json.recordsTotal = json.d.recordsTotal;
json.recordsFiltered = json.d.recordsFiltered;
json.data = json.d.data;
var return_data = json.d.data;
return return_data;
}
},
"columns": [ // columns to populate
{ "data": "Name", "name": "Name", "autoWidth": true },
{ "data": "Qty", "name": "Qty", "autoWidth": true },
]
});
});
</script>
Step 6
We are going to create a simple class file that keep the information of database passed from class file to html
public class DataTables
{
public int draw { get; set; }
public int recordsTotal { get; set; }
public int recordsFiltered { get; set; }
public List<Stock> data { get; set; }
}
Step 7
Create a method as below to get the data for AJAX call.
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
public static object GetData()
{
DataTables result = new DataTables();
using(var db = new jqueryDataTableEntities())
{
string search = HttpContext.Current.Request.Params["search[value]"];
string draw = HttpContext.Current.Request.Params["draw"];
string order = HttpContext.Current.Request.Params["order[0][column]"];
string sortColumnDirection = HttpContext.Current.Request.Params["order[0][dir]"];
int startRec = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);
int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);
string start = HttpContext.Current.Request.Params["start"];
int skip = start != null ? Convert.ToInt32(start) : 0;
var data = db.Stock.ToList();
int totalRecords = data.Count;
int recFilter = data.Count;
result.draw = Convert.ToInt32(draw);
result.recordsTotal = totalRecords;
result.recordsFiltered = recFilter;
result.data = data.Skip(skip).Take(pageSize).ToList();
}
return result;
}
Step 7
Outcome
