1
<
%
Option
Explicit
%
>
2 < %
3 ' ****************************************************************
4 ' 类名:Cls_dbOperate
5 ' 作用:数据库操作类
6 ' ****************************************************************
7 Class Cls_dbOperate Class Cls_dbOperate
8Private 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
25Private Sub Class_Initialize()Sub Class_Initialize()
26strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(SitePath & DbFilePath)
27End Sub
28
29'*** 临时设置连接字符串
30Public Sub setConnString()Sub setConnString(ByRef sConn)
31strConn = sConn
32End Sub
33
34'*** 关闭对象
35Private Sub ClearObject()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()Sub Connection()
45Call ClearObject(objConn)
46Set objConn = Server.CreateObject("ADODB.Connection")
47objConn.Open strConn
48End Sub
49'*** 关闭连接
50Private Sub ClearConnection()Sub ClearConnection()
51ClearObject(objConn)
52End Sub
53
54'*** 创建记录集
55Private Sub Recordset()Sub Recordset()
56ClearObject(objRs)
57Set objRs = Server.CreateObject("ADODB.Recordset")
58End Sub
59
60'*** 关闭记录集
61Private Sub ClearRecordSet()Sub ClearRecordSet()
62Call ClearObject(objRs)
63End Sub
64
65'*** 创建命令对象
66Private Sub Command()Sub Command()
67'Call ClearObject(objCmd)
68Connection()
69Set objCmd = Server.CreateObject("ADODB.Command")
70objCmd.ActiveConnection = objConn
71End Sub
72
73Private Sub ClearCommand()Sub ClearCommand
74Call ClearObject(objcmd)
75End Sub
76
77'*** 相关处理状态信息
78'*** Msg:提示信息
79'*** IsSucceed : 是否成功执行命令 选项:1-失败并且停止处理,0-成功
80Private Sub Message()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()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()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()Property Let SetID(ByRef intID)
118ID = intID
119End Property
120
121'* 获得表名
122Public Property Let()Property Let TableName(ByVal strTableName)
123strTable = strTableName
124End Property
125
126'* 获得表字段
127Public Property Let()Property Let Fields(ByVal strFieldName)
128strField = strFieldName
129End Property
130
131'* 获取值
132Public Property Let()Property Let FieldsValue(ByVal Values)
133strFieldValue = Values
134End Property
135
136'* 筛选条件
137Public Property Let()Property Let Condition(ByVal strWhere)
138strCondition = strWhere
139End Property
140
141'* 排序,查询的时候需要用到
142Public Property Let()Property Let orderBy(ByRef orderByID)
143strOrderBy = orderByID
144End Property
145
146'* 查询时候用的
147Public Sub doQuery()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()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()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()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()Property Let SetSql(ByRef Sql)
248strSql = ""
249strSql = Sql
250End Property
251
252'执行 Sql 语句。返回无集录集的语句
253Public Sub ExecuteNoQuery()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()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()Sub Class_Terminate()
280If objConn.State = 1 Then
281Call ClearConnection()
282End If
283On Error Resume Next
284Call ClearRecord()
285Call ClearCommand()
286End Sub
287
288
289End 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 % >
2 < %
3 ' ****************************************************************
4 ' 类名:Cls_dbOperate
5 ' 作用:数据库操作类
6 ' ****************************************************************
7 Class Cls_dbOperate Class Cls_dbOperate
8Private 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
25Private Sub Class_Initialize()Sub Class_Initialize()
26strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(SitePath & DbFilePath)
27End Sub
28
29'*** 临时设置连接字符串
30Public Sub setConnString()Sub setConnString(ByRef sConn)
31strConn = sConn
32End Sub
33
34'*** 关闭对象
35Private Sub ClearObject()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()Sub Connection()
45Call ClearObject(objConn)
46Set objConn = Server.CreateObject("ADODB.Connection")
47objConn.Open strConn
48End Sub
49'*** 关闭连接
50Private Sub ClearConnection()Sub ClearConnection()
51ClearObject(objConn)
52End Sub
53
54'*** 创建记录集
55Private Sub Recordset()Sub Recordset()
56ClearObject(objRs)
57Set objRs = Server.CreateObject("ADODB.Recordset")
58End Sub
59
60'*** 关闭记录集
61Private Sub ClearRecordSet()Sub ClearRecordSet()
62Call ClearObject(objRs)
63End Sub
64
65'*** 创建命令对象
66Private Sub Command()Sub Command()
67'Call ClearObject(objCmd)
68Connection()
69Set objCmd = Server.CreateObject("ADODB.Command")
70objCmd.ActiveConnection = objConn
71End Sub
72
73Private Sub ClearCommand()Sub ClearCommand
74Call ClearObject(objcmd)
75End Sub
76
77'*** 相关处理状态信息
78'*** Msg:提示信息
79'*** IsSucceed : 是否成功执行命令 选项:1-失败并且停止处理,0-成功
80Private Sub Message()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()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()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()Property Let SetID(ByRef intID)
118ID = intID
119End Property
120
121'* 获得表名
122Public Property Let()Property Let TableName(ByVal strTableName)
123strTable = strTableName
124End Property
125
126'* 获得表字段
127Public Property Let()Property Let Fields(ByVal strFieldName)
128strField = strFieldName
129End Property
130
131'* 获取值
132Public Property Let()Property Let FieldsValue(ByVal Values)
133strFieldValue = Values
134End Property
135
136'* 筛选条件
137Public Property Let()Property Let Condition(ByVal strWhere)
138strCondition = strWhere
139End Property
140
141'* 排序,查询的时候需要用到
142Public Property Let()Property Let orderBy(ByRef orderByID)
143strOrderBy = orderByID
144End Property
145
146'* 查询时候用的
147Public Sub doQuery()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()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()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()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()Property Let SetSql(ByRef Sql)
248strSql = ""
249strSql = Sql
250End Property
251
252'执行 Sql 语句。返回无集录集的语句
253Public Sub ExecuteNoQuery()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()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()Sub Class_Terminate()
280If objConn.State = 1 Then
281Call ClearConnection()
282End If
283On Error Resume Next
284Call ClearRecord()
285Call ClearCommand()
286End Sub
287
288
289End 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 % >