The question is, why do we need SQL Pagination when we already have built-in paging functionality in Grid View or JQuery Data table.
Answer is èSometimes it's not a good habit to load all the data over the network, but instead to transfer only the data required, to improve performance. For that we have SQL paging. Pass only a Page Index and Page Size to load the required data.
To do this, I created the following Stored Procedure:
=============================================
-- Author: Nilachal Sethi
-- Create Date: Oct 31, 2022
-- Description: Get full List By Pagination
-- =============================================
CREATE PROCEDURE [dbo].[ListData]
@PageSize as int=1000,
@Caret as int=0,
@Option varchar(100) = Null
AS
BEGIN
Declare @TotalRecord int
SELECT @TotalRecord =COUNT(ID) FROM JobOrder
OFFSET @PageSize * (@Caret)ROWS FETCH NEXT @PageSize ROWS ONLY
SELECT @TotalRecord AS TotalRecord,* FROM JobOrder
ORDER BY DateTaken DESC
OFFSET @PageSize * (@Caret)ROWS FETCH NEXT @PageSize ROWS ONLY
END;
Now the Controller is:
[HttpGet]
public IActionResult GetJobOrderGrid(int? pagesize, int? caret)
{
string responseString = "";
var response = webApiMethodsForDBAction.GetAsyncCall(webApiUrl +"URL?Pagesize=" + pagesize + "&Caret=" + caret);
int StatusCode = Convert.ToInt32(response.StatusCode);
if (StatusCode == 200)
{
responseString = response.Content.ReadAsStringAsync().Result;
}
return Json(responseString);
}
Now the View is:
<div id="JobGrid">
<input type="hidden" id="hidcurrentindexemp" value="0" />
<input type="hidden" id="hidemppagesize" value="10" />
<input type="hidden" id="hiddenTotalRecord" value="" />
<div class="row">
<div class="col-md-12 m-grid__item">
<div id="JobDiv" class="">
</div>
<div class="clearfix"></div>
<div class="pagination-content clearfix" id="qemp"></div>
</div>
</div>
</div>
<script src="@Url.Content(" ~/MainCustomer/js/FullCustomer.js")"></script>
<script type="text/javascript">
$(document).ready(function () {
GetJobOrderList();
});
</script>
Now the JQuery is:
For making Table And Pagination
function GetJobOrderList() {
$.ajax({
url: '/Area/Controller/ActionName',
type: "GET",
data: { pagesize: $('#hidemppagesize').val(), caret: $('#hidcurrentindexemp').val() },
dataType: "json",
async: true,
beforeSend: function () {
$("#divLoading").show();
},
success: function (data) {
var Job = JSON.parse(data);
if (Job === '') {
window.location.reload();
}
var JobList = Job.thisList;
var TotalRecord = Job.thisList[0].TotalRecords;
var job_id = Job.thisList[0].Id;
$('#hiddenTotalRecord').val(TotalRecord);
var grid = '';
grid += '<div class="main-container table-responsive table-container"><table class="table table-striped table-hover table-bordered" id="JobOrderTable"><thead> <tr><th>Action</th><th>Job Title</th><th >Customer Name </th><th>Stages </th><th>Start Date</th><th>Location</th><th>End Client</th><th>#Position</th><th>Status </th></tr> </thead>';
grid += '<tbody>';
if (data === null) {
grid += '<tr><td colspan="9" >No Records Found.<td></tr>';
}
else {
$.each(JobList, function (d, value) {
grid += ' <tr><td>' + (value.JobTitle === "" ? "N/A" : value.JobTitle) + '</td><td>' + (value.CustomerName === "" ? "N/A" : value.CustomerName) + '</td><td>' + (value.JoborderStage === "" ? "N/A" : value.JoborderStage) + '</td><td>' + (value.FormattedStartDate === "" ? "N/A" : value.FormattedStartDate) + '</td><td>' + (value.Location === null ? "N/A" : value.Location) + '</td><td>' + (value.Positions === "" ? "N/A" : value.Positions) + '</td><td>' + (value.IsActive === "Active" ? "" : "") + '</td></tr>';
});
}
grid += '</tbody></table>';
Pagination($('#hiddenTotalRecord').val(), $("#hidcurrentindexemp").val(), $("#hidemppagesize").val(), "#qemp", "qemp");
$("#JobDiv").html(grid);
$("#divLoading").hide();
},
error: function (xhr, textStatus) {
if (xhr.status === 401) { window.location.reload(); }
else {
$.notify('please try again!', "error");
$("#divLoading").hide();
}
}
});
}
For Click Page Function
function Search_qemp(index) {
$("#hidcurrentindexemp").val(index);
GetJobOrderList();
}
Main Pagination JS:
function Pagination(totalrecord, currentindex, pagesize, div, Type) {
currentindex = parseInt(currentindex);
totalrecord = parseInt(totalrecord);
pagesize = parseInt(pagesize);
var pagestring = '';
$(div).html("");
var pagerlink = Math.ceil(totalrecord / pagesize);
var lastindex = pagerlink - 1;
if (totalrecord === 0) {
$(div).append('<p>Displaying 0 out of 0 items </p>');
}
else if (totalrecord > 0) {
if (currentindex === lastindex) {
if (currentindex === 0) {
$(div).append('<p>Displaying ' + 1 + ' to ' + totalrecord + ' out of ' + totalrecord + ' items </p>');
}
else {
$(div).append('<p>Displaying ' + parseInt(1 + (pagesize * (currentindex - 1) + parseInt(pagesize))) + ' to ' + totalrecord + ' out of ' + totalrecord + ' items </p>')
}
}
else {
$(div).append('<p>Displaying ' + parseInt(pagesize * currentindex + 1) + ' to ' + parseInt(pagesize * currentindex + parseInt(pagesize)) + ' out of ' + totalrecord + ' items </p>')
}
$(div).append('');
if (totalrecord === 0) {
pagestring = pagestring + '<li class="paginate_button page-item"><a class="page-link number">First</a></li><li class="paginate_button page-item"><a class="page-link number">Previous</a></li>' +
'<li class="paginate_button page-item"> No Record Found </li><li class="paginate_button page-item"><a class="page-link number">Next</a></li><li class="paginate_button page-item"><a class="page-link number">Last</a></li>';
}
else {
if (currentindex === 0) {
pagestring = pagestring + '<li class="paginate_button page-item"><a class="page-link number">First</a></li>' +
'<li class="paginate_button page-item"><a class="page-link number">Previous</a></li>';
}
else {
pagestring = pagestring + '<li class="paginate_button page-item"><a class="page-link number" onclick="Search_' + Type + '(0);">First</a></li>' +
'<li class="paginate_button page-item"><a class="page-link number" onclick="Search_' + Type + '(' + parseInt(currentindex - 1) + ');">Previous</a></li>';
}
var counter = 0;
var intial = 0;
if (parseInt(currentindex) < 5) {
intial = 0;
}
else {
intial = parseInt(currentindex) - 3;
}
for (var i = intial; i < pagerlink; i++) {
var j = i + 1;
if (i === currentindex) {
pagestring = pagestring + '<li class="paginate_button page-item number active"> <a class="page-link number" value="' + j + '">' + j + '</a></li>';
}
else {
pagestring = pagestring + '<li class="paginate_button page-item"> <a class="page-link number" onclick="Search_' + Type + '(' + i + ');" value="' + j + '">' + j + '</a> </li>';
}
if (counter === 5)
break;
counter++;
}
if (currentindex === lastindex) {
pagestring = pagestring + '<li class="paginate_button page-item"><a class="page-link number">Next</a></li>' +
'<li class="paginate_button page-item"><a class="page-link number">Last</a></li>';
}
else {
var nextindex = (parseInt(currentindex) + 1);
pagestring = pagestring + '<li class="paginate_button page-item"><a class="page-link number" onclick="Search_' + Type + '(' + nextindex + ');">Next</a></li>' +
'<li class="paginate_button page-item"><a class="page-link number" onclick="Search_' + Type + '(' + lastindex + ');">Last</a></li>';
}
}
pagestring = '<div class="pagination-right"><nav><ul class="pagination float-md-right float-lg-right">' + pagestring + '</ul></nav></div>';
$(div).append(pagestring);
}
}
Now run the application.
Here we go…..
![Custom SQL Server Pagination with .Net Core MVC and JQuery]()
If you have any problem then please let me know.