How to Make Custom SQL Server Pagination Using JQuery And .Net Core MVC

Why do we require SQL Pagination when Grid View and JQuery Data Table both have built-in paging capabilities?

Response is In order to optimize speed, it’s sometimes preferable to send only the data that is necessary rather than the entire load of data over the network. We have SQL paging for that. Just a Page Index and Page Size are needed to load the necessary data.

To do this, I created the following Stored Procedure:

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…..

Please let me know if there are any issues.