在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

%>

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!