Paging Results with ASP.NET's PagedDataSource

A Real World Example

The example we covered isn't very good at showing the PagedDataSource in a practical sense, so now we'll go over using it with some data pulled from a database. For this example, we'll use the Microsoft Northwind SQL Server database.

To start with, we have to get the data from the database. We'll work with the customers table because it has the most records.

First off, we need to import the required namespaces:

<%@ Page Language="VB" %>    
<%@ Import Namespace="System.Data" %>    
<%@ Import Namespace="System.Data.SQLClient" %>

This allows us to access all the relevant classes and objects for accessing and working with data.

And, speaking of data, let's look at retrieving ours. For easier modularity, we're going to use a different sub-routine to retrieve our code:

Function getTheData() As DataTable    
  Dim DS As New DataSet()    
  Dim strConnect As New SQLConnection("server=localhost;    
  uid=sa;pwd=;Database=Northwind")    
  Dim objSQLAdapter As New SQLDataAdapter("SELECT    
  companyName, contactName, contactTitle FROM customers", strConnect)    
  objSQLAdapter.Fill(DS, "customers")    
   
  Return DS.Tables("customers").Copy    
End Function

We start the function, declaring that we're going to return a DataTable.

Function getTheData() As DataTable

With the first line of the function we create a new DataSet, which will store the data we'll use.

Dim DS As New DataSet()

Next, we define our connection to the Northwind database, and create our actual SQLDataAdapter to return the data.

  Dim strConnect As New SQLConnection("server=localhost;    
  uid=sa;pwd=;Database=Northwind")    
  Dim objSQLAdapter As New SQLDataAdapter("SELECT companyName,    
  contactName, contactTitle FROM customers", strConnect)

On the next line of the function, we fill the DataSet with data from the customers table and then, on the last line, we return a copy of the DataTable.

objSQLAdapter.Fill(DS, "customers")    
   
Return DS.Tables("customers").Copy

What this means is that we've separated the data retrieval from the actual paging functionality. Therefore, if we ever had to change the data, we could do it in this function -- without affecting the paging code!

The paging code will more or less be the same as before; the only change we have to make is to remove the code for the HashTable and access the function we just covered. So the new code will look like this:

Sub doPaging()    
  pagedData.DataSource = getTheData().DefaultView    
  pagedData.AllowPaging = True    
  pagedData.PageSize = 5    
   
  Try    
     pagedData.CurrentPageIndex = Int32.Parse    
     (Request.QueryString("Page")).ToString()    
  Catch ex As Exception    
     pagedData.CurrentPageIndex = 0    
  End Try    
   
  btnPrev.Visible = ( NOT pagedData.IsFirstPage )    
  btnNext.Visible = ( NOT pagedData.IsLastPage )    
   
  theDataList.DataSource = pagedData    
  theDataList.DataBind()    
End Sub

The line we edited is the one in bold, where, as you can see, we added a call to the data function. As the function returns a DataTable, we don't need to worry about anything else.