最早是好几年前在网络上看到的代码,很简陋,但是觉得数据库操作很常用,应该封装一个类比较实用,就完善了下,按自己的习惯写了这个类
不过,对现在的我来说,这依然是简陋如斯的,,,后面要抽时间大改造,立志做个 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