Customized Paging With Repeater And SQL

I’ve been looking for a good tutorial to teach me how to make a customized Paging control with a simple DataBound control like Repeater to implement a high performance paging feature.

I’ve found a lot of articles about that issue, but non of them was a complete answer.

• Paging of Large Resultsets in
  ASP.NET

  This one is awesome, with statistics and great comparison between the different methods of doing so, but the problem is it’s too old before SQL Server had it’s new function ROW_NUMBER()

• N-Layered Web Applications with
  ASP.NET 3.5 Part 4: Sorting, Paging
  and Filtering (The Database Paging
  Section and ignore the rest)

  This one is almost perfect, but he used the grid’s existing paging feature with a customized Method for paging, instead of a repeater.

• Custom SEO friendly paging with
  ASP.NET Repeater or DataList
  control

  I think this one is WAY too complicated, and things can get done easier

I’m using SQL, Items Repeater (with direct binding in the code-behind with no datasources used), PageNumbers repeater (which will have a link as an ItemTemplate to pass querystring so the used method could retrieve the next portion of Items), A Label to hold the current page number and title.

I’ve been trying to implement the example on N-Layered Web Applications with ASP.NET 3.5 Part 4: Sorting, Paging and Filtering (The Database Paging Section and ignore the rest). So far I’ve created a SQL command in my Data Access Lyaer that looks like this :

WITH Records AS ( SELECT ItemId, ItemName, ROW_NUMBER() OVER (ORDER BY ItemId) AS 'RowNumber' FROM   Items)  SELECT * FROM Records WHERE (RowNumber BETWEEN (@startIndex) AND @startIndex + @pageSize - 1)

but now I’m stuck on how to use it in my presentation layer!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can create a custom method to render your own pagination control. Here is an example:

    /// <summary>
    /// Produces html for a pagination control.
    /// </summary>
    /// <param name="page">Page number for the current page (1-based index).</param>
    /// <param name="pageSize">Number or items per page.</param>
    /// <param name="totalItems">Total number of items across all pages.</param>
    /// <returns>Html of a pagination control.</returns>
    public string RenderPaginationControl(int page, int pageSize, int totalItems)
    {
        int totalPages = (int)Math.Ceiling((double)totalItems/pageSize);

        // Create pager.
        StringBuilder pagerSb = new StringBuilder();
        for (int i = 1; i <= totalPages; ++i)
        {
            // If it is NOT a link to current page.
            if (i != page) { pagerSb.Append(string.Format("<a href='/data.aspx?page={0}'>{0}</a>", i)); }
            // If it is the link to current page.
            else { pagerSb.Append(string.Format("<span>{0}</span>", i)); }
        }

        return pagerSb.ToString();
    }

As you can see apart from your sql, you will also need to call

SELECT COUNT(*) FROM Items

and pass that value to totalItems in the RenderPaginationControl.

And as far as the binding to Repeater is concerned – it’s pretty straight forward:

this.MyRepeater.DataSource = DAL.GetItems(page, pageSize);
this.MyRepeater.DataBind();

int totalItems = DAL.GetTotalNumberOfItems();
this.PaginationLabel.Text = RenderPaginationControl(page, pageSize, totalItems);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0