萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 自訂資料庫操作類別:DBClass 封裝連線、查詢、分頁、交易

設計目標

  • 封裝連線字串、開啟/關閉、錯誤處理
  • 提供統一介面:ExecuteSQL、Query、GetPage、Transaction
  • 支援 Access / SQL Server 切換
  • 內建分頁屬性 (PageSize, CurrPage, RecordCount, PageCount)

完整類別代碼:dbClass.asp

<%@ Language=VBScript CodePage=65001 %>
<%
Class DBClass
    Private conn, connStr
    Private sql, tbName, pKey, pgFields, pgSize, currPg, conditions, orderBy
    Private errMsg, inTrans
    
    ' --- 初始化 ---
    Private Sub Class_Initialize()
        ' 從 Application/設定檔讀取連線字串
        connStr = Application("DB_ConnStr")
        If connStr = "" Then
            ' 預設 Access 範例
            connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/app.mdb") & ";"
        End If
        Set conn = Server.CreateObject("ADODB.Connection")
        On Error Resume Next
        conn.Open connStr
        If Err.Number <> 0 Then
            errMsg = "連線失敗: " & Err.Description
            Err.Clear
        End If
        On Error GoTo 0
        inTrans = False
    End Sub
    
    ' --- 終結釋放 ---
    Private Sub Class_Terminate()
        If inTrans Then Me.RollbackTrans
        If Not conn Is Nothing Then
            If conn.State = 1 Then conn.Close
            Set conn = Nothing
        End If
    End Sub
    
    ' --- 公開屬性 ---
    Public Property Let SQL(v) : sql = v : End Property
    Public Property Let TableName(v) : tbName = v : End Property
    Public Property Let PrimaryKey(v) : pKey = v : End Property
    Public Property Let PageFields(v) : pgFields = v : End Property
    Public Property Let PageSize(v) : pgSize = v : End Property
    Public Property Let CurrentPage(v) : currPg = v : End Property
    Public Property Let WhereClause(v) 
        If Len(v) > 0 Then conditions = " WHERE " & v Else conditions = " WHERE 1=1 "
    End Property
    Public Property Let OrderBy(v)
        If Len(v) > 0 Then orderBy = " ORDER BY " & v Else orderBy = ""
    End Property
    
    Public Property Get LastError() : LastError = errMsg : End Property
    Public Property Get Connection() : Set Connection = conn : End Property
    
    ' --- 核心方法 ---
    ' 執行無回傳 SQL (INSERT/UPDATE/DELETE/DDL)
    Public Function ExecuteSQL(sqlText)
        On Error Resume Next
        errMsg = ""
        conn.Execute sqlText
        If Err.Number <> 0 Then
            errMsg = "ExecuteSQL: " & Err.Description
            ExecuteSQL = False
        Else
            ExecuteSQL = True
        End If
        On Error GoTo 0
    End Function
    
    ' 查詢回傳 Recordset (呼叫端需自行 Close)
    Public Function Query(sqlText)
        Dim rs
        Set rs = Server.CreateObject("ADODB.Recordset")
        On Error Resume Next
        rs.Open sqlText, conn, 1, 1 ' adOpenForwardOnly, adLockReadOnly
        If Err.Number <> 0 Then
            errMsg = "Query: " & Err.Description
            Set Query = Nothing
        Else
            Set Query = rs
        End If
        On Error GoTo 0
    End Function
    
    ' 取得單一值 (Scalar)
    Public Function GetScalar(sqlText)
        Dim rs, val
        Set rs = Me.Query(sqlText)
        If Not rs Is Nothing And Not rs.EOF Then
            val = rs(0)
            If IsNull(val) Then val = ""
        Else
            val = ""
        End If
        If Not rs Is Nothing Then rs.Close: Set rs = Nothing
        GetScalar = val
    End Function
    
    ' --- 分頁查詢 ---
    Private Function BuildPageSQL()
        Dim flds, baseSQL, countSQL
        flds = IIf(Len(pgFields) > 0, pgFields, "*")
        baseSQL = "SELECT " & flds & " FROM " & tbName & conditions & orderBy
        countSQL = "SELECT COUNT(" & pKey & ") AS Cnt FROM " & tbName & conditions
        BuildPageSQL = Array(baseSQL, countSQL)
    End Function
    
    Public Function GetPage()
        Dim sqlArr, countSQL, rs, total, pageCount, startRec, i, html
        sqlArr = Me.BuildPageSQL()
        countSQL = sqlArr(1)
        
        ' 取總筆數 (快取於 Session)
        If currPg = 1 Then
            total = CLng(Me.GetScalar(countSQL))
            Session("db_total_" & tbName) = total
        Else
            total = CLng(Session("db_total_" & tbName))
            If total = 0 Then total = CLng(Me.GetScalar(countSQL))
        End If
        
        If total = 0 Then
            Set GetPage = Nothing
            Exit Function
        End If
        
        pageCount = (total \ pgSize) + IIf(total Mod pgSize > 0, 1, 0)
        If currPg < 1 Then currPg = 1
        If currPg > pageCount Then currPg = pageCount
        
        ' 使用 AbsolutePage (需 CursorLocation = adUseClient)
        Dim rsPage
        Set rsPage = Server.CreateObject("ADODB.Recordset")
        rsPage.CursorLocation = 3 ' adUseClient
        rsPage.Open sqlArr(0), conn, 3, 1 ' adOpenStatic, adLockReadOnly
        rsPage.PageSize = pgSize
        rsPage.AbsolutePage = currPg
        
        ' 轉為二維陣列回傳 (脫離連線)
        Dim dataArr()
        If Not rsPage.EOF Then
            ReDim dataArr(rsPage.Fields.Count - 1, pgSize - 1)
            i = 0
            Do While Not rsPage.EOF And i < pgSize
                For j = 0 To rsPage.Fields.Count - 1
                    dataArr(j, i) = rsPage(j)
                Next
                rsPage.MoveNext
                i = i + 1
            Loop
        End If
        rsPage.Close: Set rsPage = Nothing
        
        ' 回傳 Dictionary 包含資料與分頁資訊
        Dim result : Set result = Server.CreateObject("Scripting.Dictionary")
        result.Add "Data", dataArr
        result.Add "TotalRecords", total
        result.Add "PageCount", pageCount
        result.Add "CurrentPage", currPg
        result.Add "PageSize", pgSize
        Set GetPage = result
    End Function
    
    ' --- 交易控制 ---
    Public Sub BeginTrans()
        On Error Resume Next
        conn.BeginTrans
        inTrans = (Err.Number = 0)
        If Not inTrans Then errMsg = "BeginTrans: " & Err.Description
        On Error GoTo 0
    End Sub
    
    Public Sub CommitTrans()
        On Error Resume Next
        conn.CommitTrans
        inTrans = False
        If Err.Number <> 0 Then errMsg = "CommitTrans: " & Err.Description
        On Error GoTo 0
    End Sub
    
    Public Sub RollbackTrans()
        On Error Resume Next
        conn.RollbackTrans
        inTrans = False
        If Err.Number <> 0 Then errMsg = "RollbackTrans: " & Err.Description
        On Error GoTo 0
    End Sub
    
    ' --- 輔助 ---
    Private Function IIf(c, t, f) : If c Then IIf = t Else IIf = f : End Function
End Class
%>

使用範例

基本查詢

<!--#include file="dbClass.asp"-->
<%
Dim db, rs
Set db = New DBClass
If db.LastError <> "" Then Response.Write "DB錯誤: " & db.LastError : Response.End

db.SQL = "SELECT TOP 10 * FROM News WHERE IsPub=1 ORDER BY PubDate DESC"
Set rs = db.Query(db.SQL)
If Not rs Is Nothing Then
    Do While Not rs.EOF
        Response.Write rs("Title") & "
" rs.MoveNext Loop rs.Close End If %>

分頁顯示

<%
Dim db, pageData, dataArr, i, j
Set db = New DBClass
db.TableName = "News"
db.PrimaryKey = "NewsID"
db.PageFields = "NewsID, Title, PubDate"
db.PageSize = 20
db.CurrentPage = CInt(Request("page"))
If db.CurrentPage = 0 Then db.CurrentPage = 1
db.WhereClause = "IsPub=1"
db.OrderBy = "PubDate DESC"

Set pageData = db.GetPage()
If Not pageData Is Nothing Then
    dataArr = pageData("Data")
    For i = 0 To UBound(dataArr, 2)
        Response.Write "

" & dataArr(1, i) & "

" ' Title Response.Write "" & dataArr(2, i) & "
" ' PubDate Next ' 分頁導航 Response.Write "
" If pageData("CurrentPage") > 1 Then Response.Write "首頁 上頁 " End If Response.Write "第 " & pageData("CurrentPage") & " / " & pageData("PageCount") & " 頁 " If pageData("CurrentPage") < pageData("PageCount") Then Response.Write " 下頁 尾頁" End If Response.Write "
" End If %>

交易範例:轉帳

<%
Dim db
Set db = New DBClass
db.BeginTrans
If db.ExecuteSQL("UPDATE Accounts SET Balance=Balance-100 WHERE ID=1") And _
   db.ExecuteSQL("UPDATE Accounts SET Balance=Balance+100 WHERE ID=2") Then
    db.CommitTrans
    Response.Write "轉帳成功"
Else
    db.RollbackTrans
    Response.Write "轉帳失敗: " & db.LastError
End If
%>
copyright © 萬盛學電腦網 all rights reserved