未分类

vb6封装的一个数据库操作类

最早是好几年前在网络上看到的代码,很简陋,但是觉得数据库操作很常用,应该封装一个类比较实用,就完善了下,按自己的习惯写了这个类

不过,对现在的我来说,这依然是简陋如斯的,,,后面要抽时间大改造,立志做个 vb 界的 ORM

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "cDataBase"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'引用:
'Microsoft ActiveX Data Objects 2.8 Library


'== 标记数据库是否连接 ==
Public IsConnect As Boolean
'保存最后一次错误
Public LastErr As String
Public LastErrNumber As Long
Public LastErrDescription As String
'创建Command对象Exec_Cmd
Public Exec_Cmd As New ADODB.Command
'保存记录集
Public rs As New ADODB.Recordset
Public Exec_Rs As New ADODB.Recordset
Public Exec_Rs_2 As New ADODB.Recordset
'连接数据库的Connection对象
Public Conn As New ADODB.Connection
'是否开始了事务
Private isTrans As Boolean

Public Enum enumDbType
    Access
    mysql
    MsSql
    CSV
End Enum


'连接数据库
Public Function Connect( _
    Optional ByVal dirPath As String, _
    Optional ByVal dbType As enumDbType, _
    Optional ByVal Server As String = "127.0.0.1,1433", _
    Optional ByVal Uid As String = "sa", _
    Optional ByVal Pwd As String = "Sa123456", _
    Optional ByVal DataBase As String) As Boolean
    
    On Error GoTo eee
    
    '如果连接标记为真,则返回。否则会出错
    If IsConnect = True Then
        '返回成功
        Connect = True
        Exit Function
    End If
    
    '设置连接字符串ConnectionString属性
    Select Case dbType
    Case Access
        Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & dirPath & ";"
    Case CSV
        Conn.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=" & dirPath
    Case MsSql
        Conn.ConnectionString = "Driver={SQL Server};Server=" & Server & ";Uid=" & Uid & ";pwd=" & Pwd & ";Database=" & DataBase & ";"
    Case mysql
        Dim Svr: Svr = Split(Server, ",")
        Dim Address$: Address = Svr(0)
        Dim Port$: Port = "3306"
        If UBound(Svr) > 0 Then Port = Svr(1)
        Conn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=" & Address & ";Port=" & Port & ";Uid=" & Uid & ";pwd=" & Pwd & ";Database=" & DataBase & ";OPTION=3"
    End Select
    '打开到数据库的连接
    Conn.Open
    '判断连接的状态
    If Conn.State <> adStateOpen Then
        '如果连接不成功,则显示提示信息,退出程序
        Exit Function
    End If
    '设置Exec_Cmd的ActiveConnection属性,指定与其关联的数据库连接
    Set Exec_Cmd.ActiveConnection = Conn
    '设置rst的ActiveConnection属性,指定与其关联的数据库连接
    Set rs.ActiveConnection = Conn
    '根据链接类型设置一些必要的查询缺省值,减少外部查询代码的累赘
    Select Case dbType
    Case Access
        'todo
    Case CSV
        'todo
    Case MsSql
        'todo
    Case mysql
        'todo
    End Select
    
    Conn.CursorLocation = adUseClient
    '设置连接标记,表示已经连接到数据库
    IsConnect = True
    Connect = True
    Exit Function
eee:
    LastErr = "Connect Err[" & Err.Number & "]" & Err.Description & "{" & dirPath & "}{" & dbType & "}{" & Server & "}{" & Uid & "}{" & DataBase & "}"
    LastErrNumber = Err.Number
    LastErrDescription = Err.Description
End Function

'断开与数据库的连接
Public Function Disconnect() As Boolean
On Error GoTo eee

    '如果连接标记为假,标明已经断开连接,则直接返回
    If IsConnect = False Then
        Exit Function
    End If
    '关闭连接
    Conn.Close
    If rs.State <> 0 Then rs.Close
    If Exec_Rs.State <> 0 Then Exec_Rs.Close
    '释放conn
    Set Conn = Nothing
    Set rs = Nothing
    Set Exec_Cmd = Nothing
    
    '设置连接标记,表示已经断开与数据库的连接
    IsConnect = False
    Disconnect = True

Exit Function
eee:
LastErr = "Disconnect Err[" & Err.Number & "]" & Err.Description
LastErrNumber = Err.Number
LastErrDescription = Err.Description
End Function


'执行数据库操作语句
Public Function Exec(ByVal TmpSQLstmt As String, Optional ByVal doTrans As Boolean = True) As Boolean
On Error GoTo eee

    
    '如果连接标记为假,标明已经断开连接,则直接返回
    If IsConnect = False Then
        LastErr = "数据库未连接"
    Exit Function
    End If

    '设置要执行的命令文本
    Exec_Cmd.CommandText = TmpSQLstmt
    '  MsgBox TmpSQLstmt
    '清空
    If Exec_Rs.State <> 0 Then   '0=close
        Exec_Rs.Close
    End If
    '判断是否需要事务
    If doTrans Then
        Conn.BeginTrans
        isTrans = True
    End If
    Set Exec_Rs = Exec_Cmd.Execute()
    '如果需要事务,此时提交
    If doTrans Then Conn.CommitTrans
    '返回记录集d对象
    Exec = True
Exit Function
eee:
'如果事务失败,此时回滚
If doTrans And isTrans Then Conn.RollbackTrans
LastErr = "Exec Err[" & Err.Number & "]" & Err.Description & "{" & TmpSQLstmt & "}"
LastErrNumber = Err.Number
LastErrDescription = Err.Description
End Function

'执行数据库查询语句
Public Function Query(ByVal TmpSQLstmt As String, Optional CurType As CursorTypeEnum = adOpenKeyset, Optional LockType As LockTypeEnum = adLockOptimistic, Optional ByVal doTrans As Boolean) As Boolean
    On Error GoTo eee
    
    '如果连接标记为假,标明已经断开连接,则直接返回
    If IsConnect = False Then
        LastErr = "数据库未连接"
        Exit Function
    End If
    If rs.State <> 0 Then                                                       '0=close
        rs.Close
    End If
    '设置游标类型
    rs.CursorType = CurType
    '设置锁定类型
    rs.LockType = LockType
    '判断是否需要事务
    If doTrans Then
        Conn.BeginTrans
        isTrans = True
    End If
    '打开记录集
    rs.Open TmpSQLstmt
    '如果需要事务,此时提交
    If doTrans Then Conn.CommitTrans
    Query = True
    
    Exit Function
eee:
    '如果事务失败,此时回滚
    If doTrans And isTrans Then Conn.RollbackTrans
    LastErr = "Query Err[" & Err.Number & "]" & Err.Description & "{" & TmpSQLstmt & "}"
    LastErrNumber = Err.Number
    LastErrDescription = Err.Description
End Function


Private Sub Class_Terminate()
    Call Disconnect
End Sub

使用示例:

要不你们自己先看源码,我现在没时间

Views: 141

Hi, I’m 邓伟

本来无一物,何处惹尘埃

发表回复