萬盛學電腦網

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

ASP 資料庫操作類別 CDB 完整實作

類別設計目標

  • 封裝連線、命令、記錄集生命週期
  • 內建分頁屬性
  • 統一錯誤處理與資源釋放
  • 支援參數化查詢字串組合

核心程式碼

<%
' 輔助子程序:釋放物件
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
' 類別終結時自動釋放連線
%>

設計亮點

  1. 自動連線管理:Class_Initialize 開啟,Class_Terminate 關閉(需補全)。
  2. 分頁內建:PageSize、PageIndex、PageCount、RecordCount 一站式取得。
  3. 查詢字串累加:AppendQueryString 方便建構複雜條件。
  4. 錯誤集中:strError 統一記錄,預設屬性直接輸出錯誤。
copyright © 萬盛學電腦網 all rights reserved