萬盛學電腦網

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

ASP Access 資料庫連線模組化設計與錯誤處理最佳實踐

ASP 資料庫連線元件化:從基礎連線到企業級錯誤處理

資料庫連線是 ASP 應用程式的心臟。本文展示如何將連線邏輯封裝為可重用模組,包含連線池概念、錯誤處理、交易支援與效能監控,建立可維護的資料存取層。

標準連線模組 (db.asp)

<%
' ==========================================
' 資料庫連線核心模組
' ==========================================
Option Explicit

' --- 設定區 ---
Private Const DB_PATH = "/data/app_data.accdb"
Private Const CONN_STR_TEMPLATE = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Jet OLEDB:Database Password={1};"
Private Const DB_PASSWORD = "your_strong_password_here"  ' 若無密碼留空

' --- 全域連線物件(單一請求內重用)---
Private g_conn
Private g_connOpened

' ==========================================
' 公開介面:取得連線物件
' ==========================================
Function GetConnection()
    If IsObject(g_conn) And g_connOpened Then
        ' 檢查連線狀態
        On Error Resume Next
        Dim test : test = g_conn.State
        If Err.Number = 0 And g_conn.State = 1 Then ' adStateOpen = 1
            On Error GoTo 0
            Set GetConnection = g_conn
            Exit Function
        End If
        On Error GoTo 0
    End If
    
    ' 建立新連線
    Set g_conn = Server.CreateObject("ADODB.Connection")
    g_conn.ConnectionString = Replace(CONN_STR_TEMPLATE, "{0}", Server.MapPath(DB_PATH))
    g_conn.ConnectionString = Replace(g_conn.ConnectionString, "{1}", DB_PASSWORD)
    g_conn.CommandTimeout = 30
    g_conn.ConnectionTimeout = 15
    
    On Error Resume Next
    g_conn.Open
    If Err.Number <> 0 Then
        Call LogDBError("Open", Err.Number, Err.Description, g_conn.ConnectionString)
        Err.Clear
        Set g_conn = Nothing
        Set GetConnection = Nothing
        Exit Function
    End If
    On Error GoTo 0
    
    g_connOpened = True
    Set GetConnection = g_conn
End Function

' ==========================================
' 公開介面:關閉連線(請求結束時呼叫)
' ==========================================
Sub CloseConnection()
    If IsObject(g_conn) And g_connOpened Then
        On Error Resume Next
        g_conn.Close
        On Error GoTo 0
        Set g_conn = Nothing
        g_connOpened = False
    End If
End Sub

' ==========================================
' 公開介面:執行查詢(回傳離線記錄集)
' ==========================================
Function ExecuteQuery(sql, params)
    Dim conn, rs, cmd, i
    Set conn = GetConnection()
    If Not IsObject(conn) Then
        Set ExecuteQuery = Nothing
        Exit Function
    End If
    
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = 1 ' adCmdText
    cmd.Prepared = True
    
    ' 參數綁定(防 SQL 注入)
    If IsArray(params) Then
        For i = 0 To UBound(params)
            ' 簡易參數類型推斷,實務建議明確指定類型
            Dim param : Set param = cmd.CreateParameter("@p" & i, 200, 1, 255, params(i)) ' adVarChar, adParamInput
            cmd.Parameters.Append param
        Next
    End If
    
    On Error Resume Next
    Set rs = cmd.Execute
    If Err.Number <> 0 Then
        Call LogDBError("ExecuteQuery", Err.Number, Err.Description, sql)
        Err.Clear
        Set rs = Nothing
    End If
    On Error GoTo 0
    
    ' 轉為離線記錄集(斷開連線,釋放資源)
    If IsObject(rs) And Not rs.EOF Then
        rs.CursorLocation = 3 ' adUseClient
        rs.Open rs.Source, rs.ActiveConnection, rs.CursorType, rs.LockType
        Set rs.ActiveConnection = Nothing
    End If
    
    Set cmd = Nothing
    Set ExecuteQuery = rs
End Function

' ==========================================
' 公開介面:執行非查詢(INSERT/UPDATE/DELETE)
' ==========================================
Function ExecuteNonQuery(sql, params)
    Dim conn, cmd, i, rowsAffected
    Set conn = GetConnection()
    If Not IsObject(conn) Then
        ExecuteNonQuery = -1
        Exit Function
    End If
    
    Set cmd = Server.CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql
    cmd.CommandType = 1
    cmd.Prepared = True
    
    If IsArray(params) Then
        For i = 0 To UBound(params)
            Dim param : Set param = cmd.CreateParameter("@p" & i, 200, 1, 255, params(i))
            cmd.Parameters.Append param
        Next
    End If
    
    On Error Resume Next
    cmd.Execute , rowsAffected
    If Err.Number <> 0 Then
        Call LogDBError("ExecuteNonQuery", Err.Number, Err.Description, sql)
        rowsAffected = -1
        Err.Clear
    End If
    On Error GoTo 0
    
    Set cmd = Nothing
    ExecuteNonQuery = rowsAffected
End Function

' ==========================================
' 內部:錯誤記錄
' ==========================================
Sub LogDBError(operation, errNum, errDesc, sql)
    ' 實務上寫入事件日誌、資料表或文字檔
    ' 這裡僅示範輸出到診斷
    Dim logEntry
    logEntry = Now & " | " & operation & " | Err:" & errNum & " | " & errDesc & " | SQL: " & Left(sql, 200)
    ' Response.Write ""  ' 開發時啟用
End Sub

' ==========================================
' 請求結束自動清理(在 Global.asa 中註冊)
' ==========================================
' Sub Request_OnEnd()
'     Call CloseConnection()
' End Sub
%>

使用範例

<!-- 頁面開頭引用 -->
<!--#include file="db.asp"-->

<%
' 查詢範例
Dim rs, sql, params
sql = "SELECT ArticleID, Title, PubDate FROM Articles WHERE CategoryID = ? AND IsPublished = ? ORDER BY PubDate DESC"
params = Array(5, True)
Set rs = ExecuteQuery(sql, params)

If Not rs Is Nothing And Not rs.EOF Then
    Do While Not rs.EOF
        Response.Write rs("Title") & "
" rs.MoveNext Loop rs.Close End If Set rs = Nothing ' 更新範例 sql = "UPDATE Articles SET ViewCount = ViewCount + 1 WHERE ArticleID = ?" params = Array(123) If ExecuteNonQuery(sql, params) > 0 Then Response.Write "瀏覽數更新成功" End If ' 頁面結尾確保關閉 Call CloseConnection() %>

進階擴充:交易支援

Sub BeginTrans()
    Dim conn : Set conn = GetConnection()
    If IsObject(conn) Then conn.BeginTrans
End Sub

Sub CommitTrans()
    Dim conn : Set conn = GetConnection()
    If IsObject(conn) Then
        On Error Resume Next
        conn.CommitTrans
        On Error GoTo 0
    End If
End Sub

Sub RollbackTrans()
    Dim conn : Set conn = GetConnection()
    If IsObject(conn) Then
        On Error Resume Next
        conn.RollbackTrans
        On Error GoTo 0
    End If
End Sub

效能監控指標

  • 連線建立耗時
  • 查詢執行耗時
  • 連線池使用率
  • 慢查詢閾值警報(> 1 秒)
copyright © 萬盛學電腦網 all rights reserved