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

• Download VB.NET demo project - 1.80 MB
• Download C# demo project - 1.80 MB

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.

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

List of changes

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

Photos.aspx

<%@ 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.

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

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

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:

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

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