自己封裝的資料庫訪問類，支持Access，采用MIT協議開源。
自從前幾天嘗試后發現VB的類模塊我還沒有很好的利用起來，加上我以前的代碼全域變數嚴重（大多數是本科一年級的時候寫的）濫用，所以我這兩天加緊重構了下我的一個專案的代碼，并且用VB6 的類模塊封裝了資料庫的訪問。方便一堆一堆的詢問者。
uj5u.com熱心網友回復：
代碼地址：https://code.csdn.net/snippets/576568uj5u.com熱心網友回復：
代碼授權：MIT代碼：
'授權協議：MIT
'創建人：孫瑞
Private m_Res As ADODB.Recordset
Private m_Conn As ADODB.Connection
Private m_Command As ADODB.Command
Private m_ConnString As String
Private m_FilePath As String
Private m_Params As New Collection
'規定：每個函式執行完成后，都必須清空m_Command，并且創建一個空的物件
'【類初始化、釋放】
Private Sub Class_Initialize()
End Sub
Private Sub Class_Terminate()
Set m_Res = Nothing
Set m_Conn = Nothing
Set m_Command = Nothing
End Sub
'【類屬性】
'資料庫連接字串
Public Property Get ConnectionString() As String
ConnectionString = m_ConnString
End Property
Public Property Let ConnectionString(ByVal vNewValue As String)
m_ConnString = vNewValue
End Property
'【類方法】
Public Function ExecQuery(ByVal SqlStr As String) As Recordset
Dim tempRes As New Recordset
Set m_Command = New ADODB.Command
Call openConn
m_Command.ActiveConnection = m_Conn
m_Command.CommandText = SqlStr
Set tempRes = m_Command.Execute()
tempRes.ActiveConnection = Nothing
Call closeConn
Set ExecQuery = tempRes
Set m_Command = Nothing
End Function
Public Function ExecParamQuery(ByVal SqlStr As String, ParamArray Params())
Dim tempRes As New Recordset
Dim i As Long
Set m_Command = New ADODB.Command
'打開連接
Call openConn
m_Command.ActiveConnection = m_Conn
m_Command.CommandText = SqlStr
m_Command.CommandType = adCmdText
'設定引數
With m_Command
For Each param In Params
Dim Para As ADODB.Parameter
Set Para = .CreateParameter(CStr(i), GetVarType(param), adParamInput, LenB(param))
Para.Value = param
.Parameters.Append Para
Next
End With
'獲取執行后記錄集
Set tempRes = m_Command.Execute()
'與資料庫連接脫鉤
tempRes.ActiveConnection = Nothing
'關閉資料庫連接
Call closeConn
'回傳資料集物件參考
Set ExecParamQuery = tempRes
'清空命令物件
Set m_Command = Nothing
End Function
Public Function ExecNonQuery(ByVal SqlStr As String) As Long
'定義影響行數變數
Dim affectedRows As Long
'創建系結Command物件
Set m_Command = New ADODB.Command
'打開連接
Call openConn
'系結Command到資料庫連接
m_Command.ActiveConnection = m_Conn
'設定SQL陳述句
m_Command.CommandText = SqlStr
'設定SQL型別
m_Command.CommandType = adCmdText
'獲取執行后影響行數
m_Command.Execute affectedRows
'關閉資料庫連接
Call closeConn
'清空命令物件
Set m_Command = Nothing
'回傳影響行數
ExecNonQuery = affectedRows
End Function
Public Function ExecParamNonQuery(ByVal SqlStr As String, ParamArray Params()) As Long
Dim i As Long
Dim affectedRows As Long
Set m_Command = New ADODB.Command
'打開連接
Call openConn
m_Command.ActiveConnection = m_Conn
m_Command.CommandText = SqlStr
m_Command.CommandType = adCmdText
'設定引數
With m_Command
For Each param In Params
Dim Para As ADODB.Parameter
Set Para = .CreateParameter(CStr(i), GetVarType(param), adParamInput, LenB(param))
Para.Value = param
.Parameters.Append Para
Next
End With
'獲取執行后記錄集
m_Command.Execute affectedRows
'關閉資料庫連接
Call closeConn
'清空命令物件
Set m_Command = Nothing
'回傳影響行數
ExecParamNonQuery = affectedRows
End Function
Public Sub SetConnToFile(ByVal FilePath As String)
m_ConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"
End Sub
Public Sub ReleaseRecordset(ByRef dbRes As ADODB.Recordset)
Set dbRes = Nothing
End Sub
'【內部方法】
Private Sub openConn()
Set m_Conn = New ADODB.Connection
m_Conn.CursorLocation = adUseClient
m_Conn.Open ConnectionString
End Sub
Private Sub closeConn()
m_Conn.Close
Set m_Conn = Nothing
End Sub
Public Function GetVarType(ByRef Value As Variant) As DataTypeEnum
Dim k As New ADODB.Command
Select Case VarType(Value)
Case VbVarType.vbString
GetVarType = DataTypeEnum.adVarChar
Case VbVarType.vbInteger
GetVarType = DataTypeEnum.adSmallInt
Case VbVarType.vbBoolean
GetVarType = DataTypeEnum.adBoolean
Case VbVarType.vbCurrency
GetVarType = DataTypeEnum.adCurrency
Case VbVarType.vbDate
GetVarType = DataTypeEnum.adDate
Case Else
GetVarType = DataTypeEnum.adVariant
End Select
End Function
uj5u.com熱心網友回復：
使用方法：
Public db As New AdodbHelper
'以下代碼中洗掉了業務代碼，只留下了部分使用本類的代碼
Sub Main()
Dim dbRes As ADODB.Recordset
'系結資料庫連接字串
db.SetConnToFile App.Path & "\documents.mdb"
'取得用戶串列
Set dbRes = db.ExecQuery("select * from Users")
If dbRes.RecordCount = 0 Then
'釋放記錄集資源
db.ReleaseRecordset dbRes
Exit Sub
End If
Do While Not dbRes.EOF = True
dbRes.MoveNext
Loop
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/103772.html
標籤：VB基礎類
下一篇：VB,曾經的強大語言。