Summary

Repeater gives tremendous amount of flexibility in formatting the repeating items the way you want. However, it doesn't have the paging support the way some other data controls do. This article looks adding a navigation bar with paging support to a repeater.

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

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

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

</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();
}