ASP教程：自己写的数据库操作类

<%

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

%>