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

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

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

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

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

[VB.NET]

Dim currentIndex As Integer = 0Dim pageSize As Integer = 5Dim 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 = 5Dim 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 + pageSizemyDS.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(){// 初始化控件并添加到Formthis.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 SystemImports System.DataImports System.Data.SqlClientImports System.DrawingImports System.Windows.FormsPublic Class PagingSampleInherits 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 directionCase "Next"selCmd.CommandText = "SELECT TOP " & pageSize & " CustomerID, CompanyName FROM Customers " & _"WHERE CustomerID > @CustomerId ORDER BY CustomerID"selCmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = lastVisibleCustomerCase "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 = firstVisibleCustomerCase ElseselCmd.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 ThenSelect Case directionCase "Next"currentPage += 1Case "Previous"currentPage += -1Case ElsecurrentPage = 1End SelectpageLbl.Text = "Page " & currentPage & " of " & totalPages' Clear rows and add New results.custTable.Rows.Clear()Dim myRow As DataRowFor Each myRow In tmpTable.RowscustTable.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 IfEnd SubPublic 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 = truemyGrid.CaptionText = "NorthWind Customers"myGrid.ReadOnly = truemyGrid.AllowNavigation = falsemyGrid.PreferredColumnWidth = 150prevBtn.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 SubPublic Shared Sub Prev_OnClick(sender As Object, args As EventArgs)GetData("Previous")End SubPublic Shared Sub Next_OnClick(sender As Object, args As EventArgs)GetData("Next")End SubEnd ClassPublic Class SampleShared Sub Main()Application.Run(New PagingSample())End SubEnd Class