通过查询结果进行分页就是以结果集的子集处理查询结果的过程，这样，每次返回给用户的只是当前页面的数据大小。

　　DataAdapter对象通过重载Fill方法提供了返回当前页面数据的功能。然而，这种方法对大数据量的查询结果并不是最好的选择，这是因为：当DataAdapter用请求的结果填充DataTable或者DataSet时，数据库返回的资源仍是全部的查询结果，只是在返回时附加了额外的限定条件才返回了少量的记录集的。

　　要使用Fill方法返回当前一页的记录，需要指定开始记录startRecord，和当前页的最大记录数maxRecords。

　　下面的例子用来返回一页为5条记录的第一页的查询结果：

　[VB.NET]
　
　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")
　[C#]
　
　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表。如果要达到填充几条返回几天的目的，在SQL语句中使用TOP和WHERE从句即可。例如：
　
　[VB.NET]
　
　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")
　[C#]
　
　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，我们必须象下面那样：
　
　[VB.NET]
　Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()
　[C#]
　string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();
　下面的代码在Table填充之前进行了清空：
　
　[VB.NET]
　currentIndex = currentIndex + pageSize
　
　myDS.Tables("Orders").Rows.Clear()
　
　myDA.Fill(myDS, currentIndex, pageSize, "Orders")
　[C#]
　currentIndex += pageSize;
　
　myDS.Tables["Orders"].Rows.Clear();
　
　myDA.Fill(myDS, currentIndex, pageSize, "Orders");
　下面是完整的代码：
　
　[C#]
　
　using System;
　using System.Data;
　using System.Data.SqlClient;
　using System.Drawing;
　using System.Windows.Forms;
　
　public class PagingSample: Form
　{
　 // Form 控件.
　 Button prevBtn = new Button();
　 Button nextBtn = new Button();
　
　 static DataGrid myGrid = new DataGrid();
　 static Label pageLbl = new Label();
　
　 // 分页变量
　 static int pageSize = 10; // 要显示的页数
　 static int totalPages = 0; // 总页数
　 static int currentPage = 0; // 当前页
　 static string firstVisibleCustomer = ""; // 当前页的第一条记录，用来进行移动“前一页”的定位。
　 static string lastVisibleCustomer = ""; //当前页的最后条记录，用来进行移动“下一页”的定位。
　
　 // DataSet用来绑定到DataGrid.
　 static DataTable custTable;
　
　 //初始化连接和DataAdapter.
　 static SqlConnection nwindConn = new SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind");
　 static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);
　 static SqlCommand selCmd = custDA.SelectCommand;
public static void GetData(string direction)
　 {
　 // 创建返回一页记录的SQL语句
　 selCmd.Parameters.Clear();
　
　 switch (direction)
　 {
　 case "下一页":
　 selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
　 "WHERE CustomerID > @CustomerId ORDER BY CustomerID";
　 selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;
　 break;
　 case "前一页":
　 selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +
　 "WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC";
　 selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer;
　 break;
　 default:
　 selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers ORDER BY CustomerID";
　
　 // 计算总页数
　 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;
　 }
　
　 // 用查询结果填充临时表
　 DataTable tmpTable = new DataTable("Customers");
　 int recordsAffected = custDA.Fill(tmpTable);
　
　 // 如果表不存在，就创建
　 if (custTable == null)
　 custTable = tmpTable.Clone();
　
　 // 如果有记录返回，就刷新表
　 if (recordsAffected > 0)
　 {
　 switch (direction)
　 {
　 case "下一页":
　 currentPage++;
　 break;
　 case "上一页":
　 currentPage--;
　 break;
　 default:
　 currentPage = 1;
　 break;
　 }
　
　 pageLbl.Text = "第" + currentPage + "/ " + totalPages + "页";
　
　 // 清除行集，添加新记录
　 custTable.Rows.Clear();
　
　 foreach (DataRow myRow in tmpTable.Rows)
　 custTable.ImportRow(myRow);
　
　 // 保存first 和 last 关键值
　 DataRow[] ordRows = custTable.Select("", "CustomerID ASC");
　 firstVisibleCustomer = ordRows[0][0].ToString();
　 lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();
　 }
　 }
　　
　 public PagingSample()
　 {
　 // 初始化控件并添加到Form
　 this.ClientSize = new Size(360, 274);
　 this.Text = "NorthWind 数据表";
　
　 myGrid.Location = new Point(10,10);
　 myGrid.Size = new Size(340, 220);
　 myGrid.AllowSorting = true;
　 myGrid.CaptionText = "NorthWind 客户信息";
　 myGrid.ReadOnly = true;
　 myGrid.AllowNavigation = false;
　 myGrid.PreferredColumnWidth = 150;
　
　 prevBtn.Text = "前一页";
　 prevBtn.Size = new Size(60, 24);
　 prevBtn.Location = new Point(50, 240);
　 prevBtn.Click += new EventHandler(Prev_OnClick);
　
　 nextBtn.Text = "下一页";
　 nextBtn.Size = new Size(60, 24);
　 nextBtn.Location = new Point(120, 240);
　
　 pageLbl.Text = "没有记录返回";
　 pageLbl.Size = new Size(130, 16);
　 pageLbl.Location = new Point(200, 244);
　
　 this.Controls.Add(myGrid);
　 this.Controls.Add(prevBtn);
　 this.Controls.Add(nextBtn);
　 this.Controls.Add(pageLbl);
　 nextBtn.Click += new EventHandler(Next_OnClick);
　
　
　 // 计算默认的第一页，并进行绑定
　 GetData("Default");
　 DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);
　 myGrid.SetDataBinding(custDV, "");
　 }
　　
　 public static void Prev_OnClick(object sender, EventArgs args)
　 {
　 GetData("前一页");
　 }
　
　 public static void Next_OnClick(object sender, EventArgs args)
　 {
　 GetData("下一页");
　 }
　}
public class Sample
　{
　 static void Main()
　 {
　 Application.Run(new PagingSample());
　 }
　}
　[VB.NET]
　
　Imports System
　Imports System.Data
　Imports System.Data.SqlClient
　Imports System.Drawing
　Imports System.Windows.Forms
　
　Public Class PagingSample
　 Inherits Form
　
　 ' 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()
　
　 ' Paging variables.
　 Shared pageSize As Integer = 10 ' Size of viewed page.
　 Shared totalPages As Integer = 0 ' Total pages.
　 Shared currentPage As Integer = 0 ' Current page.
　 Shared firstVisibleCustomer As String = "" ' First customer on page to determine location for move previous.
　 Shared lastVisibleCustomer As String = "" ' Last customer on page to determine location for move next.
　
　 ' DataSet to bind to DataGrid.
　 Shared custTable As DataTable
　
　 ' Initialize connection to database and DataAdapter.
　 Shared nwindConn As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind")
　 Shared custDA As SqlDataAdapter = New SqlDataAdapter("", nwindConn)
　 Shared selCmd As SqlCommand = custDA.SelectCommand()
　
　
　 Public Shared Sub GetData(direction As String)
　
　 ' Create SQL statement to return a page of records.
　 selCmd.Parameters.Clear()
　
　 Select Case direction
　 Case "Next"
　 selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
　 "WHERE CustomerID > @CustomerId ORDER BY CustomerID"
　 selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer
　 Case "Previous"
　 selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _
　 "WHERE CustomerID < @CustomerId ORDER BY CustomerID DESC"
　 selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = firstVisibleCustomer
　 Case Else
　 selCmd.CommandText = "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.
　 Dim tmpTable As DataTable = New DataTable("Customers")
　 Dim recordsAffected As Integer = custDA.Fill(tmpTable)
　
　 ' If table does not exist, create it.
　 If custTable Is Nothing Then custTable = tmpTable.Clone()
　
　 ' Refresh table if at least one record returned.
　 If recordsAffected > 0 Then
　 Select Case direction
　 Case "Next"
　 currentPage += 1
　 Case "Previous"
　 currentPage += -1
　 Case Else
　 currentPage = 1
　 End Select
　
　 pageLbl.Text = "Page " & currentPage & " of " & totalPages
　
　 ' Clear rows and add New results.
　 custTable.Rows.Clear()
　
　 Dim myRow As DataRow
　 For Each myRow In tmpTable.Rows
　 custTable.ImportRow(myRow)
　 Next
　
　 ' Preserve 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 controls and add to form.
　 Me.ClientSize = New Size(360, 274)
　 Me.Text = "NorthWind Data"
　
　 myGrid.Location = New Point(10,10)
　 myGrid.Size = New Size(340, 220)
　 myGrid.AllowSorting = true
　 myGrid.CaptionText = "NorthWind Customers"
　 myGrid.ReadOnly = true
　 myGrid.AllowNavigation = false
　 myGrid.PreferredColumnWidth = 150
　
　 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 DataSet with first page of records and bind to 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
　
　
　Public Class Sample
　 Shared Sub Main()
　 Application.Run(New PagingSample())
　 End Sub
　End Class