VB 6.0中的数据连接模块
2007-03-28 16:48 ruinet 阅读(678) 评论(0) 收藏 举报 1
'== 标记数据库是否连接 ==
2Private IsConnect As Boolean
3'== 标记执行Connect()函数后，访问数据库的次数 ==
4Private Connect_Num As Integer
5Private cnn As ADODB.Connection '连接数据库的Connection对象
6Private rs As ADODB.Recordset '保存结果集的Recordset对象
7
8'连接数据库
9
Private Sub Connect()
10
'如果连接标记为真，则返回。否则会出错
11 If IsConnect = True Then
12 Exit Sub
13 End If
14
15 '关键New用于创建新对象cnn
16 Set cnn = New ADODB.Connection
17 '设置连接字符串ConnectionString属性
18 cnn.ConnectionString = connstr
19 '打开到数据库的连接
20 cnn.Open
21 '判断连接的状态
22 If cnn.State <> adStateOpen Then
23 '如果连接不成功，则显示提示信息，退出程序
24 MsgBox "数据库连接失败"
25 End
26 End If
27
28 '设置连接标记，表示已经连接到数据库
29 IsConnect = True
30
End Sub
31
32'断开与数据库的连接
33Private Sub Disconnect()
34 Dim Rc As Long
35 '如果连接标记为假，标明已经断开连接，则直接返回
36 If IsConnect = False Then
37 Exit Sub
38
39
40 End If
41 '关闭连接
42 cnn.Close
43 '释放cnn
44 Set cnn = Nothing
45 '设置连接标记，表示已经断开与数据库的连接
46 IsConnect = False
47End Sub
48
49'使用Connect_Num控制数据库连接
50Public Sub DB_Connect()
51 Connect_Num = Connect_Num + 1
52 Connect
53End Sub
54Public Sub DB_Disconnect()
55 If Connect_Num >= CONNECT_LOOP_MAX Then
56 Connect_Num = 0
57 Disconnect
58 End If
59End Sub
60'强制关闭api方式访问的数据库，计数器复位
61Public Sub DBapi_Disconnect()
62 Connect_Num = 0
63 Disconnect
64End Sub
65
66'执行数据库操作语句
67Public Sub SQLExt(ByVal TmpSQLstmt As String)
68 '创建Command对象cmd
69 Dim cmd As New ADODB.Command
70
71 '连接到数据库
72 DB_Connect
73 '设置cmd的ActiveConnection属性，指定与其关联的数据库连接
74 Set cmd.ActiveConnection = cnn
75 '设置要执行的命令文本
76 cmd.CommandText = TmpSQLstmt
77' MsgBox TmpSQLstmt
78 '执行命令
79 cmd.Execute
80 '清空cmd对象
81 Set cmd = Nothing
82 '断开与数据库的连接
83 DB_Disconnect
84End Sub
85
86'执行数据库查询语句
87Public Function QueryExt(ByVal TmpSQLstmt As String) As ADODB.Recordset
88 '创建Recordset对象rst
89 Dim rst As New ADODB.Recordset
90
91 '连接到数据库
92 DB_Connect
93 '设置rst的ActiveConnection属性，指定与其关联的数据库连接
94 Set rst.ActiveConnection = cnn
95 '设置游标类型
96 rst.CursorType = adOpenDynamic
97 '设置锁定类型
98 rst.LockType = adLockOptimistic
99 '打开记录集
100 rst.Open TmpSQLstmt
101 '返回记录集
102 Set QueryExt = rst
103End Function
104
'== 标记数据库是否连接 ==2
Private IsConnect As Boolean3
'== 标记执行Connect()函数后，访问数据库的次数 ==4
Private Connect_Num As Integer5
Private cnn As ADODB.Connection '连接数据库的Connection对象6
Private rs As ADODB.Recordset '保存结果集的Recordset对象7
8
'连接数据库9
Private Sub Connect()
10
'如果连接标记为真，则返回。否则会出错11
If IsConnect = True Then12
Exit Sub13
End If14
15
'关键New用于创建新对象cnn16
Set cnn = New ADODB.Connection17
'设置连接字符串ConnectionString属性18
cnn.ConnectionString = connstr19
'打开到数据库的连接20
cnn.Open21
'判断连接的状态22
If cnn.State <> adStateOpen Then23
'如果连接不成功，则显示提示信息，退出程序24
MsgBox "数据库连接失败"25
End26
End If27
28
'设置连接标记，表示已经连接到数据库29
IsConnect = True30
End Sub31
32
'断开与数据库的连接33
Private Sub Disconnect()34
Dim Rc As Long35
'如果连接标记为假，标明已经断开连接，则直接返回36
If IsConnect = False Then37
Exit Sub38
39
40
End If41
'关闭连接42
cnn.Close43
'释放cnn44
Set cnn = Nothing45
'设置连接标记，表示已经断开与数据库的连接46
IsConnect = False47
End Sub48
49
'使用Connect_Num控制数据库连接50
Public Sub DB_Connect()51
Connect_Num = Connect_Num + 152
Connect53
End Sub54
Public Sub DB_Disconnect()55
If Connect_Num >= CONNECT_LOOP_MAX Then56
Connect_Num = 057
Disconnect58
End If59
End Sub60
'强制关闭api方式访问的数据库，计数器复位61
Public Sub DBapi_Disconnect()62
Connect_Num = 063
Disconnect64
End Sub65
66
'执行数据库操作语句67
Public Sub SQLExt(ByVal TmpSQLstmt As String)68
'创建Command对象cmd69
Dim cmd As New ADODB.Command70
71
'连接到数据库72
DB_Connect73
'设置cmd的ActiveConnection属性，指定与其关联的数据库连接74
Set cmd.ActiveConnection = cnn75
'设置要执行的命令文本76
cmd.CommandText = TmpSQLstmt77
' MsgBox TmpSQLstmt78
'执行命令79
cmd.Execute80
'清空cmd对象81
Set cmd = Nothing82
'断开与数据库的连接83
DB_Disconnect84
End Sub85
86
'执行数据库查询语句87
Public Function QueryExt(ByVal TmpSQLstmt As String) As ADODB.Recordset88
'创建Recordset对象rst89
Dim rst As New ADODB.Recordset90
91
'连接到数据库92
DB_Connect93
'设置rst的ActiveConnection属性，指定与其关联的数据库连接94
Set rst.ActiveConnection = cnn95
'设置游标类型96
rst.CursorType = adOpenDynamic97
'设置锁定类型98
rst.LockType = adLockOptimistic99
'打开记录集100
rst.Open TmpSQLstmt101
'返回记录集102
Set QueryExt = rst103
End Function104
浙公网安备 33010602011771号