Click here to Skip to main content
15,867,308 members
Articles / Web Development / HTML
Article

Implementing Efficient Data Paging with the Datalist Control in ASP.NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.85/5 (26 votes)
11 May 20064 min read 225.7K   9.7K   112   41
Extending the Personal Web site Starter kit by implementing efficient data paging to the Photo Gallery.

Sample Image - paging1.jpg

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. The Photos.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 the PhotoManager 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.

Image 2

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.

Image 3

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 procedure GetPhotosByPageIndex. This stored procedure will retrieve a subset of the data based on the pageIndex 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.

Image 4

List of changes

  • photos.aspx
  • GetPhotosByPageIndex stored procedure
  • photomanager.vb
  • photos.aspx.vb
  • default.skin

Photos.aspx

ASP.NET
<%@ 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. The ItemTemplate 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.

Image 5

SQL
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

VB
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, and NumRows. It will return a list of photos and the total number of photos.

Photos.aspx.vb

VB
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 the DataList1_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:

HTML
<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:

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

History

None.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralRe: how can i implement it with session or cookie Pin
Gufran Sheikh15-Jul-07 22:26
Gufran Sheikh15-Jul-07 22:26 
Generalhi, is possible convert stored procedure in mysql Pin
matrik7827-Apr-07 0:53
matrik7827-Apr-07 0:53 
GeneralRe: hi, is possible convert stored procedure in mysql Pin
Bernard Ho-Jim27-Apr-07 3:16
Bernard Ho-Jim27-Apr-07 3:16 
GeneralRe: hi, is possible convert stored procedure in mysql Pin
Bernard Ho-Jim4-May-07 6:17
Bernard Ho-Jim4-May-07 6:17 
GeneralRe: hi, is possible convert stored procedure in mysql Pin
Erakis15-Dec-09 11:20
Erakis15-Dec-09 11:20 
Generalhelp Pin
armanemadi9-Mar-07 10:30
armanemadi9-Mar-07 10:30 
GeneralRe: help Pin
Bernard Ho-Jim12-Mar-07 2:04
Bernard Ho-Jim12-Mar-07 2:04 
GeneralExcellent Article Pin
GaryWoodfine 2-Feb-07 6:42
professionalGaryWoodfine 2-Feb-07 6:42 
I just wanted to say thank you for taking the time to do this article, I used to understand a whole lot more than just displaying images.

Keep up the good work, I voted you a full whack not something I dish out lightly;P

Kind Regards,
Gary


My Website || My Blog || My Articles

GeneralRe: Excellent Article Pin
Bernard Ho-Jim12-Mar-07 2:11
Bernard Ho-Jim12-Mar-07 2:11 
QuestionHow can I do this with the Club Site Photo Gallery? Pin
bridgetgregory10-Nov-06 15:55
bridgetgregory10-Nov-06 15:55 
GeneralSQL Server 2000 Pin
AWizardInDallas2-Nov-06 19:17
AWizardInDallas2-Nov-06 19:17 
GeneralRe: SQL Server 2000 Pin
Bernard Ho-Jim23-Nov-06 8:48
Bernard Ho-Jim23-Nov-06 8:48 
Generalvalidating the query strings Pin
martinharvey25-Aug-06 0:03
martinharvey25-Aug-06 0:03 
GeneralRe: validating the query strings Pin
Bernard Ho-Jim25-Aug-06 9:37
Bernard Ho-Jim25-Aug-06 9:37 
GeneralRe: validating the query strings Pin
martinharvey26-Aug-06 19:38
martinharvey26-Aug-06 19:38 
GeneralGood Job Pin
ss1111@ss1111.com30-May-06 8:40
ss1111@ss1111.com30-May-06 8:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.