查询结果分页是以较小数据子集（即页）的形式返回查询结果的过程。它通常用于以易于管理的小块区的形式向用户显示结果。

DataAdapter 提供了通过 Fill 方法的重载来仅返回一页数据的功能。但是，对于大量的查询结果，它可能并不是首选的分页方法，因为 DataAdapter 虽然仅使用所请求的记录来填充目标 DataTable 或 DataSet，但仍会使用返回整个查询的资源。若要从数据页中返回一页数据，但不使用返回整个查询所需的资源，请为查询指定附加的条件，这些条件用于将返回的行减少到所需的行。

若要使用 Fill 方法返回一页数据，请指定 startRecord（它指定该数据页的第一个记录），并指定 maxRecords（它指定该数据页中的记录数）。

以下代码示例显示如何使用 Fill 方法来返回查询结果（页大小为 5 个记录）的第一页。

Dim currentIndex As Integer = 0
Dim pageSize As Integer = 5

Dim orderSQL As String = "SELECT * FROM Orders ORDER BY OrderID"
Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)

Dim myDS As DataSet = New DataSet()
myDA.Fill(myDS, currentIndex, pageSize, "Orders")
int currentIndex = 0;
int pageSize = 5;

string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);

DataSet myDS = new DataSet();
myDA.Fill(myDS, currentIndex, pageSize, "Orders");

在上例中，DataSet 只填充了 5 个记录，但却返回了整个 Orders 表。若要用相同的 5 个记录填充 DataSet 但仅返回这 5 个记录，请在 SQL 语句中使用 TOP 和 WHERE 语句，如以下代码示例所示。

Dim pageSize As Integer = 5

Dim orderSQL As String = "SELECT TOP " & pageSize & " * FROM Orders ORDER BY OrderID"
Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)

Dim myDS As DataSet = New DataSet()
myDA.Fill(myDS, "Orders")
int pageSize = 5;

string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID";
SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);

DataSet myDS = new DataSet();
myDA.Fill(myDS, "Orders");

请注意，当以这种方式进行查询结果分页时，将需要保留用作行排序依据的唯一标识符，以便将唯一的 ID 传递给用于返回下一页记录的命令，如以下代码示例所示。

Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();

若要使用采用 startRecord 和 maxRecords 参数的 Fill 方法来返回下一页记录，请使当前记录索引按页大小递增，并填充该表。请记住，即使仅在 DataSet 中添加一页记录，数据库服务器仍会返回全部查询结果。在以下代码示例中，先清除表行，然后再用下一页数据填充这些表行。您可能需要在本地缓存中保留一定数量的返回行，以减少到数据库服务器的往返行程。 

currentIndex = currentIndex + pageSize

myDS.Tables("Orders").Rows.Clear()

myDA.Fill(myDS, currentIndex, pageSize, "Orders")

若要返回下一页记录而不让数据库服务器返回整个查询，请指定对 SQL SELECT 语句的限制条件。由于上例保留了返回的最后一个记录，因此可以在 WHERE 子句中使用它来指定查询的起点，如以下代码示例所示。

orderSQL = "SELECT TOP " & pageSize & " * FROM Orders WHERE OrderID > " & lastRecord & " ORDER BY OrderID"
myDA.SelectCommand.CommandText = orderSQL

myDS.Tables("Orders").Rows.Clear()

myDA.Fill(myDS, "Orders")
orderSQL = "SELECT TOP " + pageSize + " * FROM Orders WHERE OrderID > " + lastRecord + " ORDER BY OrderID";
myDA.SelectCommand.CommandText = orderSQL;

myDS.Tables["Orders"].Rows.Clear();

myDA.Fill(myDS, "Orders");		 
下面是一个查询结果分页的示例，它在 SQL 语句中指定特定条件，以便一次仅从数据库中返回一页记录。
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

Public Class PagingSample
  Inherits Form

  ' Initialize a connection to the database and DataAdapter.
  Shared nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
  Shared custDA As SqlDataAdapter = New SqlDataAdapter("", nwindConn)


  ' Include form controls.
  Dim prevBtn As Button = New Button()
  Dim nextBtn As Button = New Button()

  Shared myGrid As DataGrid = New DataGrid()
  Shared pageLbl As Label = New Label()

  ' Include paging variables.
  Shared pageSize As Integer = 10             ' Size of the viewed page.
  Shared totalPages As Integer = 0            ' Total pages.
  Shared currentPage As Integer = 0           ' Current page.
  Shared firstVisibleCustomer As String = ""  ' First customer on the page to determine the location for move previous.
  Shared lastVisibleCustomer As String = ""   ' Last customer on the page to determine the location for move next.

  ' Create a DataSet to bind to a DataGrid.
  Shared custTable As DataTable


  Public Shared Sub GetData(direction As String)

    ' Create a SQL statement to return a page of records.
    Dim myCommand As String = ""

    Select Case direction
      Case "Next"
        myCommand = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
          "WHERE CustomerID > '" & lastVisibleCustomer & "' ORDER BY CustomerID"
      Case "Previous"
        myCommand = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
          "WHERE CustomerID < '" & firstVisibleCustomer & "' ORDER BY CustomerID DESC"
      Case Else
        myCommand = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers ORDER BY CustomerID"
       
        ' Determine total pages.
        Dim totCMD As SqlCommand = New SqlCommand("SELECT Count(*) FROM Customers", nwindConn)
        nwindConn.Open()
        Dim totalRecords As Integer = CInt(totCMD.ExecuteScalar())
        nwindConn.Close()
        totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize))
    End Select

    ' Fill a temporary table with query results.
    custDA.SelectCommand.CommandText = myCommand
    Dim tmpTable As DataTable = New DataTable("Customers")
    Dim recordsAffected As Integer = custDA.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 direction
        Case "Next"
          currentPage = currentPage + 1
        Case "Previous"
          currentPage = currentPage - 1
        Case Else
          currentPage = 1
      End Select

      pageLbl.Text = "Page " & currentPage & " of " & totalPages

      ' Clear the rows and add new results.
      custTable.Rows.Clear()

      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


  Public Sub New()
    MyBase.New

    ' Initialize the controls and add them to the form.
    Me.ClientSize = New Size(360, 274)
    Me.Text = "NorthWind Data"

    myGrid.Location = New Point(10,10)
    myGrid.Size = New Size(340, 220)
    myGrid.PreferredColumnWidth = DataGrid.AutoColumnSize
    myGrid.AllowSorting = true
    myGrid.CaptionText = "NorthWind Customers"
    myGrid.ReadOnly = true
    myGrid.AllowNavigation = false

    prevBtn.Text = "<<"
    prevBtn.Size = New Size(48, 24)
    prevBtn.Location = New Point(92, 240)
    AddHandler prevBtn.Click, New EventHandler(AddressOf Prev_OnClick)

    nextBtn.Text = ">>"
    nextBtn.Size = New Size(48, 24)
    nextBtn.Location = New Point(160, 240)

    pageLbl.Text = "No Records Returned."
    pageLbl.Size = New Size(130, 16)
    pageLbl.Location = New Point(218, 244)

    Me.Controls.Add(myGrid)
    Me.Controls.Add(prevBtn)
    Me.Controls.Add(nextBtn)
    Me.Controls.Add(pageLbl)
    AddHandler nextBtn.Click, New EventHandler(AddressOf Next_OnClick)


    ' Populate the DataSet with the first page of records and bind them to the grid.
    GetData("Default")
    Dim custDV As DataView = New DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows)
    myGrid.SetDataBinding(custDV, "")
  End Sub


  Public Shared Sub Prev_OnClick(sender As Object, args As EventArgs)
    GetData("Previous")
  End Sub

  Public Shared Sub Next_OnClick(sender As Object, args As EventArgs)
    GetData("Next")
  End Sub
End Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

public class PagingSample: Form
{
  // Initialize a connection to the database and DataAdapter.
  static SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
  static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);

  // Include form controls.
  Button prevBtn = new Button();
  Button nextBtn = new Button();

  static DataGrid myGrid = new DataGrid();
  static Label pageLbl = new Label();

  // Include paging variables.
  static int pageSize = 10;                 // Size of viewed page.
  static int totalPages = 0;                // Total pages.
  static int currentPage = 0;               // Current page.
  static string firstVisibleCustomer = "";  // First customer on page to determine location for move previous.
  static string lastVisibleCustomer = "";   // Last customer on page to determine location for move next.

  // Create a DataSet to bind to a DataGrid.
  static DataTable custTable;


  public static void GetData(string direction)
  {
    // Create a SQL statement to return a page of records.
    string myCommand = "";

    switch (direction)
    {
      case "Next":
        myCommand = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
          "WHERE CustomerID > '" + lastVisibleCustomer + "' ORDER BY CustomerID";
        break;
      case "Previous":
        myCommand = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
          "WHERE CustomerID < '" + firstVisibleCustomer + "' ORDER BY CustomerID DESC";
        break;
      default:
        myCommand = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
       
        // Determine total pages.
        SqlCommand totCMD = new SqlCommand("SELECT Count(*) FROM Customers", nwindConn);
        nwindConn.Open();
        int totalRecords = (int)totCMD.ExecuteScalar();
        nwindConn.Close();
        totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

        break;
    }

    // Fill a temporary table with query results.
    custDA.SelectCommand.CommandText = myCommand;
    DataTable tmpTable = new DataTable("Customers");
    int recordsAffected = custDA.Fill(tmpTable);

    // If the table does not exist, create it.
    if (custTable == null)
      custTable = tmpTable.Clone();

    // Refresh the table if at least one record is returned.
    if (recordsAffected > 0)
    {
      switch (direction)
      {
        case "Next":
          currentPage++;
          break;
        case "Previous":
          currentPage--;
          break;
        default:
          currentPage = 1;
          break;
      }

      pageLbl.Text = "Page " + currentPage + " of " + totalPages;

      // Clear the rows and add new results.
      custTable.Rows.Clear();

      foreach (DataRow myRow in tmpTable.Rows)
        custTable.ImportRow(myRow);

      // Preserve the first and last primary key values.
      DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
      firstVisibleCustomer = ordRows[0][0].ToString();
      lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
    }
  }



  public PagingSample()
  {
    // Initialize the controls and add them to the form.
    this.ClientSize = new Size(360, 274);
    this.Text = "NorthWind Data";

    myGrid.Location = new Point(10,10);
    myGrid.Size = new Size(340, 220);
    myGrid.PreferredColumnWidth = DataGrid.AutoColumnSize;
    myGrid.AllowSorting = true;
    myGrid.CaptionText = "NorthWind Customers";
    myGrid.ReadOnly = true;
    myGrid.AllowNavigation = false;

    prevBtn.Text = "<<";
    prevBtn.Size = new Size(48, 24);
    prevBtn.Location = new Point(92, 240);
    prevBtn.Click += new EventHandler(Prev_OnClick);

    nextBtn.Text = ">>";
    nextBtn.Size = new Size(48, 24);
    nextBtn.Location = new Point(160, 240);

    pageLbl.Text = "No Records Returned.";
    pageLbl.Size = new Size(130, 16);
    pageLbl.Location = new Point(218, 244);

    this.Controls.Add(myGrid);
    this.Controls.Add(prevBtn);
    this.Controls.Add(nextBtn);
    this.Controls.Add(pageLbl);
    nextBtn.Click += new EventHandler(Next_OnClick);


    // Populate the DataSet with the first page of records and bind them to the grid.
    GetData("Default");
    DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
    myGrid.SetDataBinding(custDV, "");
  }


  public static void Prev_OnClick(object sender, EventArgs args)
  {
    GetData("Previous");
  }

  public static void Next_OnClick(object sender, EventArgs args)
  {
    GetData("Next");
  }
}



public class Sample
{
  static void Main()
  {
    Application.Run(new PagingSample());
  }
}