ADO.NET中实现查询结果的分页显示

ADO.NET中实现查询结果的分页显示
一、问题分析：
    我们利用ADO.NET进行数据库编程时，一般是将查询结果填充到DataSet的DataTable对象中，再通过与其绑定的DataGrid控件显示出来。很多时候查询结果的记录数会比较大，虽然理论上一个DataTable对象可以容纳16000000行，但在实际操作中，我们实在没有必要将全部记录装入DataTable，原因在于：用户肯定不会同时浏览这么多行，滚动条的出现使程序操作更繁琐，并且通过网络传输的记录太多，程序的效率也会大大降低。因此应尽量减少读取的记录数，采用分页机制：一次显示一页内容，只读取需要的记录。

    在填充DataTable时，将起始记录和记录个数作为参数传递给DataAdapter的Fill方法，就能实现简单的分页效果，代码如下：
    da.Fill(dt,(n-1)*10,10,"订单")    '读订单表的第n页(每页10行)填充dt
    实际在执行时，DataAdapter仍然要读取指定记录前的所有记录（即包括前n－1页），然后放弃它们。所以该方法只适用于较小的数据表，若处理大型数据库，并不能实质上提升效率。为了更好的实现分页机制，必须编写一些灵活的SQL语句。
二、实现方法：

（订单表）
    如图所示，假设有一个12条记录的数据表“订单”，按“订单ID”列排序，我们要分3页显示，每页4行，获取第1页的SQL语句很简单：
SELECT TOP 4 * FROM 订单 ORDER BY 订单ID

    移到下一页的语句也很简单，假设当前正处于表的第2页，想读出后4行，这些行的订单ID值应大于当前页最后一条记录的订单ID值：
SELECT TOP 4 * FROM 订单 WHERE 订单ID>8 ORDER BY 订单ID

    获取最后一页的语句稍复杂一些：按反向顺序读取记录，然后选择结果中的前4行。为简化问题，我们假设表中的总记录数是每页记录数的整数倍：
    SELECT TOP 4 * FROM 订单 ORDER BY 订单ID DESC
    但这样得到的结果，行的顺序是反的（即按订单ID从大到小排列），所以需要颠倒这些行的顺序，我们将以上查询当作另一个正向排序查询命令的子查询：
SELECT * FROM 订单 WHERE 订单ID IN
(SELECT TOP 4 订单ID FROM 订单 ORDER BY 订单ID DESC)
ORDER BY 订单ID

    假设当前页是第2页，移到上一页的语句如下，同样要将行的顺序颠倒一次：
SELECT * FROM 订单 WHERE 订单ID IN
(SELECT TOP 4 订单ID FROM 订单 WHERE 订单ID<5 ORDER BY 订单ID DESC)
ORDER BY 订单ID

    现在可以在Windows窗体或Web窗体中实现第一页、上一页、下一页、最后一页等按钮了，我们再添加一个“转到”按钮，实现直接显示第n页的效果。例如，显示第5页可先读取前20行，再提取结果中的后4行：
SELECT TOP 4 * FROM 订单 WHERE 订单ID IN
(SELECT TOP 20 订单ID FROM 订单 ORDER BY 订单ID)
ORDER BY 订单ID DESC
    此查询的结果也是反向的，同样需要将查询结果当作另一个正向排序查询命令的子查询：
SELECT * FROM 订单 WHERE 订单ID IN
(SELECT TOP 4 订单ID FROM 订单 WHERE 订单ID IN
(SELECT TOP 20 订单ID FROM 订单 ORDER BY 订单ID)
ORDER BY 订单ID DESC)
ORDER BY 订单ID

    在实际编程中，只需将每页记录数、页码、当前页首（尾）记录主键键值等替换为实际值，即可方便地编写出高效率的分页显示程序。

三、程序实例：
程序界面如图2所示。DataGrid1用于同DataTable绑定，5个按钮分别代表第一页、上一页、下一页、最后一页、转到n页功能，Label1显示当前页码，Label2显示总页数，TextBox1用于输入要转到的页码n。

为方便演示，我们以Office 2000中自带的Access数据库C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb为例，使用其中的“订单”数据表，主键为“订单ID”，共830行，每页10 行。编程工具采用VB.NET，代码及注释如下：
Imports System.Data.OleDb  '引用命名空间
Public Class Form1
    Inherits System.Windows.Forms.Form
#Region " Windows 窗体设计器生成的代码 "

    Dim cnstring As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
    '定义连接字符串
    Dim cn As New OleDbConnection(cnstring)
    Dim cmd As OleDbCommand
    Dim da As OleDbDataAdapter
    Dim PageSize As Integer = 10    '每页行数
    Dim RecCount As Integer         '总记录数
    Dim PageCount As Integer        '总页数
    Dim CurrPage As Integer         '当前页页码
    Dim ds As New DataSet
    Dim dt As DataTable = ds.Tables.Add("订单")
    Dim sql As String

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Button5.Enabled = False         'Button5不可用
        GetPageNumber()                 '求总页数过程
        DataGrid1.DataSource = dt       '将DataGrid绑定到DataTable
        Button1.PerformClick()          '调用Button1的Click过程，显示第一页
    End Sub
    Sub GetPageNumber()
        cn.Open()                               '建立连接
        sql = "SELECT COUNT(*) FROM 订单"
        cmd = New OleDbCommand(sql, cn)
        RecCount = CInt(cmd.ExecuteScalar())    '求总记录数
        cn.Close()                              '关闭连接
        PageCount = (RecCount + PageSize - 1) \ PageSize
        '求总页数
        Label2.Text = " of " & PageCount.ToString   '显示总页数
    End Sub
    Sub DisplayPage(ByVal n As Integer, ByVal sql As String)
        cn.Open()
        Dim da As New OleDbDataAdapter(sql, cn)
        '实现查询
        dt.Clear()
        da.Fill(dt)
        '填充DataTable，显示
        cn.Close()
        CurrPage = n
        Label1.Text = n.ToString
        '显示当前页码
        Button1.Enabled = (n > 1)
        Button2.Enabled = (n > 1)
        Button3.Enabled = (n < PageCount)
        Button4.Enabled = (n < PageCount)
        '设置4个按钮的使用状态
    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        sql = String.Format("SELECT TOP {0} * FROM 订单 ORDER BY 订单ID", PageSize)
        '查询第一页的SQL语句
        DisplayPage(1, sql)
    End Sub

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        sql = String.Format("SELECT * FROM 订单 WHERE 订单ID IN (SELECT TOP {0} 订单ID FROM 订单 WHERE 订单ID < {1} ORDER BY 订单ID DESC) ORDER BY 订单ID", PageSize, dt.Rows(0)("订单ID"))
        '查询上一页的SQL语句
        DisplayPage(CurrPage - 1, sql)
    End Sub

    Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        sql = String.Format("SELECT TOP {0} * FROM 订单 WHERE 订单ID > {1} ORDER BY 订单ID", PageSize, dt.Rows(dt.Rows.Count - 1)("订单ID"))
        '查询下一页的SQL语句
        DisplayPage(CurrPage + 1, sql)
    End Sub

    Private Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim num As Integer = RecCount - PageSize * (PageCount - 1)
        '求最后一页的记录个数
        sql = String.Format("SELECT * FROM 订单 WHERE 订单ID IN (SELECT TOP {0} 订单ID FROM 订单 ORDER BY 订单ID DESC) ORDER BY 订单ID", num)
        '查询最后一页的SQL语句
        DisplayPage(PageCount, sql)
    End Sub

    Private Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim PageNum As Integer = CInt(TextBox1.Text)
        'TextBox1中为要转到的页码
        If PageNum > 1 And PageNum < PageCount Then
            sql = String.Format("SELECT * FROM 订单 WHERE 订单ID IN (SELECT TOP {0} 订单ID FROM 订单 WHERE 订单ID IN (SELECT TOP {1} 订单ID FROM 订单 ORDER BY 订单ID) ORDER BY 订单ID DESC) ORDER BY 订单ID", PageSize, PageSize * PageNum)
            DisplayPage(PageNum, sql)
        Else
            MessageBox.Show("页号必须在 (1," & PageCount.ToString & ")")
        End If
        '实现转到第n页的功能
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Button5.Enabled = TextBox1.Text.Length > 0
        '输入页码后，Button5可用
    End Sub
End Class

四、结束语
数据表的分页显示能给用户的操作带来很大的便利，是十分有效的编程方式。笔者在此抛砖引玉，希望能看到更多同行的编程心得，当然，也希望本文能给读者在数据库编程时带来一定的启示和帮助。

程序在Windows XP，Visual Studio .NET 2003环境下调试通过。