Access 2000 数据库 80 万记录通用快速分页类
(编辑：jimmy 日期: 2024/11/8 浏览：3 次 )
代码本人优化过，测试通过
主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计. 使用 ADO 的 AbsolutePage 属性进行页面跳转即可. 为方便调用而写成类, 代码主要地方已有说明
硬件环境: AMD Athlon XP 2600+, 256 DDR
软件环境: MS Windows 2000 Advanced Server + IIS 5.0 + Access 2000 + IE 6.0
测试结果: 初次运行在 250(首页) - 400(末页)毫秒, (记录数缓存后)在页面间跳转稳定在 47 毫秒以下.第1页跳到最后一页不多于 350 毫秒
适用范围: 用于普通分页. 不适用于有较复杂的查询时: 如条件为"[Title] Like ’%最爱%’", 查询的时间大大增加, 就算 Title 字段作了索引也没用. :(
<%
Dim intDateStart
intDateStart = Timer()
Rem ## 打开数据库连接
Rem #################################################################
function f__OpenConn()
Dim strDbPath
Dim connstr
strDbPath = "fenye/db.mdb"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
connstr = connstr & Server.MapPath(strDbPath)
Set conn = Server.CreateObject("Adodb.Connection")
conn.open connstr
End function
Rem #################################################################
Rem ## 关闭数据库连接
Rem #################################################################
function f__CloseConn()
If IsObject(conn) Then
conn.close
End If
Set conn = nothing
End function
Rem #################################################################
Rem 获得执行时间
Rem #################################################################
function getTimeOver(iflag)
Dim tTimeOver
If iflag = 1 Then
tTimeOver = FormatNumber(Timer() - intDateStart, 6, true)
getTimeOver = " 执行时间: " & tTimeOver & " 秒"
Else
tTimeOver = FormatNumber((Timer() - intDateStart) * 1000, 3, true)
getTimeOver = " 执行时间: " & tTimeOver & " 毫秒"
End If
End function
Rem #################################################################
Class Cls_PageView
Private sbooInitState
Private sstrCookiesName
Private sstrPageUrl
Private sstrPageVar
Private sstrTableName
Private sstrFieldsList
Private sstrCondiction
Private sstrOrderList
Private sstrPrimaryKey
Private sintRefresh
Private sintRecordCount
Private sintPageSize
Private sintPageNow
Private sintPageMax
Private sobjConn
Private sstrPageInfo
Private Sub Class_Initialize
Call ClearVars()
End Sub
Private Sub class_terminate()
Set sobjConn = nothing
End Sub
Public Sub ClearVars()
sbooInitState = False
sstrCookiesName = ""
sstrPageUrl = ""
sstrPageVar = "page"
sstrTableName = ""
sstrFieldsList = ""
sstrCondiction = ""
sstrOrderList = ""
sstrPrimaryKey = ""
sintRefresh = 0
sintRecordCount = 0
sintPageSize = 0
sintPageNow = 0
sintPageMax = 0
End Sub
Rem ## 保存记录数的 Cookies 变量
Public Property Let strCookiesName(Value)
sstrCookiesName = Value
End Property
Rem ## 转向地址
Public Property Let strPageUrl(Value)
sstrPageUrl = Value
End Property
Rem ## 表名
Public Property Let strTableName(Value)
sstrTableName = Value
End Property
Rem ## 字段列表
Public Property Let strFieldsList(Value)
sstrFieldsList = Value
End Property
Rem ## 查询条件
Public Property Let strCondiction(Value)
If Value <> "" Then
sstrCondiction = " WHERE " & Value
Else
sstrCondiction = ""
End If
End Property
Rem ## 排序字段, 如: [ID] ASC, [CreateDateTime] DESC
Public Property Let strOrderList(Value)
If Value <> "" Then
sstrOrderList = " ORDER BY " & Value
Else
sstrOrderList = ""
End If
End Property
Rem ## 用于统计记录数的字段
Public Property Let strPrimaryKey(Value)
sstrPrimaryKey = Value
End Property
Rem ## 每页显示的记录条数
Public Property Let intPageSize(Value)
sintPageSize = toNum(Value, 20)
End Property
Rem ## 数据库连接对象
Public Property Let objConn(Value)
Set sobjConn = Value
End Property
Rem ## 当前页
Public Property Let intPageNow(Value)
sintPageNow = toNum(Value, 1)
End Property
Rem ## 页面参数
Public Property Let strPageVar(Value)
sstrPageVar = Value
End Property
Rem ## 是否刷新. 1 为刷新, 其他值则不刷新
Public Property Let intRefresh(Value)
sintRefresh = toNum(Value, 0)
End Property
Rem ## 获得当前页
Public Property Get intPageNow()
intPageNow = singPageNow
End Property
Rem ## 分页信息
Public Property Get strPageInfo()
strPageInfo = sstrPageInfo
End Property
Rem ## 取得记录集, 二维数组或字串, 在进行循环输出时必须用 IsArray() 判断
Public Property Get arrRecordInfo()
If Not sbooInitState Then
Exit Property
End If
Dim rs, sql
sql = "SELECT " & sstrFieldsList & _
" FROM " & sstrTableName & _
sstrCondiction & _
sstrOrderList
Set rs = Server.CreateObject("Adodb.RecordSet")
rs.open sql, sobjConn, 1, 1
If Not(rs.eof or rs.bof) Then
rs.PageSize = sintPageSize
rs.AbsolutePage = sintPageNow
If Not(rs.eof or rs.bof) Then
arrRecordInfo = rs.getrows(sintPageSize)
Else
arrRecordInfo = ""
End If
Else
arrRecordInfo = ""
End If
rs.close
Set rs = nothing
End Property
Rem ## 初始化记录数
Private Sub InitRecordCount()
sintRecordCount = 0
If Not(sbooInitState) Then Exit Sub
Dim sintTmp
sintTmp = toNum(request.Cookies("_xp_" & sstrCookiesName), -1)
If ((sintTmp < 0) Or (sintRefresh = 1))Then
Dim sql, rs
sql = "SELECT COUNT(" & sstrPrimaryKey & ")" & _
" FROM " & sstrTableName & _
sstrCondiction
Set rs = sobjConn.execute(sql)
If rs.eof or rs.bof Then
sintTmp = 0
Else
sintTmp = rs(0)
End If
sintRecordCount = sintTmp
response.Cookies("_xp_" & sstrCookiesName) = sintTmp
Else
sintRecordCount = sintTmp
End If
End Sub
Rem ## 初始化分页信息
Private Sub InitPageInfo()
sstrPageInfo = ""
If Not(sbooInitState) Then Exit Sub
Dim surl
surl = sstrPageUrl
If Instr(1, surl, "?", 1) > 0 Then
surl = surl & "&" & sstrPageVar & "="
Else
surl = surl & "?" & sstrPageVar & "="
End If
If sintPageNow <= 0 Then sintPageNow = 1
If sintRecordCount mod sintPageSize = 0 Then
sintPageMax = sintRecordCount \ sintPageSize
Else
sintPageMax = sintRecordCount \ sintPageSize + 1
End If
If sintPageNow > sintPageMax Then sintPageNow = sintPageMax
If sintPageNow <= 1 then
sstrPageInfo = "首页 上一页"
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & "1"">首页</a>"
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow - 1) & """>上一页</a>"
End If
If sintPageMax - sintPageNow < 1 then
sstrPageInfo = sstrPageInfo & " 下一页 末页 "
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow + 1) & """>下一页</a> "
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & sintPageMax & """>末页</a> "
End If
sstrPageInfo = sstrPageInfo & " 页次：<strong><font color=""#990000"">" & sintPageNow & "</font> / " & sintPageMax & " </strong>"
sstrPageInfo = sstrPageInfo & " 共 <strong>" & sintRecordCount & "</strong> 条记录 <strong>" & sintPageSize & "</strong> 条/页 "
End Sub
Rem ## 长整数转换
Private function toNum(s, Default)
s = s & ""
If s <> "" And IsNumeric(s) Then
toNum = CLng(s)
Else
toNum = Default
End If
End function
Rem ## 类初始化
Public Sub InitClass()
sbooInitState = True
If Not(IsObject(sobjConn)) Then sbooInitState = False
Call InitRecordCount()
Call InitPageInfo()
End Sub
End Class
Dim strLocalUrl
strLocalUrl = request.ServerVariables("SCRIPT_NAME")
Dim intPageNow
intPageNow = request.QueryString("page")
Dim intPageSize, strPageInfo
intPageSize = 30
Dim arrRecordInfo, i
Dim Conn
f__OpenConn
Dim clsRecordInfo
Set clsRecordInfo = New Cls_PageView
clsRecordInfo.strTableName = "[table1]"
clsRecordInfo.strPageUrl = strLocalUrl
clsRecordInfo.strFieldsList = "[ID], [aaaa], [bbbb], [cccc]"
clsRecordInfo.strCondiction = "[ID] < 10000"
clsRecordInfo.strOrderList = "[ID] ASC"
clsRecordInfo.strPrimaryKey = "[ID]"
clsRecordInfo.intPageSize = 20
clsRecordInfo.intPageNow = intPageNow
clsRecordInfo.strCookiesName = "RecordCount"
clsRecordInfo.strPageVar = "page"
clsRecordInfo.intRefresh = 0
clsRecordInfo.objConn = Conn
clsRecordInfo.InitClass
arrRecordInfo = clsRecordInfo.arrRecordInfo
strPageInfo = clsRecordInfo.strPageInfo
Set clsRecordInfo = nothing
f__CloseConn
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>分页测试</title>
<style type="text/css">
<!--
.PageView {
font-size: 12px;
}
.PageView td {
border-right-style: solid;
border-bottom-style: solid;
border-right-color: #E0E0E0;
border-bottom-color: #E0E0E0;
border-right-width: 1px;
border-bottom-width: 1px;
}
.PageView table {
border-left-style: solid;
border-top-style: solid;
border-left-color: #E0E0E0;
border-top-color: #E0E0E0;
border-top-width: 1px;
border-left-width: 1px;
}
tr.Header {
background: #EFF7FF;
font-size: 14px;
font-weight: bold;
line-height: 120%;
text-align: center;
}
-->
</style>
<style type="text/css">
<!--
body {
font-size: 12px;
}
a:link {
color: #993300;
text-decoration: none;
}
a:visited {
color: #003366;
text-decoration: none;
}
a:hover {
color: #0066CC;
text-decoration: underline;
}
a:active {
color: #000000;
text-decoration: none;
}
table {
font-size: 12px;
}
-->
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strPageInfo%></td>
</tr>
</table>
<div class="PageView">
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr class="Header">
<td>ID</td>
<td>描述</td>
<td>日期</td>
</tr>
<%
If IsArray(arrRecordInfo) Then
For i = 0 to UBound(arrRecordInfo, 2)
%>
<tr>
<td> <%= arrRecordInfo(0, i)%></td>
<td> <%= arrRecordInfo(1, i)%></td>
<td> <%= arrRecordInfo(2, i)%></td>
</tr>
<%
Next
End If
%>
</table>
</div>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strPageInfo%></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td align="center"> <%= getTimeOver(1)%></td>
</tr>
</table>
</body>
</html>
主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计. 使用 ADO 的 AbsolutePage 属性进行页面跳转即可. 为方便调用而写成类, 代码主要地方已有说明
硬件环境: AMD Athlon XP 2600+, 256 DDR
软件环境: MS Windows 2000 Advanced Server + IIS 5.0 + Access 2000 + IE 6.0
测试结果: 初次运行在 250(首页) - 400(末页)毫秒, (记录数缓存后)在页面间跳转稳定在 47 毫秒以下.第1页跳到最后一页不多于 350 毫秒
适用范围: 用于普通分页. 不适用于有较复杂的查询时: 如条件为"[Title] Like ’%最爱%’", 查询的时间大大增加, 就算 Title 字段作了索引也没用. :(
<%
Dim intDateStart
intDateStart = Timer()
Rem ## 打开数据库连接
Rem #################################################################
function f__OpenConn()
Dim strDbPath
Dim connstr
strDbPath = "fenye/db.mdb"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
connstr = connstr & Server.MapPath(strDbPath)
Set conn = Server.CreateObject("Adodb.Connection")
conn.open connstr
End function
Rem #################################################################
Rem ## 关闭数据库连接
Rem #################################################################
function f__CloseConn()
If IsObject(conn) Then
conn.close
End If
Set conn = nothing
End function
Rem #################################################################
Rem 获得执行时间
Rem #################################################################
function getTimeOver(iflag)
Dim tTimeOver
If iflag = 1 Then
tTimeOver = FormatNumber(Timer() - intDateStart, 6, true)
getTimeOver = " 执行时间: " & tTimeOver & " 秒"
Else
tTimeOver = FormatNumber((Timer() - intDateStart) * 1000, 3, true)
getTimeOver = " 执行时间: " & tTimeOver & " 毫秒"
End If
End function
Rem #################################################################
Class Cls_PageView
Private sbooInitState
Private sstrCookiesName
Private sstrPageUrl
Private sstrPageVar
Private sstrTableName
Private sstrFieldsList
Private sstrCondiction
Private sstrOrderList
Private sstrPrimaryKey
Private sintRefresh
Private sintRecordCount
Private sintPageSize
Private sintPageNow
Private sintPageMax
Private sobjConn
Private sstrPageInfo
Private Sub Class_Initialize
Call ClearVars()
End Sub
Private Sub class_terminate()
Set sobjConn = nothing
End Sub
Public Sub ClearVars()
sbooInitState = False
sstrCookiesName = ""
sstrPageUrl = ""
sstrPageVar = "page"
sstrTableName = ""
sstrFieldsList = ""
sstrCondiction = ""
sstrOrderList = ""
sstrPrimaryKey = ""
sintRefresh = 0
sintRecordCount = 0
sintPageSize = 0
sintPageNow = 0
sintPageMax = 0
End Sub
Rem ## 保存记录数的 Cookies 变量
Public Property Let strCookiesName(Value)
sstrCookiesName = Value
End Property
Rem ## 转向地址
Public Property Let strPageUrl(Value)
sstrPageUrl = Value
End Property
Rem ## 表名
Public Property Let strTableName(Value)
sstrTableName = Value
End Property
Rem ## 字段列表
Public Property Let strFieldsList(Value)
sstrFieldsList = Value
End Property
Rem ## 查询条件
Public Property Let strCondiction(Value)
If Value <> "" Then
sstrCondiction = " WHERE " & Value
Else
sstrCondiction = ""
End If
End Property
Rem ## 排序字段, 如: [ID] ASC, [CreateDateTime] DESC
Public Property Let strOrderList(Value)
If Value <> "" Then
sstrOrderList = " ORDER BY " & Value
Else
sstrOrderList = ""
End If
End Property
Rem ## 用于统计记录数的字段
Public Property Let strPrimaryKey(Value)
sstrPrimaryKey = Value
End Property
Rem ## 每页显示的记录条数
Public Property Let intPageSize(Value)
sintPageSize = toNum(Value, 20)
End Property
Rem ## 数据库连接对象
Public Property Let objConn(Value)
Set sobjConn = Value
End Property
Rem ## 当前页
Public Property Let intPageNow(Value)
sintPageNow = toNum(Value, 1)
End Property
Rem ## 页面参数
Public Property Let strPageVar(Value)
sstrPageVar = Value
End Property
Rem ## 是否刷新. 1 为刷新, 其他值则不刷新
Public Property Let intRefresh(Value)
sintRefresh = toNum(Value, 0)
End Property
Rem ## 获得当前页
Public Property Get intPageNow()
intPageNow = singPageNow
End Property
Rem ## 分页信息
Public Property Get strPageInfo()
strPageInfo = sstrPageInfo
End Property
Rem ## 取得记录集, 二维数组或字串, 在进行循环输出时必须用 IsArray() 判断
Public Property Get arrRecordInfo()
If Not sbooInitState Then
Exit Property
End If
Dim rs, sql
sql = "SELECT " & sstrFieldsList & _
" FROM " & sstrTableName & _
sstrCondiction & _
sstrOrderList
Set rs = Server.CreateObject("Adodb.RecordSet")
rs.open sql, sobjConn, 1, 1
If Not(rs.eof or rs.bof) Then
rs.PageSize = sintPageSize
rs.AbsolutePage = sintPageNow
If Not(rs.eof or rs.bof) Then
arrRecordInfo = rs.getrows(sintPageSize)
Else
arrRecordInfo = ""
End If
Else
arrRecordInfo = ""
End If
rs.close
Set rs = nothing
End Property
Rem ## 初始化记录数
Private Sub InitRecordCount()
sintRecordCount = 0
If Not(sbooInitState) Then Exit Sub
Dim sintTmp
sintTmp = toNum(request.Cookies("_xp_" & sstrCookiesName), -1)
If ((sintTmp < 0) Or (sintRefresh = 1))Then
Dim sql, rs
sql = "SELECT COUNT(" & sstrPrimaryKey & ")" & _
" FROM " & sstrTableName & _
sstrCondiction
Set rs = sobjConn.execute(sql)
If rs.eof or rs.bof Then
sintTmp = 0
Else
sintTmp = rs(0)
End If
sintRecordCount = sintTmp
response.Cookies("_xp_" & sstrCookiesName) = sintTmp
Else
sintRecordCount = sintTmp
End If
End Sub
Rem ## 初始化分页信息
Private Sub InitPageInfo()
sstrPageInfo = ""
If Not(sbooInitState) Then Exit Sub
Dim surl
surl = sstrPageUrl
If Instr(1, surl, "?", 1) > 0 Then
surl = surl & "&" & sstrPageVar & "="
Else
surl = surl & "?" & sstrPageVar & "="
End If
If sintPageNow <= 0 Then sintPageNow = 1
If sintRecordCount mod sintPageSize = 0 Then
sintPageMax = sintRecordCount \ sintPageSize
Else
sintPageMax = sintRecordCount \ sintPageSize + 1
End If
If sintPageNow > sintPageMax Then sintPageNow = sintPageMax
If sintPageNow <= 1 then
sstrPageInfo = "首页 上一页"
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & "1"">首页</a>"
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow - 1) & """>上一页</a>"
End If
If sintPageMax - sintPageNow < 1 then
sstrPageInfo = sstrPageInfo & " 下一页 末页 "
Else
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & (sintPageNow + 1) & """>下一页</a> "
sstrPageInfo = sstrPageInfo & " <a href=""" & surl & sintPageMax & """>末页</a> "
End If
sstrPageInfo = sstrPageInfo & " 页次：<strong><font color=""#990000"">" & sintPageNow & "</font> / " & sintPageMax & " </strong>"
sstrPageInfo = sstrPageInfo & " 共 <strong>" & sintRecordCount & "</strong> 条记录 <strong>" & sintPageSize & "</strong> 条/页 "
End Sub
Rem ## 长整数转换
Private function toNum(s, Default)
s = s & ""
If s <> "" And IsNumeric(s) Then
toNum = CLng(s)
Else
toNum = Default
End If
End function
Rem ## 类初始化
Public Sub InitClass()
sbooInitState = True
If Not(IsObject(sobjConn)) Then sbooInitState = False
Call InitRecordCount()
Call InitPageInfo()
End Sub
End Class
Dim strLocalUrl
strLocalUrl = request.ServerVariables("SCRIPT_NAME")
Dim intPageNow
intPageNow = request.QueryString("page")
Dim intPageSize, strPageInfo
intPageSize = 30
Dim arrRecordInfo, i
Dim Conn
f__OpenConn
Dim clsRecordInfo
Set clsRecordInfo = New Cls_PageView
clsRecordInfo.strTableName = "[table1]"
clsRecordInfo.strPageUrl = strLocalUrl
clsRecordInfo.strFieldsList = "[ID], [aaaa], [bbbb], [cccc]"
clsRecordInfo.strCondiction = "[ID] < 10000"
clsRecordInfo.strOrderList = "[ID] ASC"
clsRecordInfo.strPrimaryKey = "[ID]"
clsRecordInfo.intPageSize = 20
clsRecordInfo.intPageNow = intPageNow
clsRecordInfo.strCookiesName = "RecordCount"
clsRecordInfo.strPageVar = "page"
clsRecordInfo.intRefresh = 0
clsRecordInfo.objConn = Conn
clsRecordInfo.InitClass
arrRecordInfo = clsRecordInfo.arrRecordInfo
strPageInfo = clsRecordInfo.strPageInfo
Set clsRecordInfo = nothing
f__CloseConn
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>分页测试</title>
<style type="text/css">
<!--
.PageView {
font-size: 12px;
}
.PageView td {
border-right-style: solid;
border-bottom-style: solid;
border-right-color: #E0E0E0;
border-bottom-color: #E0E0E0;
border-right-width: 1px;
border-bottom-width: 1px;
}
.PageView table {
border-left-style: solid;
border-top-style: solid;
border-left-color: #E0E0E0;
border-top-color: #E0E0E0;
border-top-width: 1px;
border-left-width: 1px;
}
tr.Header {
background: #EFF7FF;
font-size: 14px;
font-weight: bold;
line-height: 120%;
text-align: center;
}
-->
</style>
<style type="text/css">
<!--
body {
font-size: 12px;
}
a:link {
color: #993300;
text-decoration: none;
}
a:visited {
color: #003366;
text-decoration: none;
}
a:hover {
color: #0066CC;
text-decoration: underline;
}
a:active {
color: #000000;
text-decoration: none;
}
table {
font-size: 12px;
}
-->
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strPageInfo%></td>
</tr>
</table>
<div class="PageView">
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr class="Header">
<td>ID</td>
<td>描述</td>
<td>日期</td>
</tr>
<%
If IsArray(arrRecordInfo) Then
For i = 0 to UBound(arrRecordInfo, 2)
%>
<tr>
<td> <%= arrRecordInfo(0, i)%></td>
<td> <%= arrRecordInfo(1, i)%></td>
<td> <%= arrRecordInfo(2, i)%></td>
</tr>
<%
Next
End If
%>
</table>
</div>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td> <%= strPageInfo%></td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td align="center"> <%= getTimeOver(1)%></td>
</tr>
</table>
</body>
</html>
下一篇：asp的通用数据分页类