設計目標
- 封裝連線字串、開啟/關閉、錯誤處理
- 提供統一介面: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 ""
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
%>