Custom SQL Server Pagination with .Net Core MVC and JQuery

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();
            }
        }
    });
}

function Search_qemp(index) {
    $("#hidcurrentindexemp").val(index);
    GetJobOrderList();
}

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);
    }
}