Access 2000 数据库 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 字段作了索引也没用. :(
<%
Dim intDateStart
intDateStart = Timer()
Rem ## 打开数据库连接
Rem #################################################################
function f__OpenConn()
Dim strDbPath
Dim connstr
strDbPath = "fenye/db.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 = "[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
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>
相关推荐
-
asp.net access web.config denied
最近有朋友发现access web.config denied出现这个问题,大家可以检查下aspnet帐号的权限.
-
ASP.NET 连接ACCESS数据库的简单方法
index.aspx 复制代码 代码如下: <%@ Page Language="C#" %><%@ import Namespace="System.Data" %><%@ import Namespace="System.Data.OleDb" %><script runat="server"> // Insert page code here // voi
-
asp.net access添加返回自递增id的实现方法第1/3页
先看界面: 添加后数据库: 而所要执行的语句: 复制代码 代码如下: string name_ = this.tbxUseName.Text.Trim(); string webname_ = this.tbxWebName.Text.Trim(); string url_ = this.tbxUrl.Text.Trim(); AddFieldItem("news_Title", name_); AddFieldItem("news_So
-
asp.net(C#) Access 数据操作类
复制代码 代码如下: using System; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Xml; using System.Collections; namespace Website.Command { /// <summary> /// WSplus 的摘要说明. /// </summary> public class AccessClass :
-
Asp.net在线备份、压缩和修复Access数据库示例代码
1.问题的提出 在设计中小型Web应用程序时,可以选择Microsoft Accesss为数据库.在数据库的使用过程中经常性进行增加和删除操作.事实上,Microsoft Access并不能有效地释放已分配的但被删除的对象空间,这将意味着即使你删除了一个对象,而这个对象仍然占据着数据库的空间,使得数据库越来越大.不但占用不必要的空间,而且降低了数据库的效率.特别在虚拟站点上的问题尤为突出.因此对Access数据库进行压缩瘦身很有实际意义. 虽然Access数据库自身具有"压缩和修复数据库&quo
-
asp.net实现access数据库分页的方法
asp.net操作access数据库是常见的数据库操作应用,本文就来实例讲解一下asp.net实现access数据库分页的方法.希望对大家的asp.net程序设计能有所帮助. 具体实例代码如下: <divclass="page"id="ctrlRecordPage"> 总<asp:LabelID="Zpage"runat="server"Text="1"></asp:Label
-
asp.net下Oracle,SQL Server,Access万能数据库通用类
复制代码 代码如下: using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; using System.Data.OracleClient; using System.Configuration; using System.Reflection; nam
-
浅谈mssql access数据库 top分页方法
但是有利也有弊,它要求排序字段必须构成唯一记录,且select后的字段列表中,不允许出现与排序字段同名的字段.虽然sql2k5及以上版本已经提供了rownum()来进行分页处理,但是使用sql2k进行开发的还是较多的 复制代码 代码如下: uusing system.collections.generic;sing system;using system.text;/// <summary>/// 构造分页后的sql语句/// </summary>public static cla
-
一个简答的Access下的分页asp.net代码
复制代码 代码如下: public void listArticle() { CmsArticle arObj = new CmsArticle(); DataSet ds = arObj.listArticle(); if (ds != null) { PagedDataSource pds = new PagedDataSource(); pds.DataSource = ds.Tables[0].DefaultView; pds.AllowPaging = true; pds.PageSi
-
asp.net 数据库备份还原(sqlserver+access)
/********************************************************************************** * * 功能说明:备份和恢复SQL Server数据库 * 作者: 刘功勋; * 版本:V0.1(C#2.0);时间:2007-1-1 * 当使用SQL Server时,请引用 COM组件中的,SQLDMO.dll组件 * 当使用Access中,请浏览添加引用以下两个dll * 引用C:\Program Files\Common
-
asp.net访问Access数据库溢出错误
写了如下的一个方法来返回数据操作影响的行数:如下 复制代码 代码如下: private int GetReturnValue(string sStr, string conn) { OleDbConnection odbconn = AccessHelp(conn); OleDbCommand odbcmd = new OleDbCommand(sStr, odbconn); return odbcmd.ExecuteNonQuery(); } 用下面的一个方法来调用这个类: 复制代码 代码如下
-
asp.net和asp下ACCESS的参数化查询
今天我就把我用ACCESS参数化查询的一些方法和经验和大家分享 希望对大家有所启发,有写的不对的地方希望高手们多多指教 ASP.NET 用OleDbCommand的new OleDbParameter创建参数货查询 ASP用Command的CreateParameter 方法创建参数化查询 (SQL储存过程查询也是用这个方法建立的) ASP.NET C#语法 OleDbParameter parm = new OleDbParameter(Name, Type, Direction, Size,
-
asp.net中获取新增加记录的ID Access版
这里参考了Erist.Protal里的代码 复制代码 代码如下: /// <summary> /// 增加新的文章 /// </summary> /// <param name="ArticleTitle"></param> /// <param name="Author"></param> /// <param name="ArticleFrom"></p