ASP教程：自己寫的數據庫操作類

　　程序代碼

　　以下為引用的內容
<%
Class dbClass

    變量說明
    connconnection對象
    strsql執行查詢的語句
    vTbName查詢分頁的表名
    vPKey查詢分頁的表的主鍵
    vPgFields查詢分頁要顯示的字段
    vPgSize查詢分頁每頁顯示的記錄數
    vCurrPg查詢分頁顯示的當前頁
    vConditions查詢分頁的條件
    vOrderBy查詢分頁的排序

    private connstrsqlvTbNamevPKeyvPgFieldsvPgSizevCurrPgvConditionsvOrderBy
    類的初始化
    private Sub Class_Initialize()
        當是MS Sql數據庫時設置以下兩個變量
        dim dbServer            數據庫服務器的名稱或ip地址
        dim dbname              數據庫的名字
        dim dbPath              若是Access數據庫此處設置其路徑
        dim dbUser              數據庫的登錄用戶名
        dim dbPass              數據庫的登錄密碼
        dim connstr
        dbPath = "/testasp/data/datamdb" 設置數據庫路徑
        dbUser = "admin"
        dbPass = ""
        若是access並且有密碼
        connstr = "Provider=MicrosoftJetOLEDB;Data Source=" & ServerMapPath(dbPath) &_
         ";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass
        若是access並且沒有密碼
        connstr = "Provider = MicrosoftJetOLEDB;Data Source = " & ServerMapPath(dbPath)
        若是mssql數據庫
        connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_
        "; Initial Catalog = " & dbname & "; Data Source = " & dbServer
        on error resume next
        set conn=serverCreateObject("adodbconnection")
        connopen connstr
        errMsg "連接數據庫"
    End Sub
    類結束
    Private Sub Class_terminate()
        connclose
        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)> then
    vConditions=" where "&Values
    else
    vConditions=" where = "
    end if
    end property
   
    設置查詢的排序
    public property let orderBy(ByVal Values)
    if Len(Values)> then
    vOrderBy=" order by "&Values
    else
    vOrderBy=Values
    end if
    end property   
   
    得到記錄總數
    public property get vRsCount()
    if vCurrPg= then
    sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions
    set rsc=serverCreateObject("adodbrecordset")
    rscopen sqlcconn
    RsNum=rsc("IdCount")
    rscclose
    set rsc=nothing
    if RsNum> then
    responseCookies("iRecord")=RsNum
    vRsCount=RsNum
    else
    vRsCount=
    end if
    else
    vRsCount=requestCookies("iRecord")
    end if
    end property
    得到總頁數
    public property get vPgCount()
    iRsCount=vRsCount()
    if iRsCount mod vPgSize = then
    vPgCount=int(iRsCount/vPgSize)
    else
    vPgCount=int(iRsCount/vPgSize)+
    end if
    end property
查詢數據庫
    Public Function rsDB()
    on error resume next
    簡單的查詢出結果
     set rsDB = ServerCreateObject("ADODBRecordSet")
     rsDBOpen strsqlconn
    Set rsDB=connExecute(strsql)
    errMsg "查詢數據庫"
    End Function
添加更新刪除數據庫記錄
    public Function upDB()
    on error resume next
    connexecute(strsql)
    errMsg "編輯數據庫記錄"
    end Function

用來實現分頁的記錄集函數
    public function pageRs()
    on error resume next
    dim startRs
    startRs=(vCurrPg)*vPgSize
   
    使用此語句的話要根據參數修改代碼具體的是若排序為asc則<改為>min改為max
    if startRs= 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= 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=serverCreateObject("adodbrecordset")
    pageRsopen strsqlconn
    errMsg "記錄分頁"
    end function
sql用存儲過程分頁
public function sqlPage()
on error resume next
Set sqlPage=serverCreateObject("AdodbRecordSet")
Set Cm=ServerCreateObject("AdodbCommand")
CmCommandType =
CmActiveConnection = conn
CmCommandText="sp_Util_Page"
Cmparameters() = vPgFields
Cmparameters() = vTbName
Cmparameters() = vConditions
Cmparameters() = vOrderBy
Cmparameters() = vPKey
Cmparameters() = vCurrPg
Cmparameters() = vPgSize
Cmparameters() = vRsCount()
Cmparameters() = ""
sqlPageCursorLocation =
sqlPageLockType =
sqlPageOpen Cm
errMsg "記錄分頁"
end function

關閉記錄集objRs

    Public Function cRs(ByVal ObjRs)
    ObjRsclose()
    Set ObjRs = Nothing
    End Function
分頁的頁碼導航
public function pageNav()
iRsCount=vRsCount()總記錄數
mypage=vCurrPg當前頁數
PgCount=vPgCount()總頁數
prePage=mypage
if prePage< then
prePage=
end if
nextPage=mypage+
if nextPage>PgCount then
nextPage=PgCount
end if
pagestr="<div id=""fy""><span id=""rpc"">總共有"&iRsCount&"條記錄  "&mypage&"/"&PgCount&"</span>"
pagestr=pagestr&"<a class=aW>首頁</a><a ?currpage="&prePage&" class=aW>前一頁</a>"
if (mypage) mod = then
firstPage=mypage
elseif int((mypage)/)= then
firstPage=
else
firstPage=int((mypage)/)*+
end if
endPage=firstPage+
astr=""
for i=firstPage to endPage
astr=astr&"<a ?currpage="&i&""
if Cstr(mypage)=Cstr(i) then
astr=astr&" id=currP"
end if
astr=astr&">"&i&"</a>"
if i>PgCount then exit for
next
astr=astr&"<a ?currpage="&nextPage&" class=aW>後一頁</a><a ?currpage="&PgCount&" class=aW>尾頁</a></div>"
pagestr=pagestr&astr
pageNav=pagestr
end function
輸出帶分頁功能的table
Function showTb(ByVal TbTil)
    set rsTb=pageRs()若是存儲過程就調用sqlPage()
    tbRs= rsTbgetrows()
    cRs(rsTb)
    iTblRow=Ubound(tbRs)
    iTblCol=Ubound(TbTil)
    tbStr="<table border= cellspacing= cellpadding=><tbody>"
    for r= to iTblCol
      tr=tr&"<td width="&split(TbTil(r)"|")()&">"&split(TbTil(r)"|")()&"</td>"
    next
    tr="<tr>"&tr&"</tr>"
    for ri= to iTblRow
      for ci= to iTblCol
        td=td&"<td width="&split(TbTil(ci)"|")()&">"&tbRs(ciri)&"</td>"
      next
      tr=tr&"<tr>"&td&"</tr>"
      td=null
    next
    TbTil=null
    tbRs=null
    responseWrite(tbStr&tr&tr&"<tr><td colspan="&iTblCol+&">"&pageNav()&"</td></tr></tbody></table>")
    tbStr=null
    tr=null
    tr=null
End Function
打印sql語句以便語句有錯誤時檢查
    Public Sub prnSql()
        responseWrite(strsql)
    End Sub
   
容錯函數

     Private Function errMsg(errMsg)
     If Errnumber<> Then
     出現問題可利用此處代碼打印出描述信息方便調試可注釋掉
     responseWrite(Cstr(Errdescription)&"<br>")
     ErrClear
     ResponseWrite "<font color=#FF>"&errMsg&"出錯</font>"  注釋
     ResponseEnd()
     End If
     End Function

容錯函數結束

End Class
%>

From:http://tw.wingwit.com/Article/program/net/201311/14424.html