通過查詢結果進行分頁 (From derny)

原文連結

通過查詢結果進行分頁就是以結果集的子集處理查詢結果的過程，這樣，每次返回給使用者的只是當前頁面的資料大小。

DataAdapter對象通過重載Fill方法提供了返回當前頁面資料的功能。然而，這種方法對大資料量的查詢結果並不是最好的選擇，這是因為：當DataAdapter用請求的結果填充DataTable或者DataSet時，資料庫返回的資源仍是全部的查詢結果，只是在返回時附加了額外的限定條件才返回了少量的記錄集的。

要使用Fill方法返回當前一頁的記錄，需要指定開始記錄startRecord，和當前頁的最大記錄數maxRecords。

下面的例子用來返回一頁為5條記錄的第一頁的查詢結果：

[VB.NET]

Dim currentIndex As Integer = 0<br />Dim pageSize As Integer = 5</p><p>Dim orderSQL As String = "SELECT * FROM Orders ORDER BY OrderID"<br />Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)</p><p>Dim myDS As DataSet = New DataSet()<br />myDA.Fill(myDS, currentIndex, pageSize, "Orders")<br />

[C#]

int currentIndex = 0;<br />int pageSize = 5;</p><p>string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";<br />SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);</p><p>DataSet myDS = new DataSet();<br />myDA.Fill(myDS, currentIndex, pageSize, "Orders");<br />

在上面的例子中，DataSet只填充了5條記錄，但返回的仍是整個Orders表。如果要達到填充幾條返回幾天的目的，在SQL語句中使用TOP和WHERE從句即可。例如：

[VB.NET]

Dim pageSize As Integer = 5</p><p>Dim orderSQL As String = "SELECT TOP " &amp; pageSize &amp; " * FROM Orders ORDER BY OrderID"<br />Dim myDA As SqlDataAdapter = New SqlDataAdapter(orderSQL, nwindConn)</p><p>Dim myDS As DataSet = New DataSet()<br />myDA.Fill(myDS, "Orders")<br />

[C#]

int pageSize = 5;</p><p>string orderSQL = "SELECT TOP " + pageSize + " * FROM Orders ORDER BY OrderID";<br />SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn);</p><p>DataSet myDS = new DataSet();<br />myDA.Fill(myDS, "Orders");<br />

此時需要注意的是：用這種方法進行的分頁，必須自己維護記錄排序的唯一標識，為了向下一頁請求傳遞唯一的ID，我們必須象下面那樣：

[VB.NET]<br />Dim lastRecord As String = myDS.Tables("Orders").Rows(pageSize - 1)("OrderID").ToString()<br />[C#]<br />string lastRecord = myDS.Tables["Orders"].Rows[pageSize - 1]["OrderID"].ToString();<br />

下面的代碼在Table填充之前進行了清空：

[VB.NET]<br />currentIndex = currentIndex + pageSize</p><p>myDS.Tables("Orders").Rows.Clear()</p><p>myDA.Fill(myDS, currentIndex, pageSize, "Orders")<br />[C#]<br />currentIndex += pageSize;</p><p>myDS.Tables["Orders"].Rows.Clear();</p><p>myDA.Fill(myDS, currentIndex, pageSize, "Orders");<br />

下面是完整的代碼：

[C#]

using System;<br />using System.Data;<br />using System.Data.SqlClient;<br />using System.Drawing;<br />using System.Windows.Forms;</p><p>public class PagingSample: Form<br />{<br /> // Form 控制項.<br /> Button prevBtn = new Button();<br /> Button nextBtn = new Button();</p><p> static DataGrid myGrid = new DataGrid();<br /> static Label pageLbl = new Label();</p><p> // 分頁變數<br /> static int pageSize = 10; // 要顯示的頁數<br /> static int totalPages = 0; // 總頁數<br /> static int currentPage = 0; // 當前頁<br /> static string firstVisibleCustomer = ""; // 當前頁的第一條記錄，用來進行移動“前一頁”的定位。<br /> static string lastVisibleCustomer = ""; //當前頁的最後條記錄，用來進行移動“下一頁”的定位。 </p><p> // DataSet用來綁定到DataGrid.<br /> static DataTable custTable;</p><p> //初始化串連和DataAdapter.<br /> static SqlConnection nwindConn = new SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind");<br /> static SqlDataAdapter custDA = new SqlDataAdapter("", nwindConn);<br /> static SqlCommand selCmd = custDA.SelectCommand;</p><p> public static void GetData(string direction)<br /> {<br /> // 建立返回一頁記錄的SQL語句<br /> selCmd.Parameters.Clear();</p><p> switch (direction)<br /> {<br /> case "下一頁":<br /> selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +<br /> "WHERE CustomerID &gt; @CustomerId ORDER BY CustomerID";<br /> selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer;<br /> break;<br /> case "前一頁":<br /> selCmd.CommandText = "SELECT TOP " + pageSize + " CustomerID, CompanyName FROM Customers " +<br /> "WHERE CustomerID 0)<br /> {<br /> switch (direction)<br /> {<br /> case "下一頁":<br /> currentPage++;<br /> break;<br /> case "上一頁":<br /> currentPage--;<br /> break;<br /> default:<br /> currentPage = 1;<br /> break;<br /> }</p><p> pageLbl.Text = "第" + currentPage + "/ " + totalPages + "頁";</p><p> // 清除行集，添加新記錄<br /> custTable.Rows.Clear();</p><p> foreach (DataRow myRow in tmpTable.Rows)<br /> custTable.ImportRow(myRow);</p><p> // 儲存first 和 last 關索引值<br /> DataRow[] ordRows = custTable.Select("", "CustomerID ASC");<br /> firstVisibleCustomer = ordRows[0][0].ToString();<br /> lastVisibleCustomer = ordRows[custTable.Rows.Count - 1][0].ToString();<br /> }<br /> }</p><p> public PagingSample()<br /> {<br /> // 初始化控制項並添加到Form<br /> this.ClientSize = new Size(360, 274);<br /> this.Text = "NorthWind 資料表";</p><p> myGrid.Location = new Point(10,10);<br /> myGrid.Size = new Size(340, 220);<br /> myGrid.AllowSorting = true;<br /> myGrid.CaptionText = "NorthWind 客戶資訊";<br /> myGrid.ReadOnly = true;<br /> myGrid.AllowNavigation = false;<br /> myGrid.PreferredColumnWidth = 150;</p><p> prevBtn.Text = "前一頁";<br /> prevBtn.Size = new Size(60, 24);<br /> prevBtn.Location = new Point(50, 240);<br /> prevBtn.Click += new EventHandler(Prev_OnClick);</p><p> nextBtn.Text = "下一頁";<br /> nextBtn.Size = new Size(60, 24);<br /> nextBtn.Location = new Point(120, 240);</p><p> pageLbl.Text = "沒有記錄返回";<br /> pageLbl.Size = new Size(130, 16);<br /> pageLbl.Location = new Point(200, 244);</p><p> this.Controls.Add(myGrid);<br /> this.Controls.Add(prevBtn);<br /> this.Controls.Add(nextBtn);<br /> this.Controls.Add(pageLbl);<br /> nextBtn.Click += new EventHandler(Next_OnClick);</p><p> // 計算預設的第一頁，並進行綁定<br /> GetData("Default");<br /> DataView custDV = new DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows);<br /> myGrid.SetDataBinding(custDV, "");<br /> }</p><p> public static void Prev_OnClick(object sender, EventArgs args)<br /> {<br /> GetData("前一頁");<br /> }</p><p> public static void Next_OnClick(object sender, EventArgs args)<br /> {<br /> GetData("下一頁");<br /> }<br />}</p><p>public class Sample<br />{<br /> static void Main()<br /> {<br /> Application.Run(new PagingSample());<br /> }<br />}<br />

[VB.NET]

Imports System<br />Imports System.Data<br />Imports System.Data.SqlClient<br />Imports System.Drawing<br />Imports System.Windows.Forms</p><p>Public Class PagingSample<br /> Inherits Form</p><p> ' Form controls.<br /> Dim prevBtn As Button = New Button()<br /> Dim nextBtn As Button = New Button()</p><p> Shared myGrid As DataGrid = New DataGrid()<br /> Shared pageLbl As Label = New Label()</p><p> ' Paging variables.<br /> Shared pageSize As Integer = 10 ' Size of viewed page.<br /> Shared totalPages As Integer = 0 ' Total pages.<br /> Shared currentPage As Integer = 0 ' Current page.<br /> Shared firstVisibleCustomer As String = "" ' First customer on page to determine location for move previous.<br /> Shared lastVisibleCustomer As String = "" ' Last customer on page to determine location for move next.</p><p> ' DataSet to bind to DataGrid.<br /> Shared custTable As DataTable</p><p> ' Initialize connection to database and DataAdapter.<br /> Shared nwindConn As SqlConnection = New SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=northwind")<br /> Shared custDA As SqlDataAdapter = New SqlDataAdapter("", nwindConn)<br /> Shared selCmd As SqlCommand = custDA.SelectCommand()</p><p> Public Shared Sub GetData(direction As String)</p><p> ' Create SQL statement to return a page of records.<br /> selCmd.Parameters.Clear()</p><p> Select Case direction<br /> Case "Next"<br /> selCmd.CommandText = "SELECT TOP " &amp; pageSize &amp; " CustomerID, CompanyName FROM Customers " &amp; _<br /> "WHERE CustomerID &gt; @CustomerId ORDER BY CustomerID"<br /> selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomer<br /> Case "Previous"<br /> selCmd.CommandText = "SELECT TOP " &amp; pageSize &amp; " CustomerID, CompanyName FROM Customers " &amp; _<br /> "WHERE CustomerID 0 Then<br /> Select Case direction<br /> Case "Next"<br /> currentPage += 1<br /> Case "Previous"<br /> currentPage += -1<br /> Case Else<br /> currentPage = 1<br /> End Select</p><p> pageLbl.Text = "Page " &amp; currentPage &amp; " of " &amp; totalPages</p><p> ' Clear rows and add New results.<br /> custTable.Rows.Clear()</p><p> Dim myRow As DataRow<br /> For Each myRow In tmpTable.Rows<br /> custTable.ImportRow(myRow)<br /> Next</p><p> ' Preserve first and last primary key values.<br /> Dim ordRows() As DataRow = custTable.Select("", "CustomerID ASC")<br /> firstVisibleCustomer = ordRows(0)(0).ToString()<br /> lastVisibleCustomer = ordRows(custTable.Rows.Count - 1)(0).ToString()<br /> End If<br /> End Sub</p><p> Public Sub New()<br /> MyBase.New</p><p> ' Initialize controls and add to form.<br /> Me.ClientSize = New Size(360, 274)<br /> Me.Text = "NorthWind Data"</p><p> myGrid.Location = New Point(10,10)<br /> myGrid.Size = New Size(340, 220)<br /> myGrid.AllowSorting = true<br /> myGrid.CaptionText = "NorthWind Customers"<br /> myGrid.ReadOnly = true<br /> myGrid.AllowNavigation = false<br /> myGrid.PreferredColumnWidth = 150</p><p> prevBtn.Text = "&gt;"<br /> nextBtn.Size = New Size(48, 24)<br /> nextBtn.Location = New Point(160, 240)</p><p> pageLbl.Text = "No Records Returned."<br /> pageLbl.Size = New Size(130, 16)<br /> pageLbl.Location = New Point(218, 244)</p><p> Me.Controls.Add(myGrid)<br /> Me.Controls.Add(prevBtn)<br /> Me.Controls.Add(nextBtn)<br /> Me.Controls.Add(pageLbl)<br /> AddHandler nextBtn.Click, New EventHandler(AddressOf Next_OnClick)</p><p> ' Populate DataSet with first page of records and bind to grid.<br /> GetData("Default")<br /> Dim custDV As DataView = New DataView(custTable, "", "CustomerID", DataViewRowState.CurrentRows)<br /> myGrid.SetDataBinding(custDV, "")<br /> End Sub</p><p> Public Shared Sub Prev_OnClick(sender As Object, args As EventArgs)<br /> GetData("Previous")<br /> End Sub</p><p> Public Shared Sub Next_OnClick(sender As Object, args As EventArgs)<br /> GetData("Next")<br /> End Sub<br />End Class</p><p>Public Class Sample<br /> Shared Sub Main()<br /> Application.Run(New PagingSample())<br /> End Sub<br />End Class<br />