开发学院WEB开发ASP Access2000数据库80万记录通用快速分页类 阅读

Access2000数据库80万记录通用快速分页类

 2005-01-18 10:29:54 来源:WEB开发网   
核心提示: 主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计. 使用 ADO 的 AbsolutePage 属性进行页面跳转即可. 为方便调用而写成类, 代码主要地方已有说明硬件环境: AMD Athlon XP 2600+

   主要思路: 用一条语句统计(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 = "VBScript" CODEPAGE="936"%>
<%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">
<!--
.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>

Tags:Access 数据库 记录

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接