1 2 下一页 数据库操作类在网上一搜一大把，我这并不比那些好，只是是自己写的，用着更习惯。所以我这个类没有什么特别的地方，只是自己用着习惯罢了，至于效率等方面，欢迎赐教！！ 程序代码： <%Class dbClass ''------------------------------------------------------------------------- ''变量说明 ''conn-----------connection对象 ''strsql---------执行查询的语句 ''vTbName--------查询分页的表名 ''vPKey----------查询分页的表的主键 ''vPgFields------查询分页要显示的字段 ''vPgSize--------查询分页每页显示的记录数 ''VCurrPg--------查询分页显示的当前页 ''vConditions----查询分页的条件 ''vOrderBy-------查询分页的排序 ''------------------------------------------------------------------------- private conn,strsql,vTbName,vPKey,vPgFields,vPgSize,vCurrPg,vConditions,vOrderBy ''类的初始化 private Sub Class_Initialize() ''当是MS Sql数据库时设置以下两个变量 ''dim dbServer ''数据库服务器的名称或ip地址 ''dim dbname ''数据库的名字 dim dbPath ''若是Access数据库，此处设置其路径 dim dbUser ''数据库的登录用户名 dim dbPass ''数据库的登录密码 dim connstr dbPath = "/testASP/data/data.mdb" ''设置数据库路径 dbUser = "admin" dbPass = "123456" ''若是access,并且有密码 connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(dbPath) &_ ";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass ''若是access，并且没有密码 ''connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(dbPath) ''若是ms-sql数据库 ''connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_ '' "; Initial Catalog = " & dbname & "; Data Source = " & dbServer on error resume next set conn=server.CreateObject("adodb.connection") conn.open connstr errMsg "连接数据库" End Sub ''类结束 Private Sub Class_terminate() conn.close set conn=nothing End Sub ''------------------------------------------------------------------------- ''给类的变量设置值 ''------------------------------------------------------------------------- ''设置sql语句 Public Property Let sqlStr(Byval Values) strsql=Values End Property ''设置查询分页的表名 public property let tbName(Byval Values) vTbName=Values end property ''-------------------------------------------------------- ''设置查询分页的表的主键 public property let pKey(ByVal Values) vPKey=Values end property ''-------------------------------------------------------- ''设置显示的字段 public property let pgFields(ByVal Values) vPgFields=Values end property ''-------------------------------------------------------- ''设置每页显示的记录数 public property let pgSize(ByVal Values) vPgSize=Values end property ''--------------------------------------------------------- ''设置当前显示的页数 public property let currPg(ByVal Values) vCurrPg=Values end property ''-------------------------------------------------------- ''设置查询的条件 public property let conditions(ByVal Values) if Len(Values)>0 then vConditions=" where "&Values else vConditions=" where 1=1 " end if end property ''------------------------------------------------------- ''设置查询的排序 public property let orderBy(ByVal Values) if Len(Values)>0 then vOrderBy=" order by "&Values else vOrderBy=Values end if end property ''------------------------------------------------------------- ''得到记录总数 public property get vRsCount() if vCurrPg=1 then sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions set rsc=server.CreateObject("adodb.recordset") rsc.open sqlc,conn,0,1 RsNum=rsc("IdCount") rsc.close set rsc=nothing if RsNum>0 then response.Cookies("iRecord")=RsNum vRsCount=RsNum else vRsCount=0 end if else vRsCount=request.Cookies("iRecord") end if end property ''得到总页数 public property get vPgCount() iRsCount2=vRsCount() if iRsCount2 mod vPgSize =0 then vPgCount=int(iRsCount2/vPgSize) else vPgCount=int(iRsCount2/vPgSize)+1 end if end property ''查询数据库 Public Function rsDB() on error resume next ''简单的查询出结果 '' set rsDB = Server.CreateObject("ADODB.RecordSet") '' rsDB.Open strsql,conn,1,3 Set rsDB=conn.Execute(strsql) errMsg "查询数据库" End Function ''添加，更新，删除数据库记录 public Function upDB() on error resume next conn.execute(strsql) errMsg "编辑数据库记录" end Function ''------------------------------------------------------------------------- ''用来实现分页的记录集函数 public function pageRs() on error resume next dim startRs startRs=(vCurrPg-1)*vPgSize ''------------------------------------------------------------------------------------------- ''使用此语句的话要根据参数修改代码，具体的是若排序为asc则<改为>,min改为max '' if startRs=0 then '' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy '' else '' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" < " '' strsql=strsql&"(select min("&vPKey&") from (select top "&startRs&" "&vPKey&" from " '' strsql=strsql&vTbName&" "&vConditions&" "&vOrderBy&") as idTable) "&vOrderBy '' end if ''--------------------------------------------------------------- if startRs=0 then strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy else strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" not " strsql=strsql&"in (select top "&startRs&" "&vPKey&" from "&vTbName&" "&vConditions&" "&vOrderBy strsql=strsql&") "&vOrderBy end if ''------------------------------------------------------------------- set pageRs=server.CreateObject("adodb.recordset") pageRs.open strsql,conn,0,1 errMsg "记录分页" end function ''------sql用存储过程分页------------------------------------------------------ public function sqlPage() on error resume next Set sqlPage=server.CreateObject("Adodb.RecordSet") Set Cm=Server.CreateObject("Adodb.Command") Cm.CommandType = 4 Cm.ActiveConnection = conn Cm.CommandText="sp_Util_Page" Cm.parameters(1) = vPgFields Cm.parameters(2) = vTbName Cm.parameters(3) = vConditions Cm.parameters(4) = vOrderBy Cm.parameters(5) = vPKey Cm.parameters(6) = vCurrPg Cm.parameters(7) = vPgSize Cm.parameters(8) = vRsCount() Cm.parameters(9) = "" sqlPage.CursorLocation = 3 sqlPage.LockType = 1 sqlPage.Open Cm errMsg "记录分页" end function ''---------------------------------------------------------------------------- ''关闭记录集objRs ''---------------------------------------------------------------------------- Public Function cRs(ByVal ObjRs) ObjRs.close() Set ObjRs = Nothing End Function ''----------------------分页的页码导航--------------------------------------- public function pageNav() iRsCount=vRsCount()''总记录数 mypage=vCurrPg''当前页数 PgCount=vPgCount()''总页数 prePage=mypage-1 if prePage<1 then prePage=1 end if nextPage=mypage+1 if nextPage>PgCount then nextPage=PgCount end if pagestr="<div id=""fy""><span id=""rpc"">总共有"&iRsCount&"条记录 "&mypage&"/"&PgCount&"</span>" pagestr=pagestr&"<a href=''?currpage=1'' class=''aW''>首页</a><a href=''?currpage="&prePage&"'' class=''aW''>前一页</a>" if (mypage-1) mod 4=0 then firstPage=mypage elseif int((mypage-1)/4)=0 then firstPage=1 else firstPage=int((mypage-1)/4)*4+1 end if endPage=firstPage+4 astr="" for i=firstPage to endPage astr=astr&"<a href=''?currpage="&i&"''" if Cstr(mypage)=Cstr(i) then astr=astr&" id=''currP''" end if astr=astr&">"&i&"</a>" if i>PgCount-1 then exit for next astr=astr&"<a href=''?currpage="&nextPage&"'' class=''aW''>后一页</a><a href=''?currpage="&PgCount&"'' class=''aW''>尾页</a></div>" pagestr=pagestr&astr pageNav=pagestr end function ''输出带分页功能的table Function showTb(ByVal TbTil) set rsTb=pageRs()''若是存储过程就调用sqlPage() tbRs= rsTb.getrows() cRs(rsTb) iTblRow=Ubound(tbRs,2) iTblCol=Ubound(TbTil) tbStr="<table border=''0'' cellspacing=''0'' cellpadding=''0''><tbody>" for r1=0 to iTblCol tr1=tr1&"<td width=''"&split(TbTil(r1),"|")(1)&"''>"&split(TbTil(r1),"|")(0)&"</td>" next tr1="<tr>"&tr1&"</tr>" for ri=0 to iTblRow for ci=0 to iTblCol td=td&"<td width=''"&split(TbTil(ci),"|")(1)&"''>"&tbRs(ci,ri)&"</td>" next tr=tr&"<tr>"&td&"</tr>" td=null next TbTil=null tbRs=null response.Write(tbStr&tr1&tr&"<tr><td colspan=''"&iTblCol+1&"''>"&pageNav()&"</td></tr></tbody></table>") tbStr=null tr1=null tr=null End Function ''打印sql语句，以便语句有错误时检查 Public Sub prnSql() response.Write(strsql) End Sub ''------------------------------------------------------------------------- ''容错函数 ''------------------------------------------------------------------------- Private Function errMsg(errMsg) If Err.number<>0 Then ''出现问题可利用此处代码打印出描述信息，方便调试。可注释掉 response.Write(Cstr(Err.description)&"<br>") Err.Clear Response.Write "<font color=''#FF0000''>"&errMsg&"出错</font>" ''注释 Response.End() End If End Function ''------------------------------------------------------------------------- ''容错函数结束 ''------------------------------------------------------------------------- End Class %> (责任编辑：admin) |
