All of us are beginners & all of us face the performance issue while fetching huge data from the database.
One of the solutions is that we can bring a small piece of data (how much data we require to show) and we can achieve with Jquery DataTable.
By default Jquery DataTable will bring all the data from Backend and Bind into the Table, but we don't want all the records at one go.
For this, we can go with Server Side Pagination with Jquery DataTable
- There are many articles on Server Side Pagination on the internet, But max of them used "context.Request.Form" for getting the DataTable Properties for eg: context.Request.Form["draw"], context.Request.Form["start"]. But most of the time it gets null and we struggle for getting the values
- In this article, we can achieve the Server side pagination with object with Post methods
Let's Start with the Database,
For this I'm using Northwind Sample Database, you can download this database from
https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
Once you added the database in the SQL you will find the Employees table with 10-15 Records, for this article I have added 576 records
Now here is the stored procedure for getting employees data
In this SP we are passing 4 parameters
CREATE procedure [dbo].[getEmployeeList]
(
@page INT = 0,
@size INT =10,
@sort nvarchar(50) ='EmployeeId asc',
@totalrow INT ='50'
)
AS
BEGIN
DECLARE @offset INT
DECLARE @newsize INT
DECLARE @sql NVARCHAR(MAX)
IF(@page=0)
BEGIN
SET @offset = @page
SET @newsize = @size
END
ELSE
BEGIN
SET @offset = @page+1
SET @newsize = @size-1
END
SET NOCOUNT ON
SET @sql = '
WITH OrderedSet AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sort + ') AS ''Index''
FROM [dbo].Employees
)
SELECT * FROM OrderedSet WHERE [Index] BETWEEN ' + CONVERT(NVARCHAR(12), @offset) + ' AND ' + CONVERT(NVARCHAR(12), (@offset + @newsize))
EXECUTE (@sql)
SET @totalrow = (SELECT COUNT(*) FROM Employees)
select @totalrow
END
In MVC c# I have added Pagination.cs class like below
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace ServerSidePagination.Models
{
public class Pagination
{
public DatatablePostData data { get; set; }
}
public class DatatablePostData
{
public int draw { get; set; }
public int start { get; set; }
public int length { get; set; }
public List<Column> columns { get; set; }
public Search search { get; set; }
public List<Order> order { get; set; }
}
public class Column
{
public string data { get; set; }
public string name { get; set; }
public string searchable { get; set; }
public string orderable { get; set; }
public Search search { get; set; }
}
public class Search
{
public string value { get; set; }
public string regex { get; set; }
}
public class Order
{
public int column { get; set; }
public string dir { get; set; }
}
public class DTResponse
{
public int recordsTotal { get; set; }
public int recordsFiltered { get; set; }
public string data { get; set; }
}
}
Controller with action method for View Page (Add View page for paginationExample method)
public ActionResult paginationExample()
{
return View();
}
Controller with action method like below for getting the Employees data
[HttpPost]
public JsonResult GetEmployeeData(Pagination pagination)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
DataSet ds = new DataSet();
DTResponse DTResponse = new DTResponse();
try
{
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Parameters.Clear();
cmd.CommandText = "getEmployeeList";
cmd.Parameters.AddWithValue("@sort",
pagination.data.columns[pagination.data.order[0].column].name == null ?
"EmployeeId asc" : pagination.data.columns[pagination.data.order[0].column].name+" "+
pagination.data.order[0].dir);
cmd.Parameters.AddWithValue("@size", pagination.data.length);
cmd.Parameters.AddWithValue("@page", pagination.data.start);
cmd.Parameters.AddWithValue("@totalrow", pagination.data.length);
// cmd.Parameters.AddWithValue("@P_Search", pagination.data.search.value);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlDataAdapter.SelectCommand = cmd;
sqlDataAdapter.Fill(ds);
}
DTResponse.recordsTotal = ds.Tables[0].Rows.Count;
DTResponse.recordsFiltered = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
DTResponse.data = JsonConvert.SerializeObject(ds.Tables[0]);
}
catch(Exception ex)
{
}
return Json(DTResponse, JsonRequestBehavior.AllowGet);
}
In View Side, add the Jquery Data Table References
<link href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css" rel="stylesheet">
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
cshtml Code
Here is the Ajax call for getting the Employees data with Server Side DataTable Properties
<h2>paginationExample</h2>
<script src="~/Scripts/jquery-3.4.1.js"></script>
<link href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css" rel="stylesheet">
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<div id="tblUpdatePcInfo">
</div>
<script>
var table;
$(document).ready(function () {
GetAllEmployyesData();
})
function GetAllEmployyesData() {
var tablecontent = '<table id="tblPCInfo" class="table table-bordered table-striped display nowrap" style="width:100%"><thead><tr>\
<th>EmployeeID</th>\
<th><input type="checkbox" id="chkSelectAll" class="filled-in chk-col-success" title="Select All"/></th>\
<th class="LastName">LastName</th>\
<th>FirstName</th>\
<th>Title</th>\
<th>TitleOfCourtesy</th>\
<th>Address</th>\
<th>City</th>\
<th>PostalCode</th>\
<th>Country</th>\
<th>HomePhone</th>\
</tr></thead><tbody></tbody></table>';
$("#tblUpdatePcInfo").html(tablecontent);
table = $('#tblPCInfo').dataTable({
clear: true,
destroy: true,
serverSide: true,
pageLength: 50,
lengthMenu: [[10, 25, 50, 100, 100000], [10, 25, 50, 100, "All"]],
autoFill: false,
"initComplete": function (settings, json) {
$(this.api().table().container()).find('input').attr('autocomplete', 'off');
},
"ajax": {
url: "/Home/GetEmployeeData",
type: "POST",
contentType: "application/json; charset=utf-8",
data: function (d) {
var data = { data: d };
return JSON.stringify(data);
},
AutoWidth: false,
"dataSrc": function (json) {
var data = json;
json.draw = data.draw;
json.recordsTotal = data.recordsTotal;
json.recordsFiltered = data.recordsFiltered;
json.data = JSON.parse(data.data);
return json.data;
}
},
"columns": [
{
"data": "EmployeeID", "width": "10px", "orderable": false, "name": "EmployeeID"
},
{
"data": "a", "width": "15px", "orderable": false, "name": "m.LicNo", "render": function (data, type, row, meta) {
return '<div style="text-align:center;"><input type="checkbox" class="SelectedChk" id="' + row.LicNo + '" value="' + row.LicNo + '" class="filled-in chk-col-success" title="Select All"/></div>';
},
"searchable": false
},
{
"data": "LastName", "name": "LastName", "searchable": false
},
{ "data": "FirstName", "name": "FirstName", "searchable": false },
{ "data": "Title", "name": "Title", "searchable": false },
{ "data": "TitleOfCourtesy", "name": "TitleOfCourtesy", "searchable": false },
{ "data": "Address", "name": "Address", "searchable": false },
{ "data": "City", "name": "City", "searchable": false },
{
"data": "PostalCode", "name": "PostalCode", "searchable": false
},
{ "data": "Country", "name": "Country", "searchable": false },
{ "data": "HomePhone", "name": "HomePhone", "searchable": false }
]
});
}
</script>
The Values of GetEmployeeData argument will be as follows
![Pagination in MVC with Jquery DataTable]()
OUTPUT
![Pagination in MVC with Jquery DataTable]()