IDC资讯中心
通用分页类,以后写分页显示数据时就轻松多啦.直接调用此类,然后再execute即可以取得当前页的所有数据.
此类所做的工作是只取得当前页的数据,和总页数和总记录数等等数据.
asp代码:
<%
/*****************************分页显示类**************************
/* 作者：哇哇鱼
/* 日期：2004年11月18日
/* 作用：取得某一页的数据并返回给外部
/* 说明示例：
/* dim mypage=new pageclass
/* mypage.conn=conn 设置连接对象
/* mypage.pagesize=20 设置一页显示多少条数据 (默认为10条)
/* mypage.curpage=2 设置当前要显示的页码
/*
/* mypage.tablename="member" 设置表名
/* mypage.fields="id,membername,memberpass" 设置显示字段列表
/* mypage.condition="id>100" 设置查询条件
/* mypage.orderby="id desc" 设置排序条件（一定要设置该属性)
/* set pagers=mypage.execute 返回当前第2页的数据(recordset对象)，如果出错则返回nothing值
/*
/*以上的定义也可以用以下的方法：executeby("表名","字段列表","查询条件","排序条件")
/* set pagers=mypage.executeby("member","id,membername,memberpass","id>100","id desc")
/*
/* pagecount=mypage.pagecount 返回页码总数
/* recordcount=mypage.recordcount 返回记录总数
/* nextpage=mypage.nextpage 返回下页的页码
/* prepage=mypage.prepage 返回上一页的页码
/*****************************************************************
class pageclass
private connection 连接数据库的外部connection对象
private rs
private list_fields
private table_name
private query_where
private orderby_sql 字段排序语句部分
private page_count 返回当前查询的记录页总数
private page_size 设置一页显示多少条的记录
private cur_page 设置当前的页码
private record_count 返回当前查询的记录总数
/****************设置connection对象****************************
public property let conn(byref objconn)
set connection=objconn
end property
public property get conn()
set conn=connection
end property
/****************end******************************************
/****************设置查询sql语句*******************************
查询显示字段
public property let fields(byval value)
list_fields=value
end property
public property get fields()
fields=list_fields
end property
查询表名
public property let tablename(byval value)
table_name=value
end property
public property get tablename()
tablename=table_name
end property
查询条件
public property let condition(byval value)
query_where=value
end property
public property get condition()
condition=query_where
end property
*****************排序部分********************************************
value 语不用写上order by 。如： [object].orderby="id desc,posttime asc"
public property let orderby(byval value)
orderby_sql=value
end property
public property get orderby()
orderby=orderby_sql
end property
/****************end******************************************
/****************返回当前查询结果的总页数***********************
public property get pagecount()
pagecount=page_count
end property
public property get recordcount()
recordcount=record_count
end property
public property get nextpage()
if cur_page<page_count then
nextpage=cur_page+1
else
nextpage=page_count
end if
end property
public property get prepage()
if cur_page>1 then
prepage=cur_page-1
else
prepage=cur_page
end if
end property
/****************end******************************************
/****************设置一页显示的记录数***************************
public property let pagesize(byval value)
if not isnumeric(value) or value="" then
value=10
else
value=cint(value)
end if
if value<1 then value=10
page_size=value
end property
public property get pagesize()
pagesize=page_size
end property
设置当前的页码数**************************
public property let page(byval value)
if not isnumeric(value) or value="" then
value=1
else
value=clng(value)
end if
if value<1 then value=1
cur_page=value
end property
public property get page()
page=cur_page
end property
/****************end******************************************
private sub class_initialize
初始化recordset对象
page_size=10 默认一页为10条数据
curpage=1 默认当前为第一页
record_count=0
page_count=0
end sub
private sub class_terminate
call closerecordset
end sub
/***关闭数据库的连接*******
private sub closerecordset
on error resume next
if isobject(rs) then
rs.close
set rs=nothing
end if
on error goto 0
end sub
/**********执行查询返回对应页码的数据***********************************************
public function executeby(byval otablename,byval ofields,byval ocondition,byval oorderby)
table_name=otablename
list_fields=ofields
query_where=ocondtion
orderby_sql=oorderby
set executeby=execute()
end function
查询并返回当前curpage的页码记录
public function execute()
call closerecordset
on error resume next
dim tsql,topmod,swhere
if not isobject(connection) or table_name="" or orderby_sql="" then
set execute=nothing
record_count=0
page_count=0
exit function
end if
if trim(query_where)<>"" then
swhere="where "&query_where
else
swhere=""
end if
tsql="select count(*) from ["&table_name&"] "&swhere
record_count=connection.execute(tsql)(0) 获取记录总数
if err then
err.clear
set execute=nothing
record_count=0
page_count=0
exit function
end if
if record_count<1 then
set execute=nothing
record_count=0
page_count=0
exit function
end if
取得页的总数
if record_count mod page_size <>0 then
topmod=record_count mod page_size
page_count=fix(record_count/page_size)+1
if cur_page<page_count then
topmod=page_size
end if
else
topmod=page_size
page_count=fix(record_count/page_size)
end if
if cur_page>page_count then cur_page=page_count
if cur_page<1 then cur_page=1
if trim(list_fields)="" then list_fields="*"
tsql="select * from (select top "&topmod&" * from (select top "&(cur_page*page_size)&" "&list_fields&" from ["&table_name&"] "&swhere&" order by "&orderby_sql&") order by "&transformorder(orderby_sql)&")order by "&orderby_sql
set rs=connection.execute(tsql)
if err then
err.clear
set execute=nothing
record_count=0
page_count=0
exit function
end if
set execute=rs
end function
转换orderby的顺序 asc->desc desc->asc
private function transformorder(byval value)
if value="" then
transformorder=""
exit function
end if
dim orderarray,i,result,bystring,fields,inpos
orderarray=split(value,",") 分解每个字段值
for i=0 to ubound(orderarray)
if orderarray(i)<>"" then
inpos=instrrev(trim(orderarray(i))," ") 找出排序的顺序
if inpos<1 then 如果找不到则是asc排序
bystring="asc"
fields=orderarray(i)+" "
else
bystring=trim(mid(orderarray(i),inpos+1))
fields=left(orderarray(i),inpos)
if bystring<>"" then
bystring=ucase(bystring)
else
bystring="asc"
end if
end if
转换排序
if bystring="asc" then
bystring="desc"
else
bystring="asc"
end if
result=result+fields+bystring+","
end if
next
if result<>"" then result=left(result,len(result)-1)
transformorder=result
end function
end class
%>
示例代码:
sub show_list
dim page,pagers
page=request("page")
dim mypage
set mypage=new pageclass
mypage.conn=conn
mypage.pagesize=20
mypage.page=page
mypage.tablename="movie"
mypage.fields="*"
mypage.orderby="id asc"
set pagers=mypage.execute
set pagers=mypage.executeby("movie","*","","id asc")
if pagers is nothing then exit sub
do until pagers.eof
response.write " <tr bgcolor=""#fdfdfd"" style=""cursor:hand"" onmouseover=""this.style.background=#f3f3f3"" onmouseout=""this.style.background=#fdfdfd"">"
response.write " <td height=""20""><div align=""center"">"&pagers("id")&"</div></td>"
response.write " <td>"&pagers("name")&"</td>"
response.write " <td><a href="""&pagers("url")&"""><font color=#000000>"&pagers("url")&"</font></a></td>"
response.write " <td>"&pagers("type")&"</td>"
response.write " </tr>"
pagers.movenext
loop
pagers.close
pagecount=mypage.pagecount
page=mypage.page 取得当前正确的页码数
nextpage=mypage.nextpage
prepage=mypage.prepage
set pagers=nothing
set mypage=nothing
end sub
