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 DataListand 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
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 ItemDataBoundevent 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
Copy CodeCREATE 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 PhotoCountoutput parameter.PhotoManager.vb
Hide Shrink Copy Code
Copy CodePublic 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
Copy CodePartial 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. UpdatePageris 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