手机
当前位置:查字典教程网 >编程开发 >ASP教程 >Access 2000 数据库 80 万记录通用快速分页类
Access 2000 数据库 80 万记录通用快速分页类
摘要:代码本人优化过,测试通过主要思路:用一条语句统计(Count)出记录数(而不在查询时获得RecordCount属性),缓存在Cookies中...

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

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

Setconn=Server.CreateObject("Adodb.Connection")

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=sintRecordCountsintPageSize

Else

sintPageMax=sintRecordCountsintPageSize+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.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

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>

【Access 2000 数据库 80 万记录通用快速分页类】相关文章:

数据库记录的删除,delete好还是update好?

asp中通过getrows实现数据库记录分页的一段代码

access数据库的一些少用操作,ASP,创建数据库文件,创建表,创建字段,ADOX

sql 存储过程分页

ASP经典分页类

asp的通用数据分页类

在ASP中使用均速分页法提高分页速度

在MsSql、Access两种数据库中插入记录后马上得到自动编号的ID值

access 数据连接程序

ASP连接11种数据库语法总结

精品推荐
分类导航