最近做几个项目全使用ACCESS数据库，少了存储过程，对数据分页处理有些不太习惯，查了一些资料，设计了一个数据分页类，分四种情况对SQL语句分别处理，自己做个纪录，代码如下：
Public Class DataHelperClass DataHelper
''' <summary>SQL语句处理数据分页</summary>
''' <param name="pageSize">分量，每页纪录数量</param>
''' <param name="pageIndex">当前页</param>
''' <param name="recordCount">纪录总数</param>
''' <param name="tableName">选取的数据表</param>
''' <param name="Fields">选取的数据字段</param>
''' <param name="primaryKey">字段主键</param>
''' <param name="ascending">是否正向排序</param>
''' <param name="condition">选取条件，Where字句</param>
''' <returns>返回SQL语句</returns>
Public Shared Function Paging()Function Paging(ByVal pageSize As Integer, ByVal pageIndex As Integer, ByVal recordCount As Integer, ByVal tableName As String, ByVal Fields As String, ByVal primaryKey As String, ByVal ascending As Boolean, ByVal condition As String) As String
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
Dim pageCount As Integer = GetPageCount(recordCount, pageSize)
Dim middleIndex As Integer = GetMidPageIndex(pageCount)
Dim firstIndex As Integer = 0
Dim lastIndex As Integer = pageCount - 1
If pageIndex <= firstIndex Then
Dim sql As String = "SELECT TOP {0} {1} FROM {2} WHERE {3} ORDER BY {4} {5}"
sb.AppendFormat(sql, pageSize, Fields, tableName, condition, primaryKey, GetSortType(ascending))
ElseIf pageIndex > firstIndex AndAlso pageIndex <= middleIndex Then
Dim sql As String = "SELECT TOP {0} {1} FROM {2} WHERE {3} < (SELECT MIN({3}) FROM (SELECT TOP {4} {3} FROM {2} WHERE {5} ORDER BY {3} DESC) ta) ORDER BY {3} DESC"
If ascending Then sql = "SELECT TOP {0} {1} FROM {2} WHERE {3} > (SELECT MAX({3}) FROM (SELECT TOP {4} {3} FROM {2} WHERE {5} ORDER BY {3} ASC) ta) ORDER BY {3} ASC"
sb.AppendFormat(sql, pageSize, Fields, tableName, primaryKey, pageSize * pageIndex, condition)
ElseIf pageIndex > middleIndex AndAlso pageIndex < lastIndex Then
Dim sql As String = "SELECT * FROM (SELECT TOP {0} {1} FROM {2} WHERE {3} > (SELECT MAX({3}) FROM (SELECT TOP {4} {3} FROM {2} WHERE {5} ORDER BY {3} ASC ) ta) ORDER BY {3} ASC) tb ORDER BY {3} DESC"
If ascending Then sql = "SELECT * FROM (SELECT TOP {0} {1} FROM {2} WHERE {3} < (SELECT MIN({3}) FROM (SELECT TOP {4} {3} FROM {2} WHERE {5} ORDER BY {3} DESC) ta) ORDER BY {3} DESC) tb ORDER BY {3} ASC"
sb.AppendFormat(sql, pageSize, Fields, tableName, primaryKey, recordCount - pageSize * (pageIndex + 1), condition)
ElseIf pageIndex >= lastIndex Then
Dim sql As String = "SELECT * FROM (SELECT TOP {0} {1} FROM {2} WHERE {3} ORDER BY {4} {5}) ta ORDER BY {4} {6}"
sb.AppendFormat(sql, recordCount - pageSize * lastIndex, Fields, tableName, condition, primaryKey, GetSortType(Not ascending), GetSortType(ascending))
End If
Return sb.ToString
End Function
Public Shared Function GetPageCount()Function GetPageCount(ByVal recordCount As Integer, ByVal pageSize As Integer) As Integer
Return CType(Math.Ceiling(CType(recordCount, Double) / pageSize), Integer)
End Function
Public Shared Function GetMidPageIndex()Function GetMidPageIndex(ByVal pageCount As Integer) As Integer
Return CType(Math.Ceiling(CType(pageCount, Double) / 2), Integer) - 1
End Function
Public Shared Function GetSortType()Function GetSortType(ByVal ascending As Boolean) As String
Return (Microsoft.VisualBasic.IIf(ascending, "ASC", "DESC"))
End Function
End Class
PS:在ACCESS下我比较喜欢使用查询代替直接使用复杂的SQL语句，觉得设计方便，使用也方便，不知道对性能有什么影响。