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

主要思路: 用一条语句统计(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"%>

1Option Explicit

  1   
  2Dim intDateStart   
  3intDateStart = Timer()   
  4Rem ## 打开数据库连接   
  5Rem #################################################################   
  6function f__OpenConn()   
  7Dim strDbPath   
  8Dim connstr   
  9strDbPath = "../db/test.mdb"   
 10connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   
 11connstr = connstr & Server.MapPath(strDbPath)   
 12Set conn = Server.CreateObject("Adodb.Connection")   
 13conn.open connstr   
 14End function   
 15Rem #################################################################   
 16  
 17Rem ## 关闭数据库连接   
 18Rem #################################################################   
 19function f__CloseConn()   
 20If IsObject(conn) Then   
 21conn.close   
 22End If   
 23Set conn = nothing   
 24End function   
 25Rem #################################################################   
 26Rem 获得执行时间   
 27Rem #################################################################   
 28function getTimeOver(iflag)   
 29Dim tTimeOver   
 30If iflag = 1 Then   
 31tTimeOver = FormatNumber(Timer() - intDateStart, 6, true)   
 32getTimeOver = " 执行时间: " & tTimeOver & " 秒"   
 33Else   
 34tTimeOver = FormatNumber((Timer() - intDateStart) * 1000, 3, true)   
 35getTimeOver = " 执行时间: " & tTimeOver & " 毫秒"   
 36End If   
 37End function   
 38Rem #################################################################   
 39Class Cls_PageView   
 40Private sbooInitState   
 41Private sstrCookiesName   
 42Private sstrPageUrl   
 43Private sstrPageVar   
 44Private sstrTableName   
 45Private sstrFieldsList   
 46Private sstrCondiction   
 47Private sstrOrderList   
 48Private sstrPrimaryKey   
 49Private sintRefresh   
 50  
 51Private sintRecordCount   
 52Private sintPageSize   
 53Private sintPageNow   
 54Private sintPageMax   
 55  
 56Private sobjConn   
 57  
 58Private sstrPageInfo   
 59  
 60Private Sub Class_Initialize   
 61Call ClearVars()   
 62End Sub   
 63  
 64Private Sub class_terminate()   
 65Set sobjConn = nothing   
 66End Sub   
 67  
 68Public Sub ClearVars()   
 69sbooInitState = False   
 70sstrCookiesName = ""   
 71sstrPageUrl = ""   
 72sstrPageVar = "page"   
 73sstrTableName = ""   
 74sstrFieldsList = ""   
 75sstrCondiction = ""   
 76sstrOrderList = ""   
 77sstrPrimaryKey = ""   
 78sintRefresh = 0   
 79  
 80sintRecordCount = 0   
 81sintPageSize = 0   
 82sintPageNow = 0   
 83sintPageMax = 0   
 84End Sub   
 85  
 86Rem ## 保存记录数的 Cookies 变量   
 87Public Property Let strCookiesName(Value)   
 88sstrCookiesName = Value   
 89End Property   
 90  
 91Rem ## 转向地址   
 92Public Property Let strPageUrl(Value)   
 93sstrPageUrl = Value   
 94End Property   
 95  
 96Rem ## 表名   
 97Public Property Let strTableName(Value)   
 98sstrTableName = Value   
 99End Property   
100  
101Rem ## 字段列表   
102Public Property Let strFieldsList(Value)   
103sstrFieldsList = Value   
104End Property   
105  
106Rem ## 查询条件   
107Public Property Let strCondiction(Value)   
108If Value <> "" Then   
109sstrCondiction = " WHERE " & Value   
110Else   
111sstrCondiction = ""   
112End If   
113End Property   
114  
115Rem ## 排序字段, 如: [ID] ASC, [CreateDateTime] DESC   
116Public Property Let strOrderList(Value)   
117If Value <> "" Then   
118sstrOrderList = " ORDER BY " & Value   
119Else   
120sstrOrderList = ""   
121End If   
122End Property   
123  
124Rem ## 用于统计记录数的字段   
125Public Property Let strPrimaryKey(Value)   
126sstrPrimaryKey = Value   
127End Property   
128  
129Rem ## 每页显示的记录条数   
130Public Property Let intPageSize(Value)   
131sintPageSize = toNum(Value, 20)   
132End Property   
133  
134Rem ## 数据库连接对象   
135Public Property Let objConn(Value)   
136Set sobjConn = Value   
137End Property   
138  
139Rem ## 当前页   
140Public Property Let intPageNow(Value)   
141sintPageNow = toNum(Value, 1)   
142End Property   
143  
144Rem ## 页面参数   
145Public Property Let strPageVar(Value)   
146sstrPageVar = Value   
147End Property   
148  
149Rem ## 是否刷新. 1 为刷新, 其他值则不刷新   
150Public Property Let intRefresh(Value)   
151sintRefresh = toNum(Value, 0)   
152End Property   
153  
154Rem ## 获得当前页   
155Public Property Get intPageNow()   
156intPageNow = singPageNow   
157End Property   
158  
159Rem ## 分页信息   
160Public Property Get strPageInfo()   
161strPageInfo = sstrPageInfo   
162End Property   
163  
164Rem ## 取得记录集, 二维数组或字串, 在进行循环输出时必须用 IsArray() 判断   
165Public Property Get arrRecordInfo()   
166If Not sbooInitState Then   
167Exit Property   
168End If   
169  
170Dim rs, sql   
171sql = "SELECT " & sstrFieldsList & _   
172" FROM " & sstrTableName & _   
173sstrCondiction & _   
174sstrOrderList   
175  
176Set rs = Server.CreateObject("Adodb.RecordSet")   
177rs.open sql, sobjConn, 1, 1   
178If Not(rs.eof or rs.bof) Then   
179rs.PageSize = sintPageSize   
180rs.AbsolutePage = sintPageNow   
181If Not(rs.eof or rs.bof) Then   
182arrRecordInfo = rs.getrows(sintPageSize)   
183Else   
184arrRecordInfo = ""   
185End If   
186Else   
187arrRecordInfo = ""   
188End If   
189rs.close   
190Set rs = nothing   
191End Property   
192  
193Rem ## 初始化记录数   
194Private Sub InitRecordCount()   
195sintRecordCount = 0   
196If Not(sbooInitState) Then Exit Sub   
197Dim sintTmp   
198sintTmp = toNum(request.Cookies("_xp_" & sstrCookiesName), -1)   
199If ((sintTmp < 0) Or (sintRefresh = 1))Then   
200Dim sql, rs   
201sql = "SELECT COUNT(" & sstrPrimaryKey & ")" & _   
202" FROM " & sstrTableName & _   
203sstrCondiction   
204Set rs = sobjConn.execute(sql)   
205If rs.eof or rs.bof Then   
206sintTmp = 0   
207Else   
208sintTmp = rs(0)   
209End If   
210sintRecordCount = sintTmp   
211  
212response.Cookies("_xp_" & sstrCookiesName) = sintTmp   
213Else   
214sintRecordCount = sintTmp   
215End If   
216End Sub   
217  
218Rem ## 初始化分页信息   
219Private Sub InitPageInfo()   
220sstrPageInfo = ""   
221If Not(sbooInitState) Then Exit Sub   
222  
223Dim surl   
224surl = sstrPageUrl   
225If Instr(1, surl, "?", 1) > 0 Then   
226surl = surl & "&" & sstrPageVar & "="   
227Else   
228surl = surl & "?" & sstrPageVar & "="   
229End If   
230  
231If sintPageNow <= 0 Then sintPageNow = 1   
232If sintRecordCount mod sintPageSize = 0 Then   
233sintPageMax = sintRecordCount \ sintPageSize   
234Else   
235sintPageMax = sintRecordCount \ sintPageSize + 1   
236End If   
237If sintPageNow > sintPageMax Then sintPageNow = sintPageMax   
238  
239If sintPageNow <= 1 then   
240sstrPageInfo = "首页 上一页"   
241Else   
242sstrPageInfo = sstrPageInfo & "

<a "="" "1""="" &="" href="" surl="">首页</a>

1"   
2sstrPageInfo = sstrPageInfo & "

<a "="" """="" &="" (sintpagenow="" -="" 1)="" href="" surl="">上一页</a>

1"   
2End If   
3  
4If sintPageMax - sintPageNow < 1 then   
5sstrPageInfo = sstrPageInfo & " 下一页 末页 "   
6Else   
7sstrPageInfo = sstrPageInfo & "

<a "="" """="" &="" (sintpagenow="" +="" 1)="" href="" surl="">下一页</a>

1"   
2sstrPageInfo = sstrPageInfo & "

<a "="" """="" &="" href="" sintpagemax="" surl="">末页</a>

1"   
2End If   
3  
4sstrPageInfo = sstrPageInfo & " 页次：

<strong><font #990000""="" color="">" &amp; sintPageNow &amp; "</font> / " &amp; sintPageMax &amp; " </strong>

1"   
2sstrPageInfo = sstrPageInfo & " 共

<strong>" &amp; sintRecordCount &amp; "</strong>

1条记录

<strong>" &amp; sintPageSize &amp; "</strong>

 1条/页 "   
 2End Sub   
 3  
 4Rem ## 长整数转换   
 5Private function toNum(s, Default)   
 6s = s & ""   
 7If s <> "" And IsNumeric(s) Then   
 8toNum = CLng(s)   
 9Else   
10toNum = Default   
11End If   
12End function   
13  
14Rem ## 类初始化   
15Public Sub InitClass()   
16sbooInitState = True   
17If Not(IsObject(sobjConn)) Then sbooInitState = False   
18Call InitRecordCount()   
19Call InitPageInfo()   
20End Sub   
21End Class   
22Dim strLocalUrl   
23strLocalUrl = request.ServerVariables("SCRIPT_NAME")   
24  
25Dim intPageNow   
26intPageNow = request.QueryString("page")   
27  
28Dim intPageSize, strPageInfo   
29intPageSize = 30   
30  
31Dim arrRecordInfo, i   
32Dim Conn   
33f__OpenConn   
34Dim clsRecordInfo   
35Set clsRecordInfo = New Cls_PageView   
36  
37clsRecordInfo.strTableName = "[myTable]"   
38clsRecordInfo.strPageUrl = strLocalUrl   
39clsRecordInfo.strFieldsList = "[ID], [Title], [LastTime]"   
40clsRecordInfo.strCondiction = "[ID] < 10000"   
41clsRecordInfo.strOrderList = "[ID] ASC"   
42clsRecordInfo.strPrimaryKey = "[ID]"   
43clsRecordInfo.intPageSize = 20   
44clsRecordInfo.intPageNow = intPageNow   
45  
46clsRecordInfo.strCookiesName = "RecordCount"   
47clsRecordInfo.strPageVar = "page"   
48  
49clsRecordInfo.intRefresh = 0   
50clsRecordInfo.objConn = Conn   
51clsRecordInfo.InitClass   
52  
53arrRecordInfo = clsRecordInfo.arrRecordInfo   
54strPageInfo = clsRecordInfo.strPageInfo   
55Set clsRecordInfo = nothing   
56f__CloseConn   

 1<html>
 2<head>
 3<meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>
 4<title>分页测试</title>
 5<style type="text/css">   
 6<!--   
 7.PageView {   
 8font-size: 12px;   
 9}   
10.PageView td {   
11border-right-style: solid;   
12border-bottom-style: solid;   
13border-right-color: #E0E0E0;   
14border-bottom-color: #E0E0E0;   
15border-right-width: 1px;   
16border-bottom-width: 1px;   
17}   
18.PageView table {   
19border-left-style: solid;   
20border-top-style: solid;   
21border-left-color: #E0E0E0;   
22border-top-color: #E0E0E0;   
23border-top-width: 1px;   
24border-left-width: 1px;   
25}   
26tr.Header {   
27background: #EFF7FF;   
28font-size: 14px;   
29font-weight: bold;   
30line-height: 120%;   
31text-align: center;   
32}   
33\-->   
34</style>
35<style type="text/css">   
36<!--   
37body {   
38font-size: 12px;   
39}   
40a:link {   
41color: #993300;   
42text-decoration: none;   
43}   
44a:visited {   
45color: #003366;   
46text-decoration: none;   
47}   
48a:hover {   
49color: #0066CC;   
50text-decoration: underline;   
51}   
52a:active {   
53color: #000000;   
54text-decoration: none;   
55}   
56table {   
57font-size: 12px;   
58}   
59\-->   
60</style>
61</head>
62<body>
63<table border="0" cellpadding="4" cellspacing="0" width="100%">
64<tr>
65<td> ```
66= strPageInfo
67```</td>
68</tr>
69</table>
70<div class="PageView">
71<table border="0" cellpadding="4" cellspacing="0" width="100%">
72<tr class="Header">
73<td>ID</td>
74<td>描述</td>
75<td>日期</td>
76</tr>

If IsArray(arrRecordInfo) Then
For i = 0 to UBound(arrRecordInfo, 2)

 1<tr>
 2<td> ```
 3= arrRecordInfo(0, i)
 4```</td>
 5<td> ```
 6= arrRecordInfo(1, i)
 7```</td>
 8<td> ```
 9= arrRecordInfo(2, i)
10```</td>
11</tr>

Next
End If

 1</table>
 2</div>
 3<table border="0" cellpadding="4" cellspacing="0" width="100%">
 4<tr>
 5<td> ```
 6= strPageInfo
 7```</td>
 8</tr>
 9</table>
10<table border="0" cellpadding="4" cellspacing="0" width="100%">
11<tr>
12<td align="center"> ```
13= getTimeOver(1)
14```</td>
15</tr>
16</table>
17</body>
18</html>
Published At
Categories with Web编程
Tagged with
comments powered by Disqus