Customized Paging With Repeater And SQL

Question

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!

Answer 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);