Efficiently Paging through data using the ASP.NET ListView Control

Posted by: Malcolm Sheridan , on 7/26/2009, in Category ASP.NET
Views: 67933
Abstract: The following article demonstrates how to use the ObjectDataSource control with the ListView control and Linq to SQL to create efficient server side paging.
Efficiently Paging through data using the ASP.NET ListView Control
 
A couple of months ago I did an article on how to Efficiently Server Side Paging with the ASP.NET GridView Control. I thought I’d focus some articles on using the ASP.NET 3.5 ListView control, which I find useful when I need to totally customise the look and feel of the UI. I thought a good starting point would be to also show you how to efficiently page through data using the ListView.  Out of the box, there is paging, but like other controls, the built-in paging is not great when you start connecting to large amounts of data. I’ll show you how to page through the data in an efficient way. This example uses the Northwind database. If you don’t have a copy of it you can download it from here.
Open Visual Studio 2008 and choose File > New > Web > ASP.NET Web Application. Add a new Linq to SQL file to the project and connect to the Northwind database. Drag the Customers table onto the designer. This is the table that will be referenced in the example. 
Customer_Table1
Now that the database layer exists, let’s turn our attention to the ObjectDataSource. The ObjectDataSource has two methods that are required for paging. These are SelectMethod and SelectCountMethod. Also we need to define two variable names that will automatically be passed through from the ObjectDataSource to our methods. These are MaximumRowsParameterName and StartRowIndexParameterName. And lastly you need to set EnablePaging to true. After we have defined everything for the ObjectDataSource the properties window will look similar to following:
Properties
Let’s add the GetCustomerCount and GetCustomers methods. Add a new Class to the project and name it Northwind.cs. This will be a partial class as this example will extend the functionality of the Northwind class defined in the Linq to SQL file. Add the following code to the class:
C#
public int GetCustomerCount()
{
int totalCount = 0;
object o = HttpContext.Current.Cache["TotalCount"];
      if (o == null)
      {
            using (NorthwindDataContext dc = new NorthwindDataContext())
            {
                  totalCount = dc.Customers.Count();
                  HttpContext.Current.Cache["TotalCount"] = totalCount;
}
}
      else
      {
            totalCount = (int)o;
}
      return totalCount;           
}
 
public List<Customer> GetCustomers(int maximumRows, int startRowIndex)
{
using (NorthwindDataContext dc = new NorthwindDataContext())
      {
return dc.Customers.Skip(startRowIndex).Take(maximumRows).ToList();
}
}
VB.NET
Public Function GetCustomerCount() As Integer
Dim totalCount As Integer = 0
Dim o As Object = HttpContext.Current.Cache("TotalCount")
       If o Is Nothing Then
             Using dc As New NorthwindDataContext()
                        totalCount = dc.Customers.Count()
                         HttpContext.Current.Cache("TotalCount") = totalCount
             End Using
       Else
             totalCount = CInt(Fix(o))
       End If
       Return totalCount
End Function
 
Public Function GetCustomers(ByVal maximumRows As Integer, ByVal startRowIndex As Integer) As List(Of Customer)
Using dc As New NorthwindDataContext()
Return dc.Customers.Skip(startRowIndex).Take(maximumRows).ToList()
End Using
End Function
 
The GetCustomers method accepts the arguments that are passed by the ObjectDataSource. It utilizes the Skip and Take methods to only return the rows needed for the ListView control, not the whole contents of the table. The GetCustomerCount method returns a count of all the records in the table. To make this more efficient, instead of hitting the database to get a count of the records for each page, I’m adding it to the ASP.NET Cache so the database is only hit once. That is all that is required for our methods. Now we need to connect this up to a ListView control and see the results.
Open the Default.aspx page. Drag a ListView control onto the page. Open the Smart Tag and select Choose Data Source. Select <New data source...>:
New_Data_Source
A wizard will open and guide you through the setup. The first screen defines the Data Source Type:
Data_Source_Configuration
Select Object and click OK. The following screen asks you to choose a Business Object. Select Choose your business object and select the newly created Customer class:
Choose_Business_Object
 
The following screen asks you to choose a Data Method. Select GetCustomers and click Next:
/Define_Data_Methods
The final screen asks you to define the parameters. Leave the values blank as they will be passed by the ObjectDataSource. Once this is done click Finish to close the wizard:
Define_Parameters
That’s all that’s required for the ObjectDataSource. The next step is to click on the ListView’s smart tag and click Configure ListView...
Object_DataSource
A new screen will open and ask you to configure the ListView’s look and feel as well as enabling paging. This is a must as without it we won’t have access to the DataPager. The DataPager is a new control in ASP.NET 3.5, and this is automatically added when you want a ListView control to have paging:
Configure_ListView
Everything is done now. If you run the project now, you’ll see the ListView paging through the data. To get a better look at what is happening between the website and SQL Server, open SQL Profiler to view the SQL statements that are being generated. 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’s generated utilises the ROW_NUMBER() function which limits the numbers of rows that are returned by the query.

In upcoming articles I’ll show you how to insert, update and delete data using the ListView control.

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

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 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 eBook 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 the 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 Jeff Bragin on Thursday, August 6, 2009 9:47 PM
The out-of-box ListView/LinqDataSource DOES support efficient paging.  you could just drop a ListView and LingDataSource into your webform and it will do exactly what you demo here. You can use sql server profile to verify linqdatasource paging query.
Comment posted by Malcolm Sheridan on Friday, August 7, 2009 8:32 AM
@Jeff Bragin
Yes that is true.  The LinqDataSource does server side paging out of the box.  The downside to this is if you have existing business objects which return collections, they're not available to the LinqDataSource unless they return the System.Data.Linq.Table<> type.  The ObjectDataSource however does allow this and it more flexible in that scenario.
Comment posted by Jeff Bragin on Friday, August 7, 2009 10:06 AM
I agree, if you have to use objectdatasource, your article is the way to go.  but you did not make it clear that is what you mean here. This is what you said in your article, "Out of the box, there is paging, but like other controls, the built-in paging is not great when you start connecting to large amounts of data."
Comment posted by elio_ier on Sunday, August 23, 2009 4:29 PM
Hi, nice code! How about if you wanted to pass a parameter to the query like for example a CustomerTypeId so not all customers are returned but just those that have the specified CustomerTypeId? (this parameter is only for example)
Thanks!
Comment posted by sanjeev on Monday, November 16, 2009 5:15 PM
sorry this custom pagin article is full of errors.
Comment posted by Malcolm Sheridan on Saturday, November 28, 2009 5:56 AM
@sanjeev
Did you download the sample code? It compiled for me.  
Comment posted by furqan butt on Tuesday, August 23, 2011 11:03 PM
@Jeff Bragin saying correct
Comment posted by iman on Wednesday, July 18, 2012 1:00 AM
hi,i receive "No parameterless constructor defined for this object. " Message,where we should to pass  parameters argument?(Maximum rows,startRowIndex)
Comment posted by Nagarajan on Thursday, November 8, 2012 6:21 AM
Great Article

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook