Using PagedDataSource For Paging

GridView, DetailsView or FormView controls are complex controls that have many pre-built features, including data paging or sorting. On opposite, Repeater or DataList offer more control over HTML output and they usually work faster than GridView, but better performances come with lack of complex GridView's features. Implementation of data paging and sorting is very simple with GridView control.

If you use Repeater or DataList, there is no built-in paging functionality and you need to build your own custom data pager since it is usually not acceptable to show hundreds or even thousands of records on single page. Fortunately, there is PagedDataSource class to help you with this task.

PagedDataSource class encapsulates paging functionality of data controls like GridView, FormView or DetailsView. If you ever worked with paging on these controls then using of PagedDataSource control will be familiar to you. Important class members, like AllowPaging, PageSize, PageCount property etc., have same names and use.

How to add paging to Repeater or DataList control using PagedDataSource control

Place one Repeater or DataList control on web form. For this example, I will use data from Products table of Northwind database. If you don't have Northwind database installed on your machine, use whichever database you have. It is not important what kind of data you show since we want to focus on paging.

Markup code for Repeater is pretty simple, it will show only product name. Bellow Repeater, add two LinkButton controls which will be used for navigation, like this:

<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
  <%#Eval("ProductName")%>
</ItemTemplate>
</asp:Repeater>
<br />
<asp:LinkButton ID="lbPrevious" runat="server" Enabled="false">Previous page</asp:LinkButton>
<asp:LinkButton ID="lbNext" runat="server">Next page</asp:LinkButton>

If you load Products table to DataTable object and bind to Repeater directly, it will show all 77 products on same page. But, we'll use PagedDataSource class implement paging. You can understand PagedDataSource as additional layer between data source control and data presentation control that filter record set and returns only one selected page. Here is the code:

[ C# ]

using System;
 
// We need these namespaces imported to work easier with database
using System.Data;
using System.Data.SqlClient;
 
// Here is PagedDataSource class located
using System.Web.UI.WebControls;
 
 
public partial class _Default : System.Web.UI.Page
{
 
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
       // Returns first page by default
      BindData(0);
    }
  }
 
  protected void lbNext_Click(object sender, EventArgs e)
  {
    // Find current page
    int SelectedPage = 0;
    if (ViewState["SelectedPage"] != null)
      SelectedPage = (int)ViewState["SelectedPage"];
 
    // Find next page index
    SelectedPage += 1;
    BindData(SelectedPage);
  }
 
  protected void lbPrevious_Click(object sender, EventArgs e)
  {
    // Find current page
    int SelectedPage = 0;
    if (ViewState["SelectedPage"] != null)
      SelectedPage = (int)ViewState["SelectedPage"];
 
    // Find previous page index
    SelectedPage -= 1;
    BindData(SelectedPage);
  }
 
  private void BindData(int SelectedPage)
  {
    // Step 1: Connect to database and load data
    SqlConnection conn = new SqlConnection("Your connection string goes here");
   conn.Open();
   DataTable dt = new DataTable();
   string SqlQuery = "SELECT * FROM Products";
   SqlDataAdapter da = new SqlDataAdapter(SqlQuery, conn);
   da.Fill(dt);
 
   // Step 2: Create PagedDataSource instance and set its properties
   PagedDataSource pds = new PagedDataSource();
   pds.DataSource = dt.DefaultView;
   pds.AllowPaging = true;
   pds.PageSize = 10;
   if (SelectedPage > (pds.PageCount - 1)
     SelectedPage = pds.PageCount - 1;
    if (SelectedPage < 0)
     SelectedPage = 0;
   pds.CurrentPageIndex = SelectedPage;
 
   // Step 3: Bind PagedDataSource to Repeater and set LinkButtons' behavior
   Repeater1.DataSource = pds;
    if (pds.IsLastPage)
      lbNext.Enabled = false;
    else
      lbNext.Enabled = true;
 
    if (pds.IsFirstPage)
      lbPrevious.Enabled = false;
    else
      lbPrevious.Enabled = true;
  }
}

[ VB.NET ]

Imports System
 
' We need these namespaces to work easier with database
Imports System.Data
Imports System.Data.SqlClient
 
' Here is PagedDataSource class
Imports System.Web.UI.WebControls
 
Partial Class DefaultVBNET
  Inherits System.Web.UI.Page
 
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
     ' Returns first page by default
      BindData(0)
    End If
  End Sub
 
  Protected Sub lbPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbPrevious.Click
    ' Find current page
    Dim SelectedPage As Integer = 0
    If Not (ViewState("SelectedPage") Is Nothing) Then
      SelectedPage = Convert.ToInt32(ViewState("SelectedPage"))
    End If
 
    ' Find previous page index
    SelectedPage -= 1
    BindData(SelectedPage)
  End Sub
 
  Protected Sub lbNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbNext.Click
    ' Find current page
    Dim SelectedPage As Integer = 0
    If Not (ViewState("SelectedPage") Is Nothing) Then
      SelectedPage = Convert.ToInt32(ViewState("SelectedPage"))
    End If
 
    ' Find next page index
    SelectedPage += 1
    BindData(SelectedPage)
  End Sub
 
  Private Sub BindData(ByVal SelectedPage As Integer)
    ' Step 1: Connect to database and load data
    Dim conn As SqlConnection = New SqlConnection("Your connection string goes here")
    conn.Open()
    Dim dt As DataTable = New DataTable()
    Dim SqlQuery As String = "SELECT * FROM Products"
    Dim da As SqlDataAdapter = New SqlDataAdapter(SqlQuery, conn)
     da.Fill(dt)
 
    ' Step 2: Create PagedDataSource instance and set its properties
    Dim pds As PagedDataSource = New PagedDataSource()
    pds.DataSource = dt.DefaultView
    pds.AllowPaging = True
    pds.PageSize = 10
    If SelectedPage > (pds.PageCount - 1) Then
      SelectedPage = pds.PageCount - 1
    End If
    If SelectedPage < 0 Then
      SelectedPage = 0
    End If
    pds.CurrentPageIndex = SelectedPage
 
    ' Step 3: Bind PagedDataSource to Repeater and set LinkButtons' behavior
    Repeater1.DataSource = pds
 
    If pds.IsLastPage Then
      lbNext.Enabled = False
    Else
      lbNext.Enabled = True
    End If
 
    If pds.IsFirstPage Then
      lbPrevious.Enabled = False
    Else
      lbPrevious.Enabled = True
    End If
  End Sub
End Class

Of course, you are not limited to Repeater control. With PagedDataSource you can use DataList, RadioButtonList, CheckBoxList etc., even GridView in some cases, for example if you need different pager on top and on bottom of the page. GridView's PagerTemplate allows you to customize paging but it shows same pager on top and on the bottom. With PagedDataSource you can create two pagers with different styling.

Using PagedDataSource without callbacks

Instead of using Button, LinkButton or other ASP.NET server control, you can create navigation with simple HTML links. In this case, we'll place page variable in query string. Instead of LinkButton controls used in previous example, this time place two PlaceHolder controls phNext and phPrevious (here we'll add HTML links). Code for BindData procedure and Page_Load will look a little different:

[ C# ]

[ VB.NET ]

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  ' Get current page from query string variable
  Dim SelectedPage As String = Request.QueryString("page")
  ' If no page selected, show first page by default
  If SelectedPage Is Nothing Then
    SelectedPage = "0"
  End If
 
  ' Load selected page
  BindData(Convert.ToInt32(SelectedPage))
End Sub
 
Private Sub BindData(ByVal SelectedPage As Integer)
  ' Step 1 and 2 are the same as in previous example
 
  ' Step 3: Bind PagedDataSource to Repeater as before
  Repeater1.DataSource = pds
 
  ' Create static HTML links for paging
  If pds.IsLastPage Then
    litNext.Text = "<b>Next</b>"
  Else
    litNext.Text = "<a href=""?page=" & (SelectedPage + 1).ToString() & _
      """>Next</a>"
  End If
 
  If pds.IsFirstPage Then
    litPrevious.Text = "<b>Previous</b>"
  Else
    litPrevious.Text = "<a href=""?page=" & (SelectedPage - 1).ToString() & _
      """>Previous</a>"
  End If
End Sub

PagedDataSource class members

PagedDataSource class is located in System.Web.UI.WebControls namespace in System.Web assembly, so you don't need to add any new reference or import namespace.

These are few most important PagedDataSource properties:

AllowPaging

Boolean [ get, set ] - default value is false. Like in GridView, AllowPaging determines is paging enabled. You can turn on paging if set AllowPaging to true.

CurrentPageIndex

Int32 [ get, set ] - default value is 0. CurrentPageIndex represents index of current page. We usually increment or decrement this property in "Next page" and "Previous page" links.

DataSource

IEnumerable [ get, set ] - Source of the data, for example DataView. Classes that don't support indexing of records, like DataReader can't be used with PagedDataSource.

IsFirstPage

Boolean [ get ] - Returns true if current page is first page in data set. Otherwise, returns false.

IsLastPage

Boolean [ get ] - Determines is current page last page in data set.

IsPagingEnabled

Boolean [ get ] - Returns true if AllowPaging is true, otherwise false.

PageCount

Int32 [ get ] - PageCount returns total number of pages.

PageSize

Int32 [ get, set ] - default value is 10. PageSize determines how many records are shown per page.

For complete list of available PagedDataSource class members check MSDN.

Optmization problems when PagedDataSource is used

If you have large table, using of PagedDataSource can cause performance problems. Although PagedDataSource provides only one page to Repeater, DataList or some other control, in behind it works with all records. To show one page with 5 or 10 rows you need to open thousands or even millions of rows in memory. This is a waste of server resources and could lead to slow execution of ASP.NET application.

What is "too large" table for PagedDataSource depends of many factors, not just how much records you have. If you are on shared hosting with hundreds of sites and databases on same server, then even table with few thousands of records can cause PagedDataSource to work slowly. Sometimes, you can improve performances by using caching, but in reality most users don't browse more than few first pages and loading of thousands of pages in memory is often bad idea.

To create scalable paging solution that works as fast as possible, you need to load only what is necessary; in other words, you need to load only current page. You can achieve this with ad-hoc SQL queries or using stored procedures. Both methods are described in SQL Queries For Paging In ASP.NET and Paging With SQL Server Stored Procedures In ASP.NET tutorials.

Paging and SEO (Search Engine Optimization) issues

Be aware that search engine bots can't pass through links that use callbacks or any kind of JavaScript. So, if you use Button or LinkButton control for pager's navigation, search engines can't index your pages because they can't follow JavaScript requests and server side code. If optimization for search engines is not important for you (e.g. if you build intranet application), you'll be fine with buttons or link buttons.

Simple solution for search engines is to use HTML links for navigation and have page variables in query string, like in second example. Even better option is to employ some method of URL rewriting in ASP.NET to get nice pager links without query strings used.

Another problem with callback occurs if visitor tries to bookmark certain page. Since URL stays the same, visitor can bookmark only first page. If query strings or URL rewriting are used, every page has different URL and it's easy to add it to favorites.

PagedDataSource class remarks

PagedDataSource inherits ICollection, IEnumerable and ITypeList interfaces. That means that could work with data sources that could be counted or indexed, like DataView, List or HashTable, but it can't work with DataReader, SqlDataSource etc. Even DataTable can't be used directly, you need to bind PagedDataSource to DataView type, for example YourDataTable.DefaultView.

Going professional with SEO Pager control

PagedDataSource has few drawbacks. First is that it doesn't create any HTML output, so to create pager you must make all buttons, numbers, jump to specific page element, use CSS styling etc. This could demand significant time if your pager is anything more complex than just Previous, Next buttons. Another serious problem is bad performances if PagedDataSource works with large tables. Even if your site currently works well, you need to consider possible increase of data in the future or increase of visits if your site becomes popular.

To implement data paging fast, but in the same time scalable, nice looking, optimized for search engines and customizable, we created SEO Pager control. SEO Pager can work with PagedDataSource, but you can also easily switch to stored procedures if your site grows. Also, SEO pager can use callbacks, query strings or URL rewriting to better match your web site. Finally, SEO Pager enables different HTML output. There are predefined layouts to enable paging with numbers, tags, images, vertical paging, alphabetic paging etc. and also custom template that can be used to make pager in practically unlimited ways.

Happy coding!

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   Google