通过查询结果进行分页 (From derny)

原文链接

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

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