通过查询结果进行分页
添加时间: 2007-7-11 3:53:01 作者: C#教程 阅读次数:40 来源: http://www.d9soft.com
通过查询结果进行分页就是以结果集的子集处理查询结果的过程，这样，每次返回给用户的只是当前页面的数据大小。
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.<a href="http://dev.21tx.com/os/windows/" target="_blank">Windows</a>.Forms;
public class PagingSample: Form
{
// Form 控件.
Button prevBtn = new Button();
Button nextBtn = new Button();
static <a href="http://dev.21tx.com/dotnet/aspnet/datagrid/" target="_blank">DataGrid</a> 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
上一篇文章: 在C#中实现Ping 下一篇文章: 百万程序员的苦恼－选择VB.NET还是C#
相关文章: