一个数据库操作类
1
<% Option Explicit %>
2<%
3'****************************************************************
4' 类名：Cls_dbOperate
5' 作用：数据库操作类
6'****************************************************************
7
Class Cls_dbOperate
8
Private strConn
9Private objConn
10Private objRs
11
12Private objRs1
13
14Private objCmd
15Private strTable
16Private strField
17Private strFieldValue '字段
18Private strOrderBy
19Private strCondition
20Private strSql
21Private dbFile '数据库文件路径名，此处最好是用到配置文件
22Private ID
23Private Status '====命令状态提示符号！0-成功；1-失败
24
25
Private Sub Class_Initialize()
26strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(SitePath & DbFilePath)
27
End Sub
28
29'*** 临时设置连接字符串
30Public Sub setConnString(ByRef sConn)
31strConn = sConn
32End Sub
33
34'*** 关闭对象
35Private Sub ClearObject(ByRef obj)
36On Error Resume Next
37If IsObject(obj) = True Then
38obj.Close()
39Set obj = Nothing
40End If
41If Err.Number <> 0 Then Err.Clear
42End Sub
43'*** 创建连接对象
44Private Sub Connection()
45Call ClearObject(objConn)
46Set objConn = Server.CreateObject("ADODB.Connection")
47objConn.Open strConn
48End Sub
49'*** 关闭连接
50Private Sub ClearConnection()
51ClearObject(objConn)
52End Sub
53
54'*** 创建记录集
55Private Sub Recordset()
56ClearObject(objRs)
57Set objRs = Server.CreateObject("ADODB.Recordset")
58End Sub
59
60'*** 关闭记录集
61Private Sub ClearRecordSet()
62Call ClearObject(objRs)
63End Sub
64
65'*** 创建命令对象
66Private Sub Command()
67'Call ClearObject(objCmd)
68Connection()
69Set objCmd = Server.CreateObject("ADODB.Command")
70objCmd.ActiveConnection = objConn
71End Sub
72
73Private Sub ClearCommand
74Call ClearObject(objcmd)
75End Sub
76
77'*** 相关处理状态信息
78'*** Msg:提示信息
79'*** IsSucceed : 是否成功执行命令 选项：1-失败并且停止处理,0-成功
80Private Sub Message(Msg,IsSucceed)
81If IsSucceed = 1 Then
82Status = 1
83Response.Write(Msg)
84Response.End()
85Else
86Status = 0
87End If
88End Sub
89
90'*** 数组长度比较
91Private Function Compare(ByRef CompA,ByRef CompB)
92If Eval(CompA = CompB) = False Then
93Compare = 1
94Call ErrMsg("相关条件不匹配")
95Else
96Compare = 0
97End If
98End Function
99
100'******* 检查记录是否存在,返回 0-存在；1-不存在
101Private Function IsIDExists()
102Dim strTemp
103strTemp = "Select ID From "& strTable & " Where ID = " & ID
104Connection()
105Recordset()
106objRs.Open strTemp,objConn,1,1
107If objRs.EOF Then
108IsIDExists = 1
109Else
110IsIDExists = 0
111End If
112ClearConnection()
113ClearRecordSet()
114End Function
115
116
117Public Property Let SetID(ByRef intID)
118ID = intID
119End Property
120
121'* 获得表名
122Public Property Let TableName(ByVal strTableName)
123strTable = strTableName
124End Property
125
126'* 获得表字段
127Public Property Let Fields(ByVal strFieldName)
128strField = strFieldName
129End Property
130
131'* 获取值
132Public Property Let FieldsValue(ByVal Values)
133strFieldValue = Values
134End Property
135
136'* 筛选条件
137Public Property Let Condition(ByVal strWhere)
138strCondition = strWhere
139End Property
140
141'* 排序，查询的时候需要用到
142Public Property Let orderBy(ByRef orderByID)
143strOrderBy = orderByID
144End Property
145
146'* 查询时候用的
147Public Sub doQuery(ByRef PreArray)
148strSql = ""
149strSql = "Select "
150Dim i,flag,ArrTemp
151flag = 0
152If strField = "" Then
153strField = " * "
154Else
155strField = Replace(strField,Spliter,",")
156End If
157strSql = strSql & strField
158strSql = strSql & " From " & strTable
159If strCondition <> "" Then
160strSql = strSql & " Where " & strCondition
161Else
162If ID <> "" Then strSql = strSql & " Where ID = " & ID
163End If
164If strOrderBy <> "" Then
165strSql = strSql & strOrderBy
166End If
167Response.Write strSql
168
169ExecuteQuery PreArray
170End Sub
171
172'* 修改
173Public Sub doUpdate()
174If IsIDExists()= 0 Then
175Dim ArrField,ArrFieldValue
176strSql = ""
177ArrField = ""
178ArrFieldValue = ""
179ArrField = Split(strField,Spliter)
180ArrFieldValue = Split(strFieldValue,Spliter)
181If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub
182strSql = "Update " & strTable & " Set "
183For i = 0 To Ubound(ArrField)
184If IsNumeric(ArrFieldValue(i)) = False Then
185strSql = strSql & ArrField(i) & " = '" & ArrFieldValue(i) & "'"
186If i <> UBound(ArrField) Then strSql = strSql & " , "
187Else
188strSql = strSql & ArrField(i) & " = " & ArrFieldValue(i)
189If i <> UBound(ArrField) Then strSql = strSql & " , "
190End If
191Next
192If strCondition <> "" Then
193strSql = strSql & " where " & strCondition
194Else
195strSql = strSql & " where ID = " & ID
196End If
197
198Response.Write strSql
199ExecuteNoQuery()
200Status = 0
201Else
202Message "请设置正确的ID",1
203End If
204End Sub
205
206'* 添加
207Public Sub doAdd()
208Dim ArrFieldValue,ArrField,i
209ArrField = ""
210ArrFieldValue = ""
211strSql = ""
212ArrField = Split(strField,Spliter)
213ArrFieldValue = Split(strFieldValue,Spliter)
214If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub
215strField = Replace(strField,Spliter,",")
216strSql = "Insert into " & strTable & "(" & strField & ") Values("
217For i = 0 To Ubound(ArrFieldValue)
218If IsNumeric(ArrFieldValue(i)) = False Then
219strSql = strSql & "'" & ArrFieldValue(i) & "'"
220If i <> Ubound(ArrFieldValue) Then strSql = strSql & ","
221Else
222strSql = strSql & ArrFieldValue(i)
223If i <> Ubound(ArrFieldValue) Then strSql = strSql & ","
224End If
225Next
226strSql = strSql & ")"
227
228Response.Write strSql
229ExecuteNoQuery()
230End Sub
231
232Public Sub doDelete()
233If IsIDExists()= 0 Then
234strSql = ""
235If ID = "" or strCondition = "" Then strSql = ""
236If ID <> "" Then strSql = "Delete * From " & strTable & " Where ID = " &ID
237If strCondition <> "" Then strSql = "Delete * From " & strTable & " Where " & strCondition
238Response.Write strSql
239ExecuteNoQuery()
240Status = 0
241Else
242Message "请设置正确的ID",1
243End If
244End Sub
245
246'为自己写完整的 Sql 语句做准备
247Public Property Let SetSql(ByRef Sql)
248strSql = ""
249strSql = Sql
250End Property
251
252'执行 Sql 语句。返回无集录集的语句
253Public Sub ExecuteNoQuery()
254If strSql <> "" Then
255Command()
256With objCmd
257.CommandText = strSql
258.Execute
259End With
260End If
261End Sub
262
263'执行 Sql 语句。返回有记录集的语句,这里的 objRecordset 应该为一个 Recordset 对象
264Public Sub ExecuteQuery(ByRef PreArray)
265Recordset()
266Command()
267objCmd.CommandText = strSql
268Set objRs = objCmd.Execute
269Response.Write strsql
270'执行 Sql 语句
271'为方便前台调用，在这里将在外部生成一个2维数组，故在调用前请在外部声明一个变量
272' ExecuteQuery
273If Not objRs.EOF Then PreArray = objRs.GetRows()
274ClearRecordSet()
275ClearConnection()
276ClearCommand()
277End Sub
278
279Private Sub Class_Terminate()
280If objConn.State = 1 Then
281Call ClearConnection()
282End If
283On Error Resume Next
284Call ClearRecord()
285Call ClearCommand()
286End Sub
287
288
289
End Class
290
291
292
293'=========使用示例
294Dim test,i
295Dim t
296Set test = New Cls_dbOperate
297
298With test
299.TableName = "NewsCategory"
300.Fields = "ID|||NewsCateName|||BelongID"
301' .SetID = 5
302' .Condition = "ABC = 4"
303'查询用的
304.doQuery t
305
306' ==========第二种方式
307' .SetSql = "Select ID,NewsCateName,BelongID From NewsCategory"
308' .ExecuteQuery t
309
310'修改用的
311.SetID = 1
312.Fields = "BelongID"
313.FieldsValue = 3
314.doUpdate
315
316'添加用的
317.Fields = "NewsCateName|||BelongID"
318.FieldsValue = "我的测试|||0"
319.doAdd
320
321'删除时使用
322.SetID = 68
323.TableName = "NewsCategory"
324.doDelete
325
326End With
327%>
328
329<% If IsArray(t) = True Then %>
330<table border='1' width='300'>
331<%
332For i = 0 To UBound(t,2) %>
333<tr>
334<td><%=t(0,i)%></td>
335<td><%=t(1,i)%></td>
336<td><%=t(2,i)%></td>
337</tr>
338<% Next %>
339</table>
340<%
341Else
342Response.Write "错误,没有数据"
343End If
344%>
<% Option Explicit %> 2
<% 3
'**************************************************************** 4
' 类名：Cls_dbOperate 5
' 作用：数据库操作类 6
'**************************************************************** 7
Class Cls_dbOperate 
8
Private strConn 9
Private objConn 10
Private objRs 11
12
Private objRs1 13
14
Private objCmd 15
Private strTable 16
Private strField 17
Private strFieldValue '字段 18
Private strOrderBy 19
Private strCondition 20
Private strSql 21
Private dbFile '数据库文件路径名，此处最好是用到配置文件 22
Private ID 23
Private Status '====命令状态提示符号！0-成功；1-失败 24
25
Private Sub Class_Initialize() 
26
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(SitePath & DbFilePath) 27
End Sub 28
29
'*** 临时设置连接字符串 30
Public Sub setConnString(ByRef sConn) 31
strConn = sConn 32
End Sub 33
34
'*** 关闭对象 35
Private Sub ClearObject(ByRef obj) 36
On Error Resume Next 37
If IsObject(obj) = True Then 38
obj.Close() 39
Set obj = Nothing 40
End If 41
If Err.Number <> 0 Then Err.Clear 42
End Sub 43
'*** 创建连接对象 44
Private Sub Connection() 45
Call ClearObject(objConn) 46
Set objConn = Server.CreateObject("ADODB.Connection") 47
objConn.Open strConn 48
End Sub 49
'*** 关闭连接 50
Private Sub ClearConnection() 51
ClearObject(objConn) 52
End Sub 53
54
'*** 创建记录集 55
Private Sub Recordset() 56
ClearObject(objRs) 57
Set objRs = Server.CreateObject("ADODB.Recordset") 58
End Sub 59
60
'*** 关闭记录集 61
Private Sub ClearRecordSet() 62
Call ClearObject(objRs) 63
End Sub 64
65
'*** 创建命令对象 66
Private Sub Command() 67
'Call ClearObject(objCmd) 68
Connection() 69
Set objCmd = Server.CreateObject("ADODB.Command") 70
objCmd.ActiveConnection = objConn 71
End Sub 72
73
Private Sub ClearCommand 74
Call ClearObject(objcmd) 75
End Sub 76
77
'*** 相关处理状态信息 78
'*** Msg:提示信息 79
'*** IsSucceed : 是否成功执行命令 选项：1-失败并且停止处理,0-成功 80
Private Sub Message(Msg,IsSucceed) 81
If IsSucceed = 1 Then 82
Status = 1 83
Response.Write(Msg) 84
Response.End() 85
Else 86
Status = 0 87
End If 88
End Sub 89
90
'*** 数组长度比较 91
Private Function Compare(ByRef CompA,ByRef CompB) 92
If Eval(CompA = CompB) = False Then 93
Compare = 1 94
Call ErrMsg("相关条件不匹配") 95
Else 96
Compare = 0 97
End If 98
End Function 99
100
'******* 检查记录是否存在,返回 0-存在；1-不存在 101
Private Function IsIDExists() 102
Dim strTemp 103
strTemp = "Select ID From "& strTable & " Where ID = " & ID 104
Connection() 105
Recordset() 106
objRs.Open strTemp,objConn,1,1 107
If objRs.EOF Then 108
IsIDExists = 1 109
Else 110
IsIDExists = 0 111
End If 112
ClearConnection() 113
ClearRecordSet() 114
End Function 115
116
117
Public Property Let SetID(ByRef intID) 118
ID = intID 119
End Property 120
121
'* 获得表名 122
Public Property Let TableName(ByVal strTableName) 123
strTable = strTableName 124
End Property 125
126
'* 获得表字段 127
Public Property Let Fields(ByVal strFieldName) 128
strField = strFieldName 129
End Property 130
131
'* 获取值 132
Public Property Let FieldsValue(ByVal Values) 133
strFieldValue = Values 134
End Property 135
136
'* 筛选条件 137
Public Property Let Condition(ByVal strWhere) 138
strCondition = strWhere 139
End Property 140
141
'* 排序，查询的时候需要用到 142
Public Property Let orderBy(ByRef orderByID) 143
strOrderBy = orderByID 144
End Property 145
146
'* 查询时候用的 147
Public Sub doQuery(ByRef PreArray) 148
strSql = "" 149
strSql = "Select " 150
Dim i,flag,ArrTemp 151
flag = 0 152
If strField = "" Then 153
strField = " * " 154
Else 155
strField = Replace(strField,Spliter,",") 156
End If 157
strSql = strSql & strField 158
strSql = strSql & " From " & strTable 159
If strCondition <> "" Then 160
strSql = strSql & " Where " & strCondition 161
Else 162
If ID <> "" Then strSql = strSql & " Where ID = " & ID 163
End If 164
If strOrderBy <> "" Then 165
strSql = strSql & strOrderBy 166
End If 167
Response.Write strSql 168
169
ExecuteQuery PreArray 170
End Sub 171
172
'* 修改 173
Public Sub doUpdate() 174
If IsIDExists()= 0 Then 175
Dim ArrField,ArrFieldValue 176
strSql = "" 177
ArrField = "" 178
ArrFieldValue = "" 179
ArrField = Split(strField,Spliter) 180
ArrFieldValue = Split(strFieldValue,Spliter) 181
If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub 182
strSql = "Update " & strTable & " Set " 183
For i = 0 To Ubound(ArrField) 184
If IsNumeric(ArrFieldValue(i)) = False Then 185
strSql = strSql & ArrField(i) & " = '" & ArrFieldValue(i) & "'" 186
If i <> UBound(ArrField) Then strSql = strSql & " , " 187
Else 188
strSql = strSql & ArrField(i) & " = " & ArrFieldValue(i) 189
If i <> UBound(ArrField) Then strSql = strSql & " , " 190
End If 191
Next 192
If strCondition <> "" Then 193
strSql = strSql & " where " & strCondition 194
Else 195
strSql = strSql & " where ID = " & ID 196
End If 197
198
Response.Write strSql 199
ExecuteNoQuery() 200
Status = 0 201
Else 202
Message "请设置正确的ID",1 203
End If 204
End Sub 205
206
'* 添加 207
Public Sub doAdd() 208
Dim ArrFieldValue,ArrField,i 209
ArrField = "" 210
ArrFieldValue = "" 211
strSql = "" 212
ArrField = Split(strField,Spliter) 213
ArrFieldValue = Split(strFieldValue,Spliter) 214
If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub 215
strField = Replace(strField,Spliter,",") 216
strSql = "Insert into " & strTable & "(" & strField & ") Values(" 217
For i = 0 To Ubound(ArrFieldValue) 218
If IsNumeric(ArrFieldValue(i)) = False Then 219
strSql = strSql & "'" & ArrFieldValue(i) & "'" 220
If i <> Ubound(ArrFieldValue) Then strSql = strSql & "," 221
Else 222
strSql = strSql & ArrFieldValue(i) 223
If i <> Ubound(ArrFieldValue) Then strSql = strSql & "," 224
End If 225
Next 226
strSql = strSql & ")" 227
228
Response.Write strSql 229
ExecuteNoQuery() 230
End Sub 231
232
Public Sub doDelete() 233
If IsIDExists()= 0 Then 234
strSql = "" 235
If ID = "" or strCondition = "" Then strSql = "" 236
If ID <> "" Then strSql = "Delete * From " & strTable & " Where ID = " &ID 237
If strCondition <> "" Then strSql = "Delete * From " & strTable & " Where " & strCondition 238
Response.Write strSql 239
ExecuteNoQuery() 240
Status = 0 241
Else 242
Message "请设置正确的ID",1 243
End If 244
End Sub 245
246
'为自己写完整的 Sql 语句做准备 247
Public Property Let SetSql(ByRef Sql) 248
strSql = "" 249
strSql = Sql 250
End Property 251
252
'执行 Sql 语句。返回无集录集的语句 253
Public Sub ExecuteNoQuery() 254
If strSql <> "" Then 255
Command() 256
With objCmd 257
.CommandText = strSql 258
.Execute 259
End With 260
End If 261
End Sub 262
263
'执行 Sql 语句。返回有记录集的语句,这里的 objRecordset 应该为一个 Recordset 对象 264
Public Sub ExecuteQuery(ByRef PreArray) 265
Recordset() 266
Command() 267
objCmd.CommandText = strSql 268
Set objRs = objCmd.Execute 269
Response.Write strsql 270
'执行 Sql 语句 271
'为方便前台调用，在这里将在外部生成一个2维数组，故在调用前请在外部声明一个变量 272
' ExecuteQuery 273
If Not objRs.EOF Then PreArray = objRs.GetRows() 274
ClearRecordSet() 275
ClearConnection() 276
ClearCommand() 277
End Sub 278
279
Private Sub Class_Terminate() 280
If objConn.State = 1 Then 281
Call ClearConnection() 282
End If 283
On Error Resume Next 284
Call ClearRecord() 285
Call ClearCommand() 286
End Sub 287
288
289
End Class 290
291
292
293
'=========使用示例 294
Dim test,i 295
Dim t 296
Set test = New Cls_dbOperate 297
298
With test 299
.TableName = "NewsCategory" 300
.Fields = "ID|||NewsCateName|||BelongID" 301
' .SetID = 5 302
' .Condition = "ABC = 4" 303
'查询用的 304
.doQuery t 305
306
' ==========第二种方式 307
' .SetSql = "Select ID,NewsCateName,BelongID From NewsCategory" 308
' .ExecuteQuery t 309
310
'修改用的 311
.SetID = 1 312
.Fields = "BelongID" 313
.FieldsValue = 3 314
.doUpdate 315
316
'添加用的 317
.Fields = "NewsCateName|||BelongID" 318
.FieldsValue = "我的测试|||0" 319
.doAdd 320
321
'删除时使用 322
.SetID = 68 323
.TableName = "NewsCategory" 324
.doDelete 325
326
End With 327
%> 328
329
<% If IsArray(t) = True Then %> 330
<table border='1' width='300'> 331
<% 332
For i = 0 To UBound(t,2) %> 333
<tr> 334
<td><%=t(0,i)%></td> 335
<td><%=t(1,i)%></td> 336
<td><%=t(2,i)%></td> 337
</tr> 338
<% Next %> 339
</table> 340
<% 341
Else 342
Response.Write "错误,没有数据" 343
End If 344
%>
