Implementing Paging in a DataList Control...

The DataList does not include inbuilt support for paging. By overloading the Fill method of the DataAdapter, however, you can create a paging mechanism for the DataList

By: John Kilgo Date: April 19, 2003 Download the code.

A couple of months ago I wrote an article on presenting and editing data with the DataList. Someone who read the article wrote to ask if there was a way to implement paging in the datalist. Unlike the DataGrid, the DataList has no inbuilt paging mechanism. As with most things in .NET, however, where there is a will, there is a way.

The secret to implementing paging in the DataList control lies not in the control itself, but in the DataAdapter's Fill method which can be overloaded in several different ways. One way to overload it is to pass the arguments DataSet, starting record, number of records, and the table name or index. Number of records is analogous to "page size" in a DataGrid. Starting record is the "index" of the current starting record. For example, if our page size is 10 and we are on the first page of records, then starting record would be 0 and our 10 displayed rows would be 0 through 9. If the NextPage button is clicked, then starting record would be 10 and displayed rows would be 10 through 19, etc. All we need then is a mechanism for specifying page size (number of records) and current index (starting record). Page size is normally a static value that can be specified once in the program. Starting record (current index) will change from page to page and must be tracked within our code-behind logic.

We will use three invisible label controls on the .aspx page to store total record count, current index, and page size. The .aspx page is presented below. I used images (VCR buttons) for the paging mechanism. I needed an html element where I could include an OnServerClick event and I wanted to make the images appear as links (cursor = hand). The easiest way to do this was to use links to a bookmark at the top of the page. The rest of the markup should be self evident.

<%@ Page language="vb" Src="PagingDataList.aspx.vb" Inherits="PagingDataList" %>
<html>
<head>
<title>DataList Paging</title>
<style rel="StyleSheet">
.tablehead
  {
  font-family: Verdana, Arial, Helvetica, Sans-Serif;
  background-color:#006699;
  color:White;
  font-size: 10pt;
  }
.tablebody
  {
  font-family: Verdana, Arial, Helvetica, Sans-Serif;
  background-color:Gainsboro;
  color:Black;
  font-size: 10pt;
  }
.plaintable
  {
  font-family: Verdana, Arial, Helvetica, Sans-Serif;
  background-color:White;
  color:Black;
  font-size: 10pt;
  }
</style>
</head>
<body>
<form runat="server">
<h3><font face="verdana">DataList Paging</font></h3>
<a name="BookMark"></a>
<asp:DataList ID="dtlCustomers" Runat="server" Width="100%" BorderWidth="1" BorderColor="#006699">
  <HeaderTemplate>
    <table width="100%" cellspacing="0" cellpadding="0">
      <tr class="tablehead">
        <th align="left" width="30%">Company Name</th>
        <th align="left" width="15%">Contact Name</th>
        <th align="left" width="25%">Contact Title</th>
        <th align="left" width="15%">Country</th>
        <th align="left" width="15%">Telephone</th>
      </tr>
  </HeaderTemplate>
  <ItemTemplate>
    <tr class="tablebody">
      <td><%# DataBinder.Eval(Container.DataItem, "CompanyName") %></td>
      <td><%# DataBinder.Eval(Container.DataItem, "ContactName") %></td>
      <td><%# DataBinder.Eval(Container.DataItem, "ContactTitle") %></td>
      <td><%# DataBinder.Eval(Container.DataItem, "Country") %></td>
      <td><%# DataBinder.Eval(Container.DataItem, "Phone") %></td>
    </tr>
  </ItemTemplate>
  <FooterTemplate>
    </table>
  </FooterTemplate>
</asp:DataList>
<table width=100%>
  <tr>
    <td width=42% align=left>
      <asp:label ID="lblCounts" Runat="server" CssClass="plaintable" />
    </td>
    <td width=4% valign="bottom">
      <a href="pagingdatalist.aspx#BookMark" id="First" OnServerClick="ShowFirstPage" runat="server">
      <img src="images/firstpage.gif" border="0"></a>
    </td>
    <td width=4% valign="bottom">
      <a href="pagingdatalist.aspx#BookMark" id="Previous" OnServerClick="ShowPreviousPage" runat="server">
      <img src="images/prevpage.gif" border="0"></a>
    </td>
    <td width=4% valign="bottom">
      <a href="pagingdatalist.aspx#BookMark" id="Next" OnServerClick="ShowNextPage" runat="server">
      <img src="images/nextpage.gif" border="0"></a>
    </td>
    <td width=4% valign="bottom">
      <a href="pagingdatalist.aspx#BookMark" id="Last" OnServerClick="ShowLastPage" runat="server">
      <img src="images/lastpage.gif" border="0"></a>
    </td>
    <td width="42%"> </td>
  </tr>
</table>
<asp:label ID="lblRecordCount" Visible="False" Runat="server" />
<asp:label ID="lblCurrentIndex" Visible="False" Text="0" Runat="server" />
<asp:label ID="lblPageSize" Visible="False" Text="10" Runat="server" />
</form>
</body>
</html>

The code-behind page is shown in two parts below. This first part shows the Page_Load event and the BindTheData subroutine. Since the data must be bound to the DataList control with each page change it is convenient, and more efficient to move the data binding to a routine of its own. It is in the latter third of the BindTheData code that we make use of overloading the Fill method of the DataAdapter. That is done with the line:

dataAdapter.Fill (dataSet, Cint(lblCurrentIndex.Text), CInt(lblPageSize.Text), "Customers")

The above line is saying Fill the DataSet, with data beginning at record zero (since CurrentIndex happens to be 0 the first time through), with 10 rows (our specified page size), from the table "Customers". As we click the next and previous page buttons CurrentIndex will change appropriately and the correct page of data will be displayed.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.VisualBasic

Public Class PagingDataList : Inherits System.Web.UI.Page

  Protected lblCurrentIndex As System.Web.UI.WebControls.Label
  Protected lblRecordCount As System.Web.UI.WebControls.Label
  Protected dtlCustomers As System.Web.UI.WebControls.DataList
  Protected lblPageSize As System.Web.UI.WebControls.Label
  Protected lblCounts As System.Web.UI.WebControls.Label

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
    If Not Page.IsPostBack() Then
      BindTheData()
    End If
  End Sub

  Private Sub BindTheData()
    Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
    Dim strSql As String = "SELECT CompanyName, ContactName, ContactTitle, Country, Phone FROM Customers"
    Dim dataAdapter As New SqlDataAdapter(strSql, objConn)
    Dim dataSet As New DataSet()
    If Not Page.IsPostBack() Then
      dataAdapter.Fill(dataSet)
      lblRecordCount.Text = CStr(dataSet.Tables(0).Rows.Count)
      dataSet = Nothing
      dataSet = New DataSet()
    End If
    dataAdapter.Fill (dataSet, Cint(lblCurrentIndex.Text), CInt(lblPageSize.Text), "Customers")
    dtlCustomers.DataSource = dataSet.Tables("Customers").DefaultView
    dtlCustomers.DataBind()
    objConn.Close()
    ShowCounts()
  End Sub

The remainder of the code-behind page is shown below. The first four routines are for handling the button click on our First, Previous, Next, and Last navigation links. Essentially all they do is set the correct value for CurrentIndex. The Previous and Next routines simple increment or decrement the CurrentIndex by the PageSize as appropriate. First and Last are special cases in that First just always sets CurrentIndex to 0, while Last subtracts PageSize from RecordCount. The last routine (ShowCounts) just updates our status line showing which of how many pages we are currently displaying.

  Public Sub ShowFirstPage(ByVal s As System.Object, ByVal e As System.EventArgs)
    lblCurrentIndex.Text = "0"
    BindTheData()
  End Sub

  Public Sub ShowPreviousPage(ByVal s As System.Object, ByVal e As System.EventArgs)
    lblCurrentIndex.Text = Cstr(Cint(lblCurrentIndex.Text) - CInt(lblPageSize.Text))
    If CInt(lblCurrentIndex.Text) < 0 Then
      lblCurrentIndex.Text = "0"
    End If
    BindTheData()
  End Sub

  Public Sub ShowNextPage(ByVal s As System.Object, ByVal e As System.EventArgs)
    If CInt(CInt(lblCurrentIndex.Text) + CInt(lblPageSize.Text)) < CInt(lblRecordCount.Text) Then
      lblCurrentIndex.Text = CStr(CInt(lblCurrentIndex.Text) + CInt(lblPageSize.Text))
    End If
    BindTheData()
  End Sub

  Public Sub ShowLastPage(ByVal s As System.Object, ByVal e As System.EventArgs)
    Dim intMod as Integer
    intMod = CInt(lblRecordCount.Text) Mod CInt(lblPageSize.Text)
    If intMod > 0 Then
      lblCurrentIndex.Text = Cstr(CInt(lblRecordCount.Text) - intMod)
    Else
      lblCurrentIndex.Text = Cstr(CInt(lblRecordCount.Text) - CInt(lblPageSize.Text))
    End If
    BindTheData()
  End Sub

  Private Sub ShowCounts()
    lblCounts.Text = "|Total Rows: <b>" & lblRecordCount.Text
    lblCounts.Text += "</b> | Page:<b> "
    lblCounts.Text += CStr(CInt(CInt(lblCurrentIndex.Text) / CInt(lblPageSize.Text)+1))
    lblCounts.Text += "</b> of <b>"
    If (CInt(lblRecordCount.Text) Mod CInt(lblPageSize.Text)) > 0 Then
      lblCounts.Text += CStr(Fix(CInt(lblRecordCount.Text) / CInt(lblPageSize.Text)+1))
    Else
      lblCounts.Text += CStr(Fix(lblRecordCount.Text) / CInt(lblPageSize.Text))
    End If
    lblCounts.Text += "</b> |"
  End Sub

End Class

A fair amount of code has been presented to implement paging in the DataList control, but I think if you will run the program and then take a careful look at each section of code at a time, you will understand that the process is really not all that complicated. Please be advised that one drawback of this method is that all rows of data from the table are being brought back with each page change. We are only displaying the specific rows we want. For a large table this could become unweildy. Good luck!

You may download the code here.