Access2000數據庫80萬記錄通用快速分頁類

2019-11-18 19:26:02

字體：大中小

來源：轉載

供稿：網友

主要思路: 用一條語句統計(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 字段作了索引也沒用. :(
<%@LANGUAGE = "VBScr<%Option Explicit%>
<%
Dim intDateStart
intDateStart = Timer()
Rem ## 打開數據庫連接
Rem #################################################################
function f__OpenConn()
   Dim strDbPath
   Dim connstr
   strDbPath = "../db/test.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 = "[myTable]"
clsRecordInfo.strPageUrl = strLocalUrl
clsRecordInfo.strFieldsList = "[ID], [Title], [LastTime]"
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">

</style>
<style type="text/css">

</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>