資料庫連線是 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