Let’s look at this article with the following steps:
- Get only the articles that belong to a page
- Prepare the Repeater
- Prepare the navigation bar
- Attach the stored procedure to the SQL DataSource
- Pass the Page Number to the stored procedure in the Selecting event
- Make up Page navigation URLs in the Selected event
1. Get only the articles that belong to a page
Since the repeater doesn’t have built-in paging, first thing we need to do it to get the records that belong to a particular page. SQL Server 2005 provides a way to do this with the help of temporary result set and the ROW_NUMBER. This article describes how to get only the records that belong to a page:
http://www.infinitezest.com/articles/selecting-only-the-records-that-make-up-the-current-page.aspx
The stored procedure we are interested in the above article the one at the end (the stored procedure that returns a page full of records as well as the count of records as an OUTPUT parameter).
2. Prepare the Repeater
Prepare a repeater like you normally would (i.e. there are no special considerations for displaying just a page full of information). You will place the fields/information that you like in various templates of this repeater. We are linking the data source to a SQLDataSource control -- and this is where we will get only a page full of records at a time.
Your repeater will look something like the following (with various fields filled in)
<asp:Repeater ID="rptCustomerListing"
runat="server"
DataSourceID="sdsCustomerListing">
<HeaderTemplate>
</HeaderTemplate>
<ItemTemplate>
...
</ItemTemplate>
<SeparatorTemplate>
</SeparatorTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:Repeater>
3. Prepare the navigation bar
The Navigation bar has three pieces to it:
- Previous Navigation HyperLink
- Next Navigation HyperLink
- Center Label that displays some information (e.g. Customer 1 to 6 of 20)
<div class="listing-nav">
<div class="left">
<asp:HyperLink ID="lnkPrevious" NavigateUrl="~/CustomerListing.aspx?page=1" runat="server"> << Previous</asp:HyperLink>
</div>
<div class="right">
<asp:HyperLink ID="lnkNext" NavigateUrl="~/CustomerListing.aspx?page=1" runat="server">Next >> </asp:HyperLink>
</div>
<div class="center">
<asp:Label ID="lblCustomerRange" runat="server" Text="Page"></asp:Label>
</div>
</div>
The two hyperlinks and a label are placed inside different classes. You can use whatever the CSS you want to adjust the look and feel of these controls, but in them you should add the appropriate left/right float characteristics as shown below:
.listing-nav
{
...
}
.listing-nav .left
{
float: left;
}
.listing-nav .right
{
float: right;
}
.listing-nav .center
{
float: inherit;
text-align: center;
}
The above code sets up a navigation bar at the bottom of the repeater.
4. Attach the stored procedure to the SQL DataSource
Now setup an SqlDataSource that connects to the stored procedure we have prepared above. The important event here is OnSelected. Once a selection of a page full of records is done, we will adjust the navigation bar.
<asp:SqlDataSource ID="sdsCustomerListing"
OnSelected="sdsCustomerListing_Selected"
OnSelecting="sdsCustomerListing_Selecting"
runat="server"
ConnectionString="<%$ ConnectionStrings:MyDB %>"
ProviderName="<%$ ConnectionStrings:DotNetRegion.ProviderName %>"
SelectCommandType="StoredProcedure"
SelectCommand="GetCustomers"
ConflictDetection="CompareAllValues">
<SelectParameters>
<asp:Parameter DefaultValue="1" Name="pageNumber" Type="Int16" />
<asp:Parameter DefaultValue="5" Name="pageSize" Type="Int16" />
<asp:Parameter Direction="Output" Name="CustomerCount" Size="3" DefaultValue="" />
</SelectParameters>
</asp:SqlDataSource>
5. Pass the Page Number to stored procedure in the Selecting event
We need pass pageNumber and pageSize values to the stored procedure. As you can see under SelectParameters, the default values for these are 1 and 5; Meaning, if no values are specified, the stored procedure returns the first page with 5 (or less) records.
Here, we are passing the page number as a query string (e.g. .../Customers.aspx?Page=2). So, we would the page number in the Selecting event (just before submitting the query to the database).
/// <summary>
/// Event that occurs just before the stored procedure is called.
/// The parameters for the stored proc are set here.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void sdsCustomerListing_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
if (Request.QueryString["Page"] != null)
e.Command.Parameters["@pageNumber"].Value = Request.QueryString["Page"];
}
There is some additional discussion of Selecting and Selected events in this article:
http://www.infinitezest.com/articles/changing-the-values-of-stored-procedure-parameters-with-sqldatasource.aspx
6. Make up Page navigation URLs in the Selected event
Now we will trap the results in the Selected event and set the Previous and Next hyperlinks so that the listing can be traversed back and forth.
/// <summary>
/// Selected event occurs just after the results from the
/// stored procedure are feteched. Navigation URLs are set here
/// depending on the number of records.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void sdsCustomerListing_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
// To begin with, enable both the Previous and Next buttons
lnkPrevious.Enabled = true;
lnkNext.Enabled = true;
// Adjust the page numbers on the navigation links
int reqPage;
if (Request.QueryString["Page"] != null)
reqPage = Convert.ToInt16(Request.QueryString["Page"]);
else
reqPage = 1;
// Get the parameter values passed to the SQL Data Source
// numCustomers: Total number of Customers in the system
// pageSize: How many records per page
// pageNumber: The current page that is being displayed
int numCustomers = Convert.ToInt16(e.Command.Parameters["@CustomerCount"].Value.ToString());
int pageSize = Convert.ToInt16(e.Command.Parameters["@pageSize"].Value.ToString());
int pageNumber = Convert.ToInt16(e.Command.Parameters["@pageNumber"].Value.ToString());
// Check the cases where Next or Previous should not be enabled.
// If this is the last page, there is no Next
if (numCustomers <= reqPage * pageSize)
lnkNext.Enabled = false;
// If this is the first page, there is no Previous
if (reqPage < 2)
lnkPrevious.Enabled = false;
// Make up the center string
int startCustomer = pageSize * (pageNumber - 1) + 1;
int endCustomer = (numCustomers > pageSize * pageNumber) ? pageSize * pageNumber : numCustomers;
lblCustomerRange.Text = "Page " + pageNumber + " : Customer " + startCustomer + " to " + endCustomer + " of " + numCustomers;
// Make up the next and previous query strings
StringBuilder nextQueryString = new StringBuilder();
StringBuilder prevQueryString = new StringBuilder();
bool pageKeyExists = false;
bool isFirstKey = true;
foreach (string key in Request.QueryString.AllKeys)
{
// We don’t want to put & infront of the first key in the querystring
if (!isFirstKey)
{
nextQueryString.Append("&");
prevQueryString.Append("&");
}
// Increase (for Next) or Decrease (for Previous) the page number
if (key == "Page")
{
nextQueryString.AppendFormat("Page={0}", reqPage + 1);
prevQueryString.AppendFormat("Page={0}", reqPage - 1);
pageKeyExists = true;
}
// If it’s not page key, don’t disturb it
else
{
nextQueryString.AppendFormat("{0}={1}", key, Request.QueryString[key]);
prevQueryString.AppendFormat("{0}={1}", key, Request.QueryString[key]);
}
isFirstKey = false;
}
// If the Page key does not exist in the query string, the values shown
// belong to Page 1. We need to now go to the second page.
if (!pageKeyExists)
{
// If this is the first key, don’t put an & in front of the key.
if (Request.QueryString.Keys.Count > 0)
{
nextQueryString.Append("&");
prevQueryString.Append("&");
}
// Set the Next to Page 2 and Disable the previous button
nextQueryString.Append("Page=2");
lnkPrevious.Enabled = false;
}
// Makeup the URLs for the navigation buttons.
lnkPrevious.NavigateUrl = "~/CustomerListing.aspx?" + prevQueryString.ToString();
lnkNext.NavigateUrl = "~/CustomerListing.aspx?" + nextQueryString.ToString();
}