Paging is a processing of fetching a number of rows as a page (a subset of data) instead of all rows from a data source. Paging can be achieved in two ways in ADO.NET:
- Using DataAdapter
- Using SQL Statement
Paging using DataAdapter
The Fill method of DataAdapter provides an overloaded form where you can ask DataAdapter to returns only a selected number of rows in a DataSet. The overloaded form of the Fill method is listed as following:
Public Overloads Function Fill(ByVal dataSet As DataSet, ByVal startRecord As Integer, ByVal maxRecords As Integer, ByVal srcTable As String) As Integer
WHERE dataSet is a DataSet to fill with records and, if necessary, schema, startRecord is the zero-based record number to start with, maxRecords is the maximum number of records to retrieve, and srcTable is the name of the source table to use for table mapping. Listing 22-3 shows a function, which returns a DataSet filled with the records based on the page size passed in the method.
Function GetPagedData(ByVal da As SqlDataAdapter, ByVal idx As Integer, ByVal size As Integer) As DataSet
Dim ds As DataSet = New DataSet
Try
da.Fill(ds, idx, size, "Orders")
Catch e As Exception
MessageBox.Show(e.Message.ToString())
End Try
Return ds
End Function
Listing 22-3 GetPageData method
Paging using SELECT TOP SQL Statement
Besides the Fill method of DataAdapter, you can even use a SELECT SQL statement to retrieve number of records from a table. The SELECT TOP statement is used for this purpose. The following statement select top 10 records from Cutomers table. You set the SELECT statement as SELECT TOP statement. We'll use the same method in the following sample.
"SELECT TOP 10 CustomerID, CompanyName, " & _
" ContactName FROM Customers ORDER BY CustomerID"
Implementing Paging
OK, now let's see how to use both above discussed methods to implement paging in ADO.NET. Our application looks like Figure 22-1. In this Figure, you can enter number of rows you want to load in a page. Load Page button loads the first page. Previous Page and Next Page buttons loads next pages from database. When the SELECT TOP check box is checked, program uses the SELECT TOP method; otherwise it uses DataAdapter's Fill method.
Figure 22-1. Paging in ADO.NET application
The code listed in Listing 22-4 implements paging using both methods. As you can see from this code, the Load Page button click event handler reads the page size, creates and opens a new connection, and checks if SELECT TOP CheckBox is checked, it calls GetTopData method, otherwise it calls GetPagedData method.
The GetTopData method uses the SELECT TOP SQL query to get top records, creates a DataTable custTable and reads data in this table from the main DataTable tmpTable, that has all records from the database table.
Now if you see the Previous Page and Next Page button click handlers, you'll see its just a matter of calling GetTopData and GetPagedData with the current record number and number of records to be fetched.
Private Sub LoadPageBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadPageBtn.Click
pageSize = Convert.ToInt32(TextBox1.Text.ToString())
' Create and open a connection
conn = New SqlConnection(connectionString)
conn.Open()
' Find out total number of records
Dim cmd As SqlCommand = New SqlCommand
' Assign the SQL Insert statement we want to execute to the CommandText
cmd.CommandText = "SELECT Count(*) FROM Customers"
cmd.Connection = conn
' Call ExecuteNonQuery on the Command Object to execute insert
totalRecords = cmd.ExecuteScalar()
' Create data adapter
sql = "SELECT CustomerID, CompanyName, ContactName " & _
"FROM Customers ORDER BY CustomerID"
adapter = New SqlDataAdapter(sql, conn)
ds = New DataSet
' If SELECT TOP check box is checked
If CheckBox1.Checked Then
selectTop = True
Else
selectTop = False
End If
' if SELECT TOP is checked
If selectTop Then
GetTopData("", 0)
DataGrid1.DataSource = custTable
Else
ds = GetPagedData(adapter, curIndex, pageSize)
curIndex = curIndex + pageSize
DataGrid1.DataSource = ds.DefaultViewManager
End If
End Sub
' Get a page using SELECT TOP statement
Public Shared Sub GetTopData(ByVal selectCmd As String, ByVal type As Integer)
' First time load first TOP pages
If (selectCmd.Equals(String.Empty)) Then
selectCmd = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _
" ContactName FROM Customers ORDER BY CustomerID"
End If
totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize))
adapter.SelectCommand.CommandText = selectCmd
Dim tmpTable As DataTable = New DataTable("Customers")
Dim recordsAffected As Integer = adapter.Fill(tmpTable)
' If the table does not exist, create it.
If custTable Is Nothing Then custTable = tmpTable.Clone()
' Refresh the table if at least one record is returned.
If recordsAffected > 0 Then
Select Case type
Case 1
currentPage = currentPage + 1
Case 2
currentPage = currentPage - 1
Case Else
currentPage = 1
End Select
' Clear the rows and add new results.
custTable.Rows.Clear()
' Import rows from temp tabke to custTable
Dim myRow As DataRow
For Each myRow In tmpTable.Rows
custTable.ImportRow(myRow)
Next
' Preserve the first and last primary key values.
Dim ordRows() As DataRow = custTable.Select("", "CustomerID ASC")
firstVisibleCustomer = ordRows(0)(0).ToString()
lastVisibleCustomer = ordRows(custTable.Rows.Count - 1)(0).ToString()
End If
End Sub
' Previous page button click
Private Sub PrePageBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrePageBtn.Click
' if SELECT TOP is checked
If selectTop Then
sql = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _
" ContactName FROM Customers " & _
"WHERE CustomerID < '" & firstVisibleCustomer & "' ORDER BY CustomerID"
GetTopData(sql, 1)
Else
ds = GetPagedData(adapter, curIndex, pageSize)
curIndex = curIndex - pageSize
DataGrid1.DataSource = ds.DefaultViewManager
End If
End Sub
' Next page button click
Private Sub NextPageBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextPageBtn.Click
' if SELECT TOP is checked
If selectTop Then
sql = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _
" ContactName FROM Customers " & _
"WHERE CustomerID > '" & lastVisibleCustomer & "' ORDER BY CustomerID"
GetTopData(sql, 2)
Else
ds = GetPagedData(adapter, curIndex, pageSize)
curIndex = curIndex + pageSize
DataGrid1.DataSource = ds.DefaultViewManager
End If
End Sub
Listing 22-4. Implementing paging in ADO.NET
See the attached source code sample for more details.
Free Downloads: