Efficient Server Side Paging With ASP.NET And jQuery

Posted by: Malcolm Sheridan , on 10/4/2009, in Category jQuery and ASP.NET
Views: 53689
Abstract: The following article demonstrates how to use ASP.NET with jQuery to page through data efficiently.
Efficient Server Side Paging With ASP.NET And jQuery
 
Earlier this year I did an article on Efficient Server Side Paging with the ASP.NET GridView Control. That article is still relevant, but I thought it would be a good idea to do this using jQuery. A big reason for using jQuery’s Ajax functionality is you eliminate the need to use page heavy server controls such as the GridView, and you don’t bloat your page with tonnes of ViewState. Well here’s one way to replicate custom paging in the GridView using jQuery.
Before we get started, this example uses the latest version of jQuery which is 1.3.2. That can be downloaded from here. You’ll also need a copy of the Northwind database. If you don’t have a copy of the Northwind database, you can go here to download it. 
Open Visual Studio 2008 and create a new Web Application. To begin with add a new LINQ to SQL Classes file to your project and call it Northwind. Add the customer table to the design surface:
Customer
Save and build your project. Now that is done I’m going to create a custom class to return to the client. I’m creating it this way because the method needs to return not only a list of customers, but it also needs to return the total customer count for the paging to function correctly. Add a new class to your web application and call it CustomerData. Add the following code:
C#
public class CustomerData
{
public List<Customer> Customers { get; set; }
      public int TotalRecords { get; set; }
}
VB.NET
Public Class CustomerData
Private privateCustomers As List(Of Customer)
Public Property Customers() As List(Of Customer)
      Get
            Return privateCustomers
      End Get
      Set(ByVal value As List(Of Customer))
            privateCustomers = value
      End Set
End Property
       Private privateTotalRecords As Integer
       Public Property TotalRecords() As Integer
             Get
                   Return privateTotalRecords
             End Get
             Set(ByVal value As Integer)
                   privateTotalRecords = value
             End Set
       End Property
End Class
The CustomerData class has two properties. The Customers property will contain the list of customers, and TotalRecords will hold the total number of records. Now we need to add one method to the default page to retrieve the data. This needs to be decorated with the WebMethod attribute, so it can be consumed by jQuery’s Ajax function. Add the following method to fetch the customer data.
C#
[WebMethod]
public static CustomerData FetchCustomers(int skip, int take)
{
var data = new CustomerData();
      using (var dc = new NorthwindDataContext())
      {
            var query = (from p in dc.Customers
                            select p)
                            .Skip(skip)
                            .Take(take)                           
                            .ToList();
            data.Customers = query;
            data.TotalRecords = (from p in dc.Customers
                                     select p).Count();
}
return data;
}
VB.NET
<WebMethod> _
Public Shared Function FetchCustomers(ByVal skip As Integer, ByVal take As Integer) As CustomerData
Dim data = New CustomerData()
       Using dc = New NorthwindDataContext()
             Dim query = ( _
                  From p In dc.Customers _
                  Select p).Skip(skip).Take(take).ToList()
                  data.Customers = query
                  data.TotalRecords = ( _
                      From p In dc.Customers _
                      Select p).Count()
       End Using
Return data
End Function
In the code above, the method acceps two parameters, skip and take, which are integer values that will be used to run the IQueryable Take and Skip methods. These methods create an SQL statement that only returns records between the rows starting at the Take value, and then skipping all the rows in the Skip value instead of the whole table. That’s the server code done! Now to turn our attention to the HTML!  To begin with add the following JavaScript and CSS file references to the <head> HTML tag:
<script type="text/javascript" src="Scripts/jquery-1.3.2.js"></script>
 
Next you need to add the following HTML. This will act as a placeholder for the data retrieved from the database.
<input id="btnSearch" type="button" value="Search" />
<span id="totalRecords"></span>
<div id="result"></div>
<div id="paging"></div>
Now that’s done we can start adding the JavaScript. The following is the complete code, but I’ll break it down into smaller pieces:
var pageSize = 10;
function pageData(e) {
    var skip = e == 1 ? 0 : (e * pageSize) - pageSize;
    $.ajax({
        type: "POST",
        url: "Default.aspx/FetchCustomers",
        data: "{skip:" + skip + ",take:" + pageSize + "}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        async: true,
        cache: false,
        success: function(msg) {
            printCustomer(msg);
        }
    });
    return false;
}
 
$(document).ready(function() {
    $("#btnSearch").click(function() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/FetchCustomers",
            data: "{skip:0,take:" + pageSize + "}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            async: true,
            cache: false,
            success: function(msg) {
                var total = msg.d.TotalRecords;
                if (total > 0) {
                    printCustomer(msg);
                    $("#paging").text("");
                    // Get the page count by dividing the total records
                    // by the page size. This has to be rounded up
                    // otherwise you might not get the last page
                    var pageTotal = Math.ceil(total / pageSize);
                    for (var i = 0; i < pageTotal; i++) {
$("#paging").append("<a href=\"#\" onClick=\"pageData(" + (i +  1) + ")\">" + (i + 1) + "</a>&nbsp;");                       
                    }
                }
                else {
                    $("#paging").text("No records were found.");
                }
                $("#totalRecords").text("Total records: " + total);
            }
        });
    });
});
 
// This function accepts a customer object
// and prints the results to the div element.
function printCustomer(msg) {
    $("#result").text("");
    var customers = msg.d.Customers;
    for (var i = 0; i < customers.length; i++) {
        $("#result").append(customers[i].CustomerID + ", ");
        $("#result").append(customers[i].CompanyName + ", ");
        $("#result").append(customers[i].ContactName + ", ");
        $("#result").append(customers[i].ContactTitle + ", ");
        $("#result").append(customers[i].Address + ", ");
        $("#result").append(customers[i].City + ", ");
        $("#result").append(customers[i].Region + "<br />");
    }
In the code above, the first thing I have done is create a variable to hold the total number of records to display per page:
var pageSize = 10;
 
The user will run the search by clicking the HTML button btnSearch. I am using jQuery to attach code to the buttons click event. Once the data is retrieved from the server, the customers will be returned in the msg.d.Customers property, and the total count will be in the msg.d.TotalRecords property. To enable paging, the TotalRecords will be dived by the pageSize value to calculate how many paging options will need to be displayed.
 
$(document).ready(function() {
    $("#btnSearch").click(function() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/FetchCustomers",
            data: "{skip:0,take:" + pageSize + "}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            async: true,
            cache: false,
            success: function(msg) {
                var total = msg.d.TotalRecords;
                if (total > 0) {
                    printCustomer(msg);
                    $("#paging").text("");
                    // Get the page count by dividing the total records
                    // by the page size. This has to be rounded up
                    // otherwise you might not get the last page
                    var pageTotal = Math.ceil(total / pageSize);
                    for (var i = 0; i < pageTotal; i++) {
                        $("#paging").append("<a href=\"#\" onClick=\"pageData(" + (i + 1) + ")\">" + (i + 1) + "</a>&nbsp;");                       
                    }
                }
                else {
                    $("#paging").text("No records were found.");
                }
                $("#totalRecords").text("Total records: " + total);
            }
        });
    });
});
 
Each time the user clicks on a paging option, they’ll run a JavaScript function called pageData. This function will use jQuery’s Ajax functionality to call the server side code, but it will calculate the records to skip and take by the users selection:
 
function pageData(e) {
    var skip = e == 1 ? 0 : (e * pageSize) - pageSize;
    $.ajax({
        type: "POST",
        url: "Default.aspx/FetchCustomers",
        data: "{skip:" + skip + ",take:" + pageSize + "}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        async: true,
        cache: false,
        success: function(msg) {
            printCustomer(msg);
        }
    });
    return false;
}
 
Finally to reduce duplicate code, I have created a function called printCustomers. This will be responsible for enumerating through each customer record and rendering it in the browser:
// This function accepts a customer object
// and prints the results to the div element.
function printCustomer(msg) {
    $("#result").text("");
    var customers = msg.d.Customers;
    for (var i = 0; i < customers.length; i++) {
        $("#result").append(customers[i].CustomerID + ", ");
        $("#result").append(customers[i].CompanyName + ", ");
        $("#result").append(customers[i].ContactName + ", ");
        $("#result").append(customers[i].ContactTitle + ", ");
        $("#result").append(customers[i].Address + ", ");
        $("#result").append(customers[i].City + ", ");
        $("#result").append(customers[i].Region + "<br />");
    }
}
If you run the code you’ll see the records are displayed ten at a time. To view the real efficiency you can use SQL Profiler to see the SQL that is executed on the database. The following code is a sample of what is executed on the SQL Server:
SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
 
The code that is generated, utilises the ROW_NUMBER() function which limits the numbers of rows that are returned.

By handling the paging as shown in this example, your ASP.NET website will be able to display thousands of records efficiently using jQuery and your page weight will be reduced because you’re not using server side controls and your ViewState will be reduced because of this too. This will not only improve performance on the web server, but it will also decrease the amount of data that is sent to the client.

The entire source code of this article can be downloaded over here

If you liked the article,  Subscribe to the RSS Feed or Subscribe Via Email

Malcolm Sheridan is an independent contractor who has been working with Microsoft technologies since VB4. Malcolm has worked with .NET since its inception and thoroughly enjoys ASP.NET.
 

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Malcolm Sheridan is a Microsoft awarded MVP in ASP.NET, a Telerik Insider and a regular presenter at conferences and user groups throughout Australia and New Zealand. Being an ASP.NET guy, his focus is on web technologies and has been for the past 10 years. He loves working with ASP.NET MVC these days and also loves getting his hands dirty with jQuery and JavaScript. He also writes technical articles on ASP.NET for SitePoint and other various websites. Follow him on twitter @malcolmsheridan


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Sandy on Tuesday, October 6, 2009 3:08 AM
I have wriiten a JQuery where I can move items form one Listbox to another and its working fine, when I do a postback ie when I click a Asp server side button the listbox with populated items using Jquery disappears… How can i avoid this
Comment posted by Sandy on Tuesday, October 6, 2009 3:10 AM
here goes the code for the above issue,
(function($) {
           $(document).ready(function() {

                $("#<%= btnAddCH.ClientID %>").click(function() {


                    $("#<%= lstMGrp.ClientID %> > option:selected").appendTo("#<%= lstGrpCH.ClientID %>")

                });



                $("#<%= btnRemoveCH.ClientID %>").click(function() {

                    $("#<%= lstGrpCH.ClientID %> > option:selected").appendTo("#<%= lstMGrp.ClientID %>");

                });



                $("#<%= btnAddCL.ClientID %>").click(function() {

                    $("#<%= lstMGrp.ClientID %> > option:selected").appendTo("#<%= lstGrpCL.ClientID %>");

                });


                $("#<%= btnRemoveCL.ClientID %>").click(function() {

                    $("#<%= lstGrpCL.ClientID %> > option:selected").appendTo("#<%= lstMGrp.ClientID %>");

                });

                $("#<%= btnClear.ClientID %>").click(function() {

                    $("#<%= lstGrpCH.ClientID %> > option").appendTo("#<%= lstMGrp.ClientID %>");
                    $("#<%= lstGrpCL.ClientID %> > option").appendTo("#<%= lstMGrp.ClientID %>");

                });


            });

        })(jQuery);
Comment posted by Malcolm Sheridan on Tuesday, October 6, 2009 6:49 AM
@Sandy
You'd be better off posting your question to a forum such as www.asp.net for your questions.
Comment posted by dario-g on Wednesday, October 7, 2009 4:18 PM
Maybe efficient but very complicated :/
Comment posted by David Jacob Jarquin on Friday, January 8, 2010 10:18 AM
One way to paging using the same server side code, is to get the records when a user scrolls a div (it has to have css prop. overflow-y:auto.
BASICALLY THERE IS AN INTERVAL THAT DETECTS WHEN A DIV SCROLL IS NEAR TO BOTTOM, SENDS AN AJAX REQUEST AND CONCAT THE DATA INTO THE SAME DIV (SAME EFFECT THAN GOOGLE READER)
function startPolling(){ pollID = setInterval("detectScroll()",500); }

function detectScroll(){
var intElemScrollHeightOuter = document.getElementById("new_items_div").clientHeight;
var intElemScrollHeightInner = document.getElementById("new_items").scrollHeight;
var intElemScrolled = document.getElementById("new_items_div").scrollTop;
var height = intElemScrollHeightInner - intElemScrollHeightOuter;
if (intElemScrolled >= height-20) {
//alert("You are at " + document.getElementById("new_items").scrollTop + " pixels. adding rows...");
       document.getElementById('status').innerHTML =
"Showing&nbsp;<b>"+(viewCnt+5)+"</b>&nbsp;items";
fetchAction(viewCnt);
viewCnt +=5;
}
return true;
}
fetchAction(0);
startPolling();
Comment posted by Abdul Wahab kotwal on Saturday, June 18, 2011 1:21 PM
check out my work too on jQuery ASP.net paging
http://wahabkotwal.blogspot.com/2011/06/aspnet-ajax-simple-paging-system-using.html
Comment posted by swapana thorat on Thursday, March 15, 2012 8:10 AM
How can i add first next button??
Comment posted by asg on Monday, February 18, 2013 2:30 PM
thanks a lot . can you have a sample with aspxgridview or gridview.
Comment posted by adds on Wednesday, April 16, 2014 1:04 AM
nice

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook