類別設計目標
- 封裝連線、命令、記錄集生命週期
- 內建分頁屬性
- 統一錯誤處理與資源釋放
- 支援參數化查詢字串組合
核心程式碼
<%
' 輔助子程序:釋放物件
Sub DeleteObject(ByRef obj)
Set obj = Nothing
obj = Null
End Sub
Class CDB
Private m_oConn, m_oRS
Private m_pageIndex, m_pageSize, m_pageCount, m_recordCount
Private strError
Private m_QueryString
Private Sub Class_Initialize()
m_oConn = Null : m_oRS = Null
m_pageIndex = 1 : m_pageSize = 10 : m_pageCount = 1 : m_recordCount = 0
Call Connect()
End Sub
' 依路徑決定連線字串(前台/後台不同資料庫)
Private Property Get GetConnectionString()
If InStr(LCase(Request.ServerVariables("SCRIPT_NAME")), "/admin/") > 0 Then
GetConnectionString = "Provider=microsoft.jet.oledb.4.0;data source=" & Server.MapPath("DataBasedata.mdb")
Else
GetConnectionString = "Provider=microsoft.jet.oledb.4.0;data source=" & Server.MapPath("AdminDataBasedata.mdb")
End If
End Property
' 分頁屬性
Public Property Let SetPageSize(num)
If Not IsNumeric(num) Then Exit Property
num = CInt(num)
If num > 0 Then m_pageSize = num
End Property
Public Property Let SetPageIndex(num)
If Not IsNumeric(num) Then Exit Property
num = CInt(num)
If num > 0 Then m_pageIndex = num
End Property
' 查詢字串累加
Public Property Let AppendQueryString(paraName, paraValue)
m_QueryString = m_QueryString & "&" & paraName & "=" & Server.URLEncode(paraValue)
End Property
' 預設屬性:回傳錯誤訊息
Public Default Function ToString()
ToString = strError
End Function
' 建立連線
Private Sub Connect()
Set m_oConn = Server.CreateObject("ADODB.Connection")
m_oConn.ConnectionString = GetConnectionString
m_oConn.Open()
If Err.Number <> 0 Then
strError = Err.Description
Response.Write "Sorry, failed to connect Database!"
Err.Clear
DeleteObject m_oConn
Response.End()
End If
End Sub
' 關閉物件
Private Sub Close(ByRef DBObject)
If DBObject.State = 1 Then DBObject.Close()
End Sub
' 執行純量查詢
Public Function ExecuteScalar(strSql)
ExecuteScalar = m_oConn.Execute(strSql)(0)
End Function
' 執行無回傳 SQL
Public Sub Execute(ByVal strSql)
If Not IsObject(m_oConn) Then Exit Sub
m_oConn.Execute(strSql)
End Sub
' 開啟 Recordset(內部使用)
Private Function OpenRS()
OpenRS = False
If Not IsObject(m_oConn) Or m_oConn.State = 0 Then Exit Function
If IsObject(m_oRS) Then Close m_oRS
Set m_oRS = Server.CreateObject("ADODB.Recordset")
m_oRS.CursorLocation = 3 ' adUseClient
On Error Resume Next
m_oRS.Open m_QueryString, m_oConn, 1, 1
If Err.Number = 0 Then OpenRS = True
On Error GoTo 0
End Function
' ... 其餘分頁、GetRows、釋放資源等方法略
End Class
%>
使用範例
<%
Set db = New CDB
db.SetPageSize = 20
db.SetPageIndex = Request("page")
db.AppendQueryString "keyword", Request("kw")
db.AppendQueryString "cate", Request("cate")
If db.OpenRS() Then
' 顯示資料
Do While Not db.m_oRS.EOF
Response.Write db.m_oRS("title") & "<br>"
db.m_oRS.MoveNext
Loop
' 顯示分頁導覽
Response.Write "第 " & db.m_pageIndex & " 頁,共 " & db.m_pageCount & " 頁"
End If
' 類別終結時自動釋放連線
%>
設計亮點
- 自動連線管理:
Class_Initialize 開啟,Class_Terminate 關閉(需補全)。
- 分頁內建:
PageSize、PageIndex、PageCount、RecordCount 一站式取得。
- 查詢字串累加:
AppendQueryString 方便建構複雜條件。
- 錯誤集中:
strError 統一記錄,預設屬性直接輸出錯誤。