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

　　代码本人优化过，测试通过

主要思路:用一条语句统计(Count)出记录数(而不在查询时获得RecordCount属性),缓存在Cookies中,跳转时就不用再次统计.使用ADO的AbsolutePage属性进行页面跳转即可.为方便调用而写成类,代码主要地方已有说明

硬件环境:AMDAthlonXP2600+,256DDR
软件环境:MSWindows2000AdvancedServer+IIS5.0+Access2000+IE6.0
测试结果:初次运行在250(首页)-400(末页)毫秒,(记录数缓存后)在页面间跳转稳定在47毫秒以下.第1页跳到最后一页不多于350毫秒

适用范围:用于普通分页.不适用于有较复杂的查询时:如条件为"[Title]Like’%最爱%’",查询的时间大大增加,就算Title字段作了索引也没用.:(

<%
DimintDateStart
intDateStart=Timer()

Rem##打开数据库连接
Rem#################################################################
functionf__OpenConn()
DimstrDbPath
Dimconnstr
strDbPath="fenye/db.mdb"
connstr="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="
connstr=connstr&Server.MapPath(strDbPath)
conn.openconnstr
Endfunction
Rem#################################################################

Rem##关闭数据库连接
Rem#################################################################
functionf__CloseConn()
IfIsObject(conn)Then
conn.close
EndIf
Setconn=nothing
Endfunction
Rem#################################################################
Rem获得执行时间
Rem#################################################################
functiongetTimeOver(iflag)
DimtTimeOver
Ififlag=1Then
tTimeOver=FormatNumber(Timer()-intDateStart,6,true)
getTimeOver="执行时间:"&tTimeOver&"秒"
Else
tTimeOver=FormatNumber((Timer()-intDateStart)*1000,3,true)
getTimeOver="执行时间:"&tTimeOver&"毫秒"
EndIf
Endfunction
Rem#################################################################
ClassCls_PageView
PrivatesbooInitState
PrivatesstrCookiesName
PrivatesstrPageUrl
PrivatesstrPageVar
PrivatesstrTableName
PrivatesstrFieldsList
PrivatesstrCondiction
PrivatesstrOrderList
PrivatesstrPrimaryKey
PrivatesintRefresh

PrivatesintRecordCount
PrivatesintPageSize
PrivatesintPageNow
PrivatesintPageMax

PrivatesobjConn

PrivatesstrPageInfo

PrivateSubClass_Initialize
CallClearVars()
EndSub

PrivateSubclass_terminate()
SetsobjConn=nothing
EndSub

PublicSubClearVars()
sbooInitState=False
sstrCookiesName=""
sstrPageUrl=""
sstrPageVar="page"
sstrTableName=""
sstrFieldsList=""
sstrCondiction=""
sstrOrderList=""
sstrPrimaryKey=""
sintRefresh=0

sintRecordCount=0
sintPageSize=0
sintPageNow=0
sintPageMax=0
EndSub

Rem##保存记录数的Cookies变量
PublicPropertyLetstrCookiesName(Value)
sstrCookiesName=Value
EndProperty

Rem##转向地址
PublicPropertyLetstrPageUrl(Value)
sstrPageUrl=Value
EndProperty

Rem##表名
PublicPropertyLetstrTableName(Value)
sstrTableName=Value
EndProperty

Rem##字段列表
PublicPropertyLetstrFieldsList(Value)
sstrFieldsList=Value
EndProperty

Rem##查询条件
PublicPropertyLetstrCondiction(Value)
IfValue<>""Then
sstrCondiction="WHERE"&Value
Else
sstrCondiction=""
EndIf
EndProperty

Rem##排序字段,如:[ID]ASC,[CreateDateTime]DESC
PublicPropertyLetstrOrderList(Value)
IfValue<>""Then
sstrOrderList="ORDERBY"&Value
Else
sstrOrderList=""
EndIf
EndProperty

Rem##用于统计记录数的字段
PublicPropertyLetstrPrimaryKey(Value)
sstrPrimaryKey=Value
EndProperty

Rem##每页显示的记录条数
PublicPropertyLetintPageSize(Value)
sintPageSize=toNum(Value,20)
EndProperty

Rem##数据库连接对象
PublicPropertyLetobjConn(Value)
SetsobjConn=Value
EndProperty

Rem##当前页
PublicPropertyLetintPageNow(Value)
sintPageNow=toNum(Value,1)
EndProperty

Rem##页面参数
PublicPropertyLetstrPageVar(Value)
sstrPageVar=Value
EndProperty

Rem##是否刷新.1为刷新,其他值则不刷新
PublicPropertyLetintRefresh(Value)
sintRefresh=toNum(Value,0)
EndProperty

Rem##获得当前页
PublicPropertyGetintPageNow()
intPageNow=singPageNow
EndProperty

Rem##分页信息
PublicPropertyGetstrPageInfo()
strPageInfo=sstrPageInfo
EndProperty

Rem##取得记录集,二维数组或字串,在进行循环输出时必须用IsArray()判断
PublicPropertyGetarrRecordInfo()
IfNotsbooInitStateThen
ExitProperty
EndIf

Dimrs,sql
sql="SELECT"&sstrFieldsList&_
"FROM"&sstrTableName&_
sstrCondiction&_
sstrOrderList

Setrs=Server.CreateObject("Adodb.RecordSet")
rs.opensql,sobjConn,1,1
IfNot(rs.eoforrs.bof)Then
rs.PageSize=sintPageSize
rs.AbsolutePage=sintPageNow
IfNot(rs.eoforrs.bof)Then
arrRecordInfo=rs.getrows(sintPageSize)
Else
arrRecordInfo=""
EndIf
Else
arrRecordInfo=""
EndIf
rs.close
Setrs=nothing
EndProperty

Rem##初始化记录数
PrivateSubInitRecordCount()
sintRecordCount=0
IfNot(sbooInitState)ThenExitSub
DimsintTmp
sintTmp=toNum(request.Cookies("_xp_"&sstrCookiesName),-1)
If((sintTmp<0)Or(sintRefresh=1))Then
Dimsql,rs
sql="SELECTCOUNT("&sstrPrimaryKey&")"&_
"FROM"&sstrTableName&_
sstrCondiction
Setrs=sobjConn.execute(sql)
Ifrs.eoforrs.bofThen
sintTmp=0
Else
sintTmp=rs(0)
EndIf
sintRecordCount=sintTmp

response.Cookies("_xp_"&sstrCookiesName)=sintTmp
Else
sintRecordCount=sintTmp
EndIf
EndSub

Rem##初始化分页信息
PrivateSubInitPageInfo()
sstrPageInfo=""
IfNot(sbooInitState)ThenExitSub

Dimsurl
surl=sstrPageUrl
IfInstr(1,surl,"?",1)>0Then
surl=surl&"&"&sstrPageVar&"="
Else
surl=surl&"?"&sstrPageVar&"="
EndIf

IfsintPageNow<=0ThensintPageNow=1
IfsintRecordCountmodsintPageSize=0Then
sintPageMax=sintRecordCount\sintPageSize
Else
sintPageMax=sintRecordCount\sintPageSize+1
EndIf
IfsintPageNow>sintPageMaxThensintPageNow=sintPageMax

IfsintPageNow<=1then
sstrPageInfo="首页上一页"
Else
sstrPageInfo=sstrPageInfo&"<ahref="""&surl&"1"">首页</a>"
sstrPageInfo=sstrPageInfo&"<ahref="""&surl&(sintPageNow-1)&""">上一页</a>"
EndIf

IfsintPageMax-sintPageNow<1then
sstrPageInfo=sstrPageInfo&"下一页末页"
Else
sstrPageInfo=sstrPageInfo&"<ahref="""&surl&(sintPageNow+1)&""">下一页</a>"
sstrPageInfo=sstrPageInfo&"<ahref="""&surl&sintPageMax&""">末页</a>"
EndIf

sstrPageInfo=sstrPageInfo&"页次：<strong><fontcolor=""#990000"">"&sintPageNow&"</font>/"&sintPageMax&"</strong>"
sstrPageInfo=sstrPageInfo&"共<strong>"&sintRecordCount&"</strong>条记录<strong>"&sintPageSize&"</strong>条/页"
EndSub

Rem##长整数转换
PrivatefunctiontoNum(s,Default)
s=s&""
Ifs<>""AndIsNumeric(s)Then
toNum=CLng(s)
Else
toNum=Default
EndIf
Endfunction

Rem##类初始化
PublicSubInitClass()
sbooInitState=True
IfNot(IsObject(sobjConn))ThensbooInitState=False
CallInitRecordCount()
CallInitPageInfo()
EndSub
EndClass


DimstrLocalUrl
strLocalUrl=request.ServerVariables("SCRIPT_NAME")

DimintPageNow
intPageNow=request.QueryString("page")

DimintPageSize,strPageInfo
intPageSize=30

DimarrRecordInfo,i
DimConn
f__OpenConn
DimclsRecordInfo
SetclsRecordInfo=NewCls_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.intPageNow=intPageNow

clsRecordInfo.strCookiesName="RecordCount"
clsRecordInfo.strPageVar="page"

clsRecordInfo.intRefresh=0
clsRecordInfo.objConn=Conn
clsRecordInfo.InitClass

arrRecordInfo=clsRecordInfo.arrRecordInfo
strPageInfo=clsRecordInfo.strPageInfo
SetclsRecordInfo=nothing
f__CloseConn
%>
<html>
<head>
<metahttp-equiv="Content-Type"content="text/html;charset=gb2312">
<title>分页测试</title>
<styletype="text/css">
<!--
.PageView{
font-size:12px;
}
.PageViewtd{
border-right-style:solid;
border-bottom-style:solid;
border-right-color:#E0E0E0;
border-bottom-color:#E0E0E0;
border-right-width:1px;
border-bottom-width:1px;
}
.PageViewtable{
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>
<styletype="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>
<tablewidth="100%"border="0"cellspacing="0"cellpadding="4">
<tr>
<td><%=strPageInfo%></td>
</tr>
</table>
<divclass="PageView">
<tablewidth="100%"border="0"cellspacing="0"cellpadding="4">
<trclass="Header">
<td>ID</td>
<td>描述</td>
<td>日期</td>
</tr>
<%
IfIsArray(arrRecordInfo)Then
Fori=0toUBound(arrRecordInfo,2)
%>
<tr>
<td><%=arrRecordInfo(0,i)%></td>
<td><%=arrRecordInfo(1,i)%></td>
<td><%=arrRecordInfo(2,i)%></td>
</tr>
<%
Next
EndIf
%>
</table>
</div>
<tablewidth="100%"border="0"cellspacing="0"cellpadding="4">
<tr>
<td><%=strPageInfo%></td>
</tr>
</table>
<tablewidth="100%"border="0"cellspacing="0"cellpadding="4">
<tr>
<tdalign="center"><%=getTimeOver(1)%></td>
</tr>
</table>
</body>
</html>