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

%>