Implementing Efficient Data Paging with the Datalist Control in ASP.NET 2.0
INTRODUCTION
The purpose of this article is to describe how to implement custom paging with the
DataList
control in ASP.NET 2.0. I will show you how to retrieve a subset of data, display the data using a DataList
, and provide the navigation interface. I decided to write this article after reading Scott Guthrie's article titled Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource. ThePhotos.aspx page in the Personal Web Site Starter kit is a perfect example since it uses a DataList
and an ObjectDataSource
. This starter kit can be downloaded from Microsoft.THE PROBLEM
The Photos.aspx in the Personal Web Site Starter kit uses a
DataList
to display the photo thumbnails bound to an ObjectDataSource
. The ObjectDataSource
uses the GetPhotos
method of thePhotoManager
class to retrieve all the photo records belonging to the selected album. The thumbnails (one per photo record) are displayed on the page, four per row. As you can see, you can only fit 8 or 12 thumbnails on a page, and if you have more, you will have to scroll down to see the rest. This is usually not a big problem if you have less than 100 photos per album. But if you have 200 photos or more, you realize that a paging solution is desirable.
As I mentioned earlier, I borrowed some ideas from Scott Guthrie's article. In his example, the paging UI consists of Previous Page and Next Page links, with the current page versus total number of pages. One can notice that it can be improved easily by adding the ability to jump to a particular page or move to the first or last page.
THE SOLUTION
We are going to implement our custom paging in the photos.aspx web form. We have to create a new
GetPhotos
method in the PhotoManager
class, which will call the new stored procedureGetPhotosByPageIndex
. This stored procedure will retrieve a subset of the data based on thepageIndex
and the number of rows. In terms of the paging UI, I decided to expand on it by adding page number links and a "View All" link to display all the data. To display the paging UI for navigating through the records, we have to display some buttons or links, and optionally, some page number links and a View All link. Here is an example of paging found in a typical online store.List of changes
- photos.aspx
- GetPhotosByPageIndex stored procedure
- photomanager.vb
- photos.aspx.vb
- default.skin
Photos.aspx
Hide Shrink Copy Code
<%@ Page Language="VB" MasterPageFile="~/Default.master"
Title="Your Name Here | Photos"
CodeFile="Photos.aspx.vb"
Inherits="Photos_aspx" %>
<asp:content id="Content1"
contentplaceholderid="Main" runat="server">
<div class="shim solid"></div>
<div class="page" id="photos">
<div class="buttonbar buttonbar-top">
<a href="Albums.aspx">
<asp:image ID="Image1" runat="Server" skinid="gallery" />
</a>
</div>
<asp:DataList ID="DataList1" runat="Server"
cssclass="view" dataSourceID="ObjectDataSource1"
repeatColumns="4" repeatdirection="Horizontal"
onitemdatabound="DataList1_ItemDataBound"
EnableViewState="false">
<ItemTemplate>
<table border="0" cellpadding="0"
cellspacing="0" class="photo-frame">
<tr>
<td class="topx--"></td>
<td class="top-x-"></td>
<td class="top--x"></td>
</tr>
<tr>
<td class="midx--"></td>
<td><a id="DetailLink" runat="server">
<img src="Handler.ashx?PhotoID=<%#
Eval("PhotoID") %>&Size=S" class="photo_198"
style="border:4px solid white"
alt='Thumbnail of Photo Number
<%# Eval("PhotoID") %>' />
</a>
</td>
<td class="mid--x"></td>
</tr>
<tr>
<td class="botx--"></td>
<td class="bot-x-"></td>
<td class="bot--x"></td>
</tr>
</table>
<p><%# Server.HtmlEncode(Eval("Caption").ToString()) %></p>
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:DataList>
<div id="paging" align="center">
<asp:Label ID="PagerLocation" runat="server" />
<asp:PlaceHolder ID="ViewAll" runat="server"></asp:PlaceHolder>
<a id="PrevPage" runat="server">
<asp:image runat="Server" id="PrevImage"
AlternateText="Previous page" skinid="prevpage"/></a>
<asp:PlaceHolder ID="PageLinks" runat="server"></asp:PlaceHolder>
<a id="NextPage" runat="server">
<asp:image runat="Server" id="NextImage"
AlternateText="Next page" skinid="nextpage"/></a>
</div>
<asp:panel id="Panel1" runat="server" visible="false"
CssClass="nullpanel">There are currently no pictures
in this album.</asp:panel>
<div class="buttonbar">
<a href="Albums.aspx"><asp:image id="gallery"
runat="Server" skinid="gallery" /></a>
</div>
</div>
<asp:ObjectDataSource ID="ObjectDataSource1"
Runat="server" TypeName="PhotoManager"
SelectMethod="GetPhotos">
<SelectParameters>
<asp:QueryStringParameter Name="AlbumID"
Type="Int32" QueryStringField="AlbumID" DefaultValue="0"/>
<asp:QueryStringParameter Name="PageIndex"
QueryStringField="PageIndex" DefaultValue="0" />
<asp:QueryStringParameter Name="NumRows"
QueryStringField="NumRows" DefaultValue="8" />
<asp:Parameter Name="PhotoCount"
Direction="Output" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
</asp:content>
The photos.aspx web form contains a
DataList
with an ItemTemplate
for rendering each thumbnail image, a div
element for displaying the navigation links, and an ObjectDataSource
. TheItemTemplate
contains a hyperlink, DetailLink
, which will be created during the ItemDataBound
event of the DataList
. The paging div
element will contain a PagerLocation
label, a ViewAll placeholder, a Previous page image button, a placeholder for the page number links, and a Next page image button. The ObjectDatasource
contains additional parameters such as PageIndex
, NumRows
, and PhotoCount
for creating the paging UI. You can change the number of thumbnails per page by setting the DefaultValue
of the NumRows
parameter. In this example, there will be 8 thumbnails per page. The PhotoCount
parameter will contain the number of photos which will be displayed in the paging UI.GetPhotosByPageIndex stored procedure
The database contains the two tables: Albums and Photos.
Hide Shrink Copy Code
CREATE PROCEDURE dbo.GetPhotosByPageIndex
(
@AlbumID int,
@IsPublic bit,
@PageIndex INT,
@NumRows INT,
@PhotoCount INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
/*
The below statement enable returning
the Total Photo Count for the AlbumID
as output paramster to our SPROC. This enables
us to avoid having to make a separate call to the
database to retrieve them, and can help
improve performance quite a bit
*/
SELECT @PhotoCount=(SELECT COUNT(*) FROM Photos
where Photos.AlbumID=@AlbumID)
Declare @startRowIndex INT;
set @startRowIndex = (@PageIndex * @NumRows) + 1;
With PhotoEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY PhotoID ASC) as Row,
PhotoID, Photos.AlbumID, Photos.Caption
FROM [Photos] LEFT JOIN [Albums]
ON [Albums].[AlbumID] = [Photos].[AlbumID]
WHERE [Photos].[AlbumID] = @AlbumID
AND ([Albums].[IsPublic] = @IsPublic
OR [Albums].[IsPublic] = 1)
)
SELECT PhotoID, AlbumID, Caption
FROM PhotoEntries
WHERE Row between
@startRowIndex and @StartRowIndex+@NumRows-1
END
This stored procedure works only for SQL server 2005 (Express or Standard). It's more efficient since we only retrieve the records that we are interested in. Notice that it takes advantage of the new
ROW_NUMBER()
function in SQL Server 2005. It returns the number of photos into the PhotoCount
output parameter.PhotoManager.vb
Hide Shrink Copy Code
Public Shared Function GetPhotos(ByVal albumid As Integer, _
ByVal PageIndex As Integer, ByVal NumRows As Integer, _
ByRef PhotoCount As Integer) As List(Of Photo)
Using connection As New _
SqlConnection(ConfigurationManager.ConnectionStrings(
"Personal").ConnectionString)
Using command As New SqlCommand("GetPhotosByPageIndex", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@AlbumID", albumid))
Dim Filter As Boolean = _
Not (HttpContext.Current.User.IsInRole("Friends") _
Or HttpContext.Current.User.IsInRole("Administrators"))
command.Parameters.Add(New SqlParameter("@IsPublic", filter))
command.Parameters.Add(New SqlParameter("@PageIndex", PageIndex))
command.Parameters.Add(New SqlParameter("@NumRows", NumRows))
Dim parm As SqlParameter = _
command.Parameters.Add("@PhotoCount", SqlDbType.Int, 4)
parm.Direction = ParameterDirection.Output
connection.Open()
Dim list As List(Of Photo) = New List(Of Photo)
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
Dim temp As New Photo(CInt(reader("PhotoID")), _
CInt(reader("AlbumID")), CStr(reader("Caption")))
list.Add(temp)
End While
connection.Close()
PhotoCount = Convert.ToInt32(parm.Value)
Return list
End Using
End Using
End Function
This method is specified in the
SelectMethod
property of the ObjectDataSource
. It is used to call the GetPhotosByPageIndex
stored procedure by passing the album ID, IsPublic
, PageIndex
, andNumRows
. It will return a list of photos and the total number of photos.Photos.aspx.vb
Hide Shrink Copy Code
Partial Class Photos_aspx _
Inherits System.Web.UI.Page
Dim infinity As Integer = 9999
Dim defaultPageSize As Integer
Dim albumID As Integer = 1
Dim pageIndex As Integer = 0
Dim pageSize As Integer = 0
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
albumID = Convert.ToInt32(Request.QueryString("albumID"))
pageIndex = Convert.ToInt32(Request.QueryString("pageIndex"))
defaultPageSize = _
Convert.ToInt32(
ObjectDataSource1.SelectParameters("NumRows").DefaultValue)
pageSize = defaultPageSize
If Request.QueryString("NumRows") <> Nothing Then
pageSize = Convert.ToInt32(Request.QueryString("NumRows"))
End If
End Sub
Protected Sub DataList1_ItemDataBound(ByVal sender As Object, _
ByVal e As DataListItemEventArgs)
If (e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = _
ListItemType.AlternatingItem) Then
Dim Url As String = "Details.aspx?AlbumID={0}&Page={1}"
Dim aLink As HtmlAnchor = _
CType(e.Item.FindControl("DetailLink"), HtmlAnchor)
aLink.HRef = String.Format(Url, albumID, _
defaultPageSize * pageIndex + e.Item.ItemIndex)
End If
If (e.Item.ItemType = ListItemType.Footer) Then
If (DataList1.Items.Count = 0) Then
Panel1.Visible = True
End If
End If
End Sub
Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) _
Handles ObjectDataSource1.Selected
' Retrieve output parameter values returned from
' calling the "ProductsTableAdapter.GetProductsByCategoryId"
' method invoked by the ObjectDataSource control
Dim photoCount As Integer = _
Convert.ToInt32(e.OutputParameters("PhotoCount"))
' Update various page elements with data values
UpdatePagerLocation(pageIndex, pageSize, photoCount)
UpdateNextPrevLinks(albumID, pageIndex, pageSize, photoCount)
UpdatePager(albumID, pageIndex, pageSize, photoCount)
End Sub
Protected Sub UpdatePagerLocation(ByVal pageIndex As Integer, _
ByVal pageSize As Integer, ByVal photoCount As Integer)
Dim currentStartRow As Integer = (pageIndex * pageSize) + 1
Dim currentEndRow As Integer = (pageIndex * pageSize) + pageSize
If (currentEndRow > photoCount) Then
currentEndRow = photoCount
End If
Dim pageCount As Integer
If photoCount Mod pageSize = 0 Then
pageCount = photoCount / pageSize
Else
pageCount = photoCount \ pageSize + 1
End If
PagerLocation.Text = String.Format("Page {0} of {1}", _
pageIndex + 1, pageCount)
If pageSize = infinity Then
PagerLocation.Visible = False
End If
End Sub
Protected Sub UpdateNextPrevLinks(ByVal AlbumID As Integer, _
ByVal pageIndex As Integer, ByVal pageSize As Integer, _
ByVal photoCount As Integer)
Dim navigationFormat As String = _
"photos.aspx?AlbumID={0}&pageIndex={1}&NumRows={2}"
PrevPage.HRef = String.Format(navigationFormat, _
AlbumID, pageIndex - 1, defaultPageSize)
PrevPage.Visible = (pageIndex > 0)
NextPage.HRef = String.Format(navigationFormat, AlbumID, _
pageIndex + 1, defaultPageSize)
NextPage.Visible = (pageIndex + 1) * pageSize < photoCount
End Sub
Protected Sub UpdatePager(ByVal AlbumID As Integer, _
ByVal pageIndex As Integer, ByVal pageSize _
As Integer, ByVal photoCount As Integer)
Dim navigationFormat As String = _
"photos.aspx?AlbumID={0}&pageIndex={1}&NumRows={2}"
Dim link As HyperLink
Dim literal As LiteralControl
Dim lb As Label
Dim count As Integer = 0
Dim index As Integer = 0
If pageSize = infinity Then
pageIndex = -1
End If
Do While count < photoCount
If index = pageIndex Then
lb = New Label
lb.Font.Bold = True
lb.Text = index + 1
PageLinks.Controls.Add(lb)
Else
link = New HyperLink
link.NavigateUrl = String.Format(navigationFormat, _
AlbumID, index, defaultPageSize)
link.Text = index + 1
PageLinks.Controls.Add(link)
End If
literal = New LiteralControl
literal.Text = " "
PageLinks.Controls.Add(literal)
index += 1
count += defaultPageSize
Loop
End Sub
Protected Sub ObjectDataSource1_Selecting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) _
Handles ObjectDataSource1.Selecting
Dim navigationFormat As String = _
"photos.aspx?AlbumID={0}&pageIndex={1}&NumRows={2}"
If pageSize = infinity Then
Dim lb As New Label
lb.Text = "View All"
ViewAll.Controls.Add(lb)
Else
Dim link As New HyperLink
link.NavigateUrl = _
String.Format(navigationFormat, albumID, 0, infinity)
link.Text = "View All"
ViewAll.Controls.Add(link)
End If
End Sub
End Class
In the
Page_Load
event, I retrieve the various querystring parameters. In theDataList1_ItemDataBound
event, the hyperlink named DetailLink
is dynamically updated for each thumbnail image. In the ObjectDataSource1_Selected
event, I call the various methods to render the paging UI. The UpdatePagerLocation
method is used to display the current page versus total pages. UpdateNextPrevLinks
is used to render the Previous and Next buttons. UpdatePager
is used to generate the page number hyperlinks. In the ObjectDataSource1_Selecting
event, I render the "View All" hyperlink or label.Default.skin
The following two lines need to be added to default.skin for the White theme:
Hide Copy Code
<asp:Image runat="server"
ImageUrl="images/button-prev.gif" skinid="prevpage"/>
<asp:Image runat="server"
ImageUrl="images/button-next.gif" skinid="nextpage"/>
The following two lines need to be added to default.skin for the Black theme:
Hide Copy Code
<asp:Image runat="server"
ImageUrl="images/button-prev.jpg" skinid="prevpage"/>
<asp:Image runat="server"
ImageUrl="images/button-next.jpg" skinid="nextpage"/>
The personal Web Site starter kit can be found here.
0 comments:
Post a Comment