|
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.
Printer Friendly Version
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.
|