分享
 
 
 

Access 2000 数据库 80 万记录通用快速分页类 [摘]

王朝asp·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

作者:萧月痕(xiaoyuehen)

地址:http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx

转贴请包含相关信息, 谢谢.

主要思路: 用一条语句统计(Count)出记录数(而不在查询时获得 RecordCount 属性), 缓存在 Cookies 中, 跳转时就不用再次统计, 分页跳转链接也由此来, 使用 ADO 的 AbsolutePage 属性设置当前页面, 返回intPageSize 条记录. 为方便调用而写成类, 代码主要地方已有说明

硬件环境: 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 #################################################################

[url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]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 [url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]function

Rem #################################################################

Rem ## 关闭数据库连接

Rem #################################################################

[url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]function f__CloseConn()

If IsObject(conn) Then

conn.close

End If

Set conn = nothing

End [url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]function

Rem #################################################################

Rem 获得执行时间

Rem #################################################################

[url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]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 [url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]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 [url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]function toNum(s, Default)

s = s & ""

If s <> "" And IsNumeric(s) Then

toNum = CLng(s)

Else

toNum = Default

End If

End [url=http://blog.csdn.net/xiaoyuehen/admin/EditPosts.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#][url=http://blog.csdn.net/xiaoyuehen/archive/2005/01/17/257202.aspx#]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>&nbsp;<%= 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>&nbsp;<%= arrRecordInfo(0, i)%><

/td>

<td>&nbsp;<%= arrRecordInfo(1, i)%><

/td>

<td>&nbsp;<%= arrRecordInfo(2, i)%><

/td>

</tr>

<%

Next

End If

%>

</table>

<

/div>

<table width="100%" border="0" cellspacing="0" cellpadding="4">

<tr>

<td>&nbsp;<%= strPageInfo%><

/td>

</tr>

</table>

<table width="100%" border="0" cellspacing="0" cellpadding="4">

<tr>

<td align="center">&nbsp;<%= getTimeOver(1)%><

/td>

</tr>

</table>

</body>

</html>

这里将所有代码集中在一起是为了叙述和测试方便, 具体应用你可以将类放在单独一个文件, 然后多处调用. 请将适当的时候判断用户是否进行请求, 更改 intRefresh, 以便告知分页类是否重新统计记录数.

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有