通过查询结果进行分页

作者：孟宪会 出自：【孟宪会之精彩世界】 发布日期：2003年7月7日 18点57分19秒

---

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

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