通過查詢結果進行分頁就是以結果集的子集處理查詢結果的過程，這樣，每次返回給用戶的只是當前頁面的數據大小。
DataAdapter對像通過重載Fill方法提供了返回當前頁面數據的功能。然而，這種方法對大數據量的查詢結果並不是最好的選擇，這是因為：當DataAdapter用請求的結果填充DataTable或者DataSet時，數據庫返回的資源仍是全部的查詢結果，只是在返回時附加了額外的限定條件才返回了少量的記錄集的。
要使用Fill方法返回當前一頁的記錄，需要指定開始記錄startRecord，和當前頁的最大記錄數maxRecords。
下面的例子用來返回一頁為5條記錄的第一頁的查詢結果：
[VB.NET]
<![CDATA[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#]
<![CDATA[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]
<![CDATA[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#]
<![CDATA[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，我們必須象下面那樣：
<![CDATA[[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填充之前進行了清空：
<![CDATA[[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#]
<![CDATA[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]
<![CDATA[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
]]>
留言列表
{{ article.title }}
