 |
Introduction
Most of the times while creating database driven web pages like
product listing, employee directory, etc. we display the data in grid
format. ASP.NET has come up with Web Controls like DataGrid, DataList
and Repeater that allow you to display your data in tabular format
easily.
Amongst the above three Web Controls the DataGrid Control is the
most advanced and supports paging, templates etc. The other two
controls are used in places where you need more control over the
rendering of your data. The DataList and Repeater Controls have very
flexible templates that give you total control over the formatting of
your data, but one of the major drawbacks of these controls is that
they do not support paging!!
Paging simply means splitting data display (UI) over number of
pages in order to facilitate easy to browse interface and minimize the
data sent out to the client.
For example, you have a web page displaying your products listing. It
would not be a good idea to show all the 1000+ products you are
offering on the one single page, since it will make the page difficult
to browse as well as it will take a lot of time to display on the
clients browser due to the size of data (plus a heavy load on your
server).
The second reason this is not a good idea is that the client would not
want to know about all the products you are selling since the last 10
years, he might visit to page to look out for the new products that
you are offering.
In such scenarios, you divide the display of data into different pages
and then display say 10 - 20 items per page and provide the client
with links to view additional pages, this is called Paging.
Remember we are using server-side scripting so you don't have to
physically create all the pages, you just have to code one page in
such a way that it keeps paging all the records.
Hence some of the merits of paging are:
1) Easy to browse pages.
2) Faster to load pages on client side since the amount to display per
page is less.
3) Less load on the database server, since for every user you only
pull out a limited amount of data, rather than pulling out all the
records for each client.
As I mentioned before, the DataList and Repeater controls are very
flexible and there would be a large number of places you might want to
use these controls. Even though these controls do not support Paging
internally, in this article I will display a method using which, you
can easily enable simple paging (previous , next ) in your DataList
and Repeater controls.
25 October 2002, Update: The small error in the BuildPagers
method has been corrected. Thanks to all readers who pointed out the bug!
Requirements
1) ASP.NET v1
2) SQL Server 7.0/2000/MSDE
(Optional, I am going to use the Northwind database that installs with
the .NET SDK)
Simple Paging in Repeater Control
1) Listing 1, shows the code for a normal page that
selects all records from the Products table and displays it
using the Repeater control.
<%@ Page Language="C#" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
void Page_Load( Object sender , EventArgs e)
{
//Build the Grid only if the page has been accessed for the first time
if( !IsPostBack )
BuildGrid();
}
public void BuildGrid()
{
SqlConnection myConnection =
new SqlConnection(
"server=(local)\\NetSDK;database=Northwind;Trusted_Connection=yes" );
SqlDataAdapter myAdapter =
new SqlDataAdapter(
"SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products",
myConnection);
//Fill the DataSet
DataSet ds = new DataSet();
myAdapter.Fill(ds,"Products");
//DataBind the Repeater
MyRepeater.DataSource = ds.Tables["Products"].DefaultView;
MyRepeater.DataBind();
}
</script>
<body>
<h1>Products Listing</h1>
<form runat="server">
<ASP:Repeater id="MyRepeater" runat="server">
<HeaderTemplate>
<table width="100%" border="1" cellpadding="1" cellspacing="2">
<tr>
<th>
Product ID
</th>
<th>
Product
</th>
<th>
Quantity Per Unit
</th>
<th>
Unit Price
</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<%# DataBinder.Eval(Container.DataItem, "ProductID") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "QuantityPerUnit") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "UnitPrice", "$ {0}") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</ASP:Repeater>
</form>
</body>
</html> |
Listing 1 - Simple Repeater Control
2) The first step to enable paging in this page is to add three
hidden Html Controls that will maintain the values necessary for
paging. You can add these controls below after your repeater control.
<input type="hidden" id="PageSize" value="10"
runat="server">
<input type="hidden" id="CurrentPage" value="1" runat="server">
<input type="hidden" id="TotalSize" runat="server"> |
Listing 2 - Hidden Html Controls
The first control with the id PageSize is used to define the number
of records you want to display per page. Current I have set it to
display 10 records, you can set it to a value you wish.
The second control with the id CurrentPage is used to track the
current page that's been displayed. Usually you want to start displaying the records from the
first page so we set the value of this control to 1.
Lastly, the control with the id TotalSize is used to store the count
of the total number of records available. 3) Next, we add two
LinkButton controls that will enable navigation to the previous and
next pages. Add these controls below the Repeater control definition.
Note: You might be tempted to include these controls within your
Repeater control's FooterTemplate, but I advise you not to follow this
approach since it will complicate matters much more while
writing code to handle these buttons it will be very difficult to get
a reference to these controls.
<asp:LinkButton id="Prev" Text="<< Previous"
OnClick="Page_Repeater" runat="server" />
<asp:LinkButton id="Next" Text="Next >>" OnClick="Page_Repeater"
runat="server" /> |
Listing 3 - Link Buttons
Listing 3, defines the two LinkButtons with the id Prev and
Next
respectively. Feel free to change the Text property of these controls
to something appropriate to your implementation. Also note that I have
set the OnClick event of both these controls to one single event
handling method called Page_Repeater. Listing 4 displays the
Page_Repeater method code.
public void Page_Repeater( object sender, EventArgs e )
{
//Check for Button clicked
if( ((LinkButton)sender).ID == "Prev" )
{
//Check if we are on any page greater than 0
if( ( int.Parse( CurrentPage.Value ) - 1 ) >=0 )
{
//Decrease the CurrentPage Value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) - 1 ).ToString() ;
}
}
else if( ((LinkButton)sender).ID == "Next" )
{
//Check if we can display the next page.
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
< int.Parse( TotalSize.Value ) )
{
//Increment the CurrentPage value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) + 1 ).ToString() ;
}
}
//Rebuild the Grid
BuildGrid();
}
|
Listing 4: Page_Repeater method
In the Page_Repeater method I first check which of the button was
clicked.
If the Prev LinkButton was clicked, I check the value of the
control with id CurrentPage. If the current value minus 1 is greater than equal to 0, it
indicates I have previous pages to display. So I decrement the value
of the control with id CurrentPage by 1.
If the Next LinkButton was clicked, I check out if the product of the
controls with id's CurrentPage and the PageSize is less than the
value of the control with id TotalSize, indicating
that I have more pages to display. Hence I increment the value of the
control with id CurrentPage.
Lastly, the BuildGrid method is given a call to re-build the Repeater.
4) The current BuildGrid method is configured to fetch all the records
from the database. As I had stated earlier this is a bad practice and
only limited records should be fetched each time to increase
scalability of your web site. In Listing 5, below I modify the
BuildGrid method so that it only pulls out the required records from the
database.
In case you are data binding your control to a DataReader rather then
a DataSet, then you will have to modify your SQL Statement
appropriately so that only the required records are fetched.
public void BuildGrid()
{
SqlConnection myConnection =
new SqlConnection(
"server=(local)\\NetSDK;database=Northwind;Trusted_Connection=yes" );
SqlDataAdapter myAdapter =
new SqlDataAdapter(
"SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products" ,
myConnection);
//Fill the DataSet
DataSet ds = new DataSet();
//Get the start Record Count
//Remember database count is zero based so first decrease the value of
//the current page
int startRecord = ( int.Parse( CurrentPage.Value ) - 1 ) *
int.Parse( PageSize.Value );
//Fetch only the necessary records.
myAdapter.Fill( ds , startRecord , int.Parse( PageSize.Value ) , "Products");
//DataBind the Repeater
MyRepeater.DataSource = ds.Tables["Products"].DefaultView;
MyRepeater.DataBind();
//Second Part
//Create a new Command to select the total number of records
SqlCommand myCmd = new SqlCommand( "SELECT Count(*) FROM Products", myConnection );
myConnection.Open();
//retrieve the value
TotalSize.Value = myCmd.ExecuteScalar().ToString() ;
myConnection.Close();
}
|
Listing 5 - Modified BuildGrid method
As its clear from Listing 5, the only change I have made is that
now I use a different overload of the Fill method that takes the
start
index and the number of records to fetch along with the standard DataSet
and table name. Using this overload reduces the amount of data fetched
from the database increasing the performance of your application.
Next, I construct and execute another query that returns the total
number of records the database contains. This value is then stored to
the control with id TotalSize. Remember that we cannot get the total
records count from the DataSet since our updated Fill
method only returns the necessary number of records. In your own
implementation you will have to change this SQL query appropriately to
return the number of records present in the database.
You can leave this second part out from your code if you just want to
display a fixed number of pages and directly set the value of the
control with id TotalSize manually. If the number of records does not change
frequently you can encapsulate the second part in an if( !IsPostBack )
structure, so that the total number of records is only retrieved the
first time the Repeater is built. 5) Lastly, we need a method that
enables and disables the LinkButtons depending on the number of
records available, i.e. if you are on the first page, there is no use
of displaying the link to go to the previous page - right ??
public void BuildPagers()
{
//Check if its possible to have the previous page
if( ( int.Parse( CurrentPage.Value ) - 1 ) <= 0 )
{
Prev.Enabled = false;
}
else
{
Prev.Enabled = true ;
}
//Check if its possible to have the next page
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
>= int.Parse( TotalSize.Value ) )
{
Next.Enabled = false;
}
else
{
Next.Enabled = true ;
}
}
|
Listing 6 - BuildPagers method
The BuildPagers method shown in Listing 6, checks if its possible
to show the respective LinkButtons and enables / disables them
respectively. The logic of this method is very similar to the Page_Repeater method. One point worth nothing here is that this method
is called after the Page_Repeater method is called, so that the value
of the control with id CurrentPage has already been changed according
to the button clicked. You can put a call to this method inside the
BuildGrid method. This completes our pager sample, save your page
and test it out !! The complete code for the example is given in
Listing 7.
<%@ Page Language="C#" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
void Page_Load( Object sender , EventArgs e)
{
//Build the Grid only if the page has been accessed for the first time
if( !IsPostBack )
BuildGrid();
}
public void BuildGrid()
{
SqlConnection myConnection =
new SqlConnection(
"server=(local)\\NetSDK;database=Northwind;Trusted_Connection=yes" );
SqlDataAdapter myAdapter =
new SqlDataAdapter(
"SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products" ,
myConnection);
//Fill the DataSet
DataSet ds = new DataSet();
//Get the startRecord Count
//Remember database count is zero based so first decrease the value of
//the current page
int startRecord = ( int.Parse( CurrentPage.Value ) - 1 ) *
int.Parse( PageSize.Value );
//Fetch only the necessary records.
myAdapter.Fill( ds , startRecord , int.Parse( PageSize.Value ) ,
"Products");
//DataBind the Repeater
MyRepeater.DataSource = ds.Tables["Products"].DefaultView;
MyRepeater.DataBind();
//Second Part
//Create a new Command to select the total number of records
SqlCommand myCmd = new SqlCommand( "SELECT Count(*) from Products",
myConnection );
myConnection.Open();
//retrieve the value
TotalSize.Value = myCmd.ExecuteScalar().ToString() ;
myConnection.Close();
BuildPagers();
}
public void Page_Repeater( object sender, EventArgs e )
{
//Check for Button clicked
if( ((LinkButton)sender).ID == "Prev" )
{
//Check if we are on any page greater than 0
if( ( int.Parse( CurrentPage.Value ) - 1 ) >= 0 )
{
//Decrease the CurrentPage Value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) - 1 ).ToString() ;
}
}
else if( ((LinkButton)sender).ID == "Next" )
{
//Check if we can display the next page.
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
< int.Parse( TotalSize.Value ) )
{
//Increment the CurrentPage value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) + 1 ).ToString() ;
}
}
//Rebuild the Grid
BuildGrid();
}
public void BuildPagers()
{
//Check if its possible to have the previous page
if( ( int.Parse( CurrentPage.Value ) - 1 ) <= 0 )
{
Prev.Enabled = false;
}
else
{
Prev.Enabled = true ;
}
//Check if its possible to have the next page
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
>= int.Parse( TotalSize.Value ) )
{
Next.Enabled = false;
}
else
{
Next.Enabled = true ;
}
}
</script>
<body>
<h1>Products Listing</h1>
<form runat="server">
<ASP:Repeater id="MyRepeater" runat="server">
<HeaderTemplate>
<table width="100%" border="1" cellpadding="1" cellspacing="2">
<tr>
<th>
Product ID
</th>
<th>
Product
</th>
<th>
Quantity Per Unit
</th>
<th>
Unit Price
</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<%# DataBinder.Eval(Container.DataItem, "ProductID") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "QuantityPerUnit") %>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "UnitPrice", "$ {0}") %>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</ASP:Repeater>
<input type="hidden" id="PageSize" value="10" runat="server">
<input type="hidden" id="CurrentPage" value="1" runat="server">
<input type="hidden" id="TotalSize" runat="server">
<asp:LinkButton id="Prev" Text="<< Previous" OnClick="Page_Repeater" runat="server" />
<asp:LinkButton id="Next" Text="Next >>" OnClick="Page_Repeater" runat="server" />
</form>
</body>
</html>
|
Listing 7 - Simple Paging in Repeater (Full Code)
Simple Paging in DataList Control
The method that I have used above works the same way with DataList
controls
too, so I am not repeating the steps again. Instead I am including the
full source code for an example that uses a DataList control to
display the same data.
<%@ Page Language="C#" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
void Page_Load( Object sender , EventArgs e)
{
//Build the Grid only if the page has been accessed for the first time
if( !IsPostBack )
BuildGrid();
}
public void BuildGrid()
{
SqlConnection myConnection =
new SqlConnection(
"server=(local)\\NetSDK;database=Northwind;Trusted_Connection=yes" );
SqlDataAdapter myAdapter =
new SqlDataAdapter(
"SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products" ,
myConnection);
//Fill the DataSet
DataSet ds = new DataSet();
//Get the startRecord Count
//Remember database count is zero based so first decrease the value of
//the current page
int startRecord = ( int.Parse( CurrentPage.Value ) - 1 ) *
int.Parse( PageSize.Value );
//Fetch only the necessary records.
myAdapter.Fill( ds , startRecord , int.Parse( PageSize.Value ) , "Products");
//DataBind the DataList
MyDataList.DataSource = ds.Tables["Products"].DefaultView;
MyDataList.DataBind();
//Second Part
//Create a new Command to select the total number of records
SqlCommand myCmd = new SqlCommand( "SELECT Count(*) from Products",
myConnection );
myConnection.Open();
//retrieve the value
TotalSize.Value = myCmd.ExecuteScalar().ToString() ;
myConnection.Close();
BuildPagers();
}
public void Page_DataList( object sender, EventArgs e )
{
//Check for Button clicked
if( ((LinkButton)sender).ID == "Prev" )
{
//Check if we are on any page greater than 0
if( ( int.Parse( CurrentPage.Value ) - 1 ) >= 0 )
{
//Decrease the CurrentPage Value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) - 1 ).ToString() ;
}
}
else if( ((LinkButton)sender).ID == "Next" )
{
//Check if we can display the next page.
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
< int.Parse( TotalSize.Value ) )
{
//Increment the CurrentPage value
CurrentPage.Value = ( int.Parse( CurrentPage.Value ) + 1 ).ToString() ;
}
}
//Rebuild the Grid
BuildGrid();
}
public void BuildPagers()
{
//Check if its possible to have the previous page
if( ( int.Parse( CurrentPage.Value ) - 1 ) <= 0 )
{
Prev.Enabled = false;
}
else
{
Prev.Enabled = true ;
}
//Check if its possible to have the next page
if( ( int.Parse( CurrentPage.Value ) * int.Parse( PageSize.Value ) )
>= int.Parse( TotalSize.Value ) )
{
Next.Enabled = false;
}
else
{
Next.Enabled = true ;
}
}
</script>
<body>
<h1>Products Listing</h1>
<form runat="server">
<ASP:DataList id="MyDataList" RepeatColumns="2"
RepeatDirection="Horizontal" runat="server">
<ItemTemplate>
<div style="padding:15,15,15,15;font-size:10pt;font-family:Verdana">
<div style="font:12pt verdana;color:darkred">
<i><b><%# DataBinder.Eval(Container.DataItem, "ProductName") %></i></b>
</div>
<br>
<b>Product ID: </b><%# DataBinder.Eval(Container.DataItem, "ProductID") %><br>
<b>Quantity per Unit: </b>
<%# DataBinder.Eval(Container.DataItem, "QuantityPerUnit") %>
<br>
<b>Price: </b><%# DataBinder.Eval(Container.DataItem, "UnitPrice", "$ {0}") %><p>
</div>
</ItemTemplate>
</ASP:DataList>
<input type="hidden" id="PageSize" value="10" runat="server">
<input type="hidden" id="CurrentPage" value="1" runat="server">
<input type="hidden" id="TotalSize" runat="server">
<asp:LinkButton id="Prev" Text="<< Previous" OnClick="Page_DataList" runat="server" />
<asp:LinkButton id="Next" Text="Next >>" OnClick="Page_DataList" runat="server" />
</form>
</body>
</html>
|
Listing 7 - Simple Paging in DataList (Full Code)
Conclusion
In this article I displayed how easy it is to enable paging in
Repeater and DataList controls. You can easily extend this sample to
enable advanced paging with page numbers. |
 |
