asp数据库操作类

<script src="http://www.aspxuexi.com/abc/center.js" type=text/javascript></script> <script type=text/javascript> </script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type=text/javascript> </script>

<% Option Explicit %>
<%
'****************************************************************
' 类名：Cls_dbOperate
' 作用：数据库操作类
'****************************************************************
Class Cls_dbOperate
Private strConn
Private objConn
Private objRs

Private objRs1

Private objCmd
Private strTable
Private strField
Private strFieldValue '字段
Private strOrderBy
Private strCondition
Private strSql
Private dbFile '数据库文件路径名，此处最好是用到配置文件
Private ID
Private Status '====命令状态提示符号！0-成功；1-失败

Private Sub Class_Initialize()
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(SitePath & DbFilePath)
End Sub

'*** 临时设置连接字符串
Public Sub setConnString(ByRef sConn)
strConn = sConn
End Sub

'*** 关闭对象
Private Sub ClearObject(ByRef obj)
On Error Resume Next
If IsObject(obj) = True Then
obj.Close()
Set obj = Nothing
End If
If Err.Number <> 0 Then Err.Clear
End Sub
'*** 创建连接对象
Private Sub Connection()
Call ClearObject(objConn)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn
End Sub
'*** 关闭连接
Private Sub ClearConnection()
ClearObject(objConn)
End Sub

'*** 创建记录集
Private Sub Recordset()
ClearObject(objRs)
Set objRs = Server.CreateObject("ADODB.Recordset")
End Sub

'*** 关闭记录集
Private Sub ClearRecordSet()
Call ClearObject(objRs)
End Sub

'*** 创建命令对象
Private Sub Command()
'Call ClearObject(objCmd)
Connection()
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConn
End Sub

Private Sub ClearCommand
Call ClearObject(objcmd)
End Sub

'*** 相关处理状态信息
'*** Msg:提示信息
'*** IsSucceed : 是否成功执行命令 选项：1-失败并且停止处理,0-成功
Private Sub Message(Msg,IsSucceed)
If IsSucceed = 1 Then
Status = 1
Response.Write(Msg)
Response.End()
Else
Status = 0
End If
End Sub

'*** 数组长度比较
Private Function Compare(ByRef CompA,ByRef CompB)
If Eval(CompA = CompB) = False Then
Compare = 1
Call ErrMsg("相关条件不匹配")
Else
Compare = 0
End If
End Function

'******* 检查记录是否存在,返回 0-存在；1-不存在
Private Function IsIDExists()
Dim strTemp
strTemp = "Select ID From "& strTable & " Where ID = " & ID
Connection()
Recordset()
objRs.Open strTemp,objConn,1,1
If objRs.EOF Then
IsIDExists = 1
Else
IsIDExists = 0
End If
ClearConnection()
ClearRecordSet()
End Function


Public Property Let SetID(ByRef intID)
ID = intID
End Property

'* 获得表名
Public Property Let TableName(ByVal strTableName)
strTable = strTableName
End Property

'* 获得表字段
Public Property Let Fields(ByVal strFieldName)
strField = strFieldName
End Property

'* 获取值
Public Property Let FieldsValue(ByVal Values)
strFieldValue = Values
End Property

'* 筛选条件
Public Property Let Condition(ByVal strWhere)
strCondition = strWhere
End Property

'* 排序，查询的时候需要用到
Public Property Let orderBy(ByRef orderByID)
strOrderBy = orderByID
End Property

'* 查询时候用的
Public Sub doQuery(ByRef PreArray)
strSql = ""
strSql = "Select "
Dim i,flag,ArrTemp
flag = 0
If strField = "" Then
strField = " * "
Else
strField = Replace(strField,Spliter,",")
End If
strSql = strSql & strField
strSql = strSql & " From " & strTable
If strCondition <> "" Then
strSql = strSql & " Where " & strCondition
Else
If ID <> "" Then strSql = strSql & " Where ID = " & ID
End If
If strOrderBy <> "" Then
strSql = strSql & strOrderBy
End If
Response.Write strSql

ExecuteQuery PreArray
End Sub

'* 修改
Public Sub doUpdate()
If IsIDExists()= 0 Then
Dim ArrField,ArrFieldValue
strSql = ""
ArrField = ""
ArrFieldValue = ""
ArrField = Split(strField,Spliter)
ArrFieldValue = Split(strFieldValue,Spliter)
If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub
strSql = "Update " & strTable & " Set "
For i = 0 To Ubound(ArrField)
If IsNumeric(ArrFieldValue(i)) = False Then
strSql = strSql & ArrField(i) & " = '" & ArrFieldValue(i) & "'"
If i <> UBound(ArrField) Then strSql = strSql & " , "
Else
strSql = strSql & ArrField(i) & " = " & ArrFieldValue(i)
If i <> UBound(ArrField) Then strSql = strSql & " , "
End If
Next
If strCondition <> "" Then
strSql = strSql & " where " & strCondition
Else
strSql = strSql & " where ID = " & ID
End If

Response.Write strSql
ExecuteNoQuery()
Status = 0
Else
Message "请设置正确的ID",1
End If
End Sub

'* 添加
Public Sub doAdd()
Dim ArrFieldValue,ArrField,i
ArrField = ""
ArrFieldValue = ""
strSql = ""
ArrField = Split(strField,Spliter)
ArrFieldValue = Split(strFieldValue,Spliter)
If Compare(Ubound(ArrField),UBound(ArrFieldValue)) <> 0 Then Exit Sub
strField = Replace(strField,Spliter,",")
strSql = "Insert into " & strTable & "(" & strField & ") Values("
For i = 0 To Ubound(ArrFieldValue)
If IsNumeric(ArrFieldValue(i)) = False Then
strSql = strSql & "'" & ArrFieldValue(i) & "'"
If i <> Ubound(ArrFieldValue) Then strSql = strSql & ","
Else
strSql = strSql & ArrFieldValue(i)
If i <> Ubound(ArrFieldValue) Then strSql = strSql & ","
End If
Next
strSql = strSql & ")"

Response.Write strSql
ExecuteNoQuery()
End Sub

Public Sub doDelete()
If IsIDExists()= 0 Then
strSql = ""
If ID = "" or strCondition = "" Then strSql = ""
If ID <> "" Then strSql = "Delete * From " & strTable & " Where ID = " &ID
If strCondition <> "" Then strSql = "Delete * From " & strTable & " Where " & strCondition
Response.Write strSql
ExecuteNoQuery()
Status = 0
Else
Message "请设置正确的ID",1
End If
End Sub

'为自己写完整的 Sql 语句做准备
Public Property Let SetSql(ByRef Sql)
strSql = ""
strSql = Sql
End Property

'执行 Sql 语句。返回无集录集的语句
Public Sub ExecuteNoQuery()
If strSql <> "" Then
Command()
With objCmd
.CommandText = strSql
.Execute
End With
End If
End Sub

'执行 Sql 语句。返回有记录集的语句,这里的 objRecordset 应该为一个 Recordset 对象
Public Sub ExecuteQuery(ByRef PreArray)
Recordset()
Command()
objCmd.CommandText = strSql
Set objRs = objCmd.Execute
Response.Write strsql
'执行 Sql 语句
'为方便前台调用，在这里将在外部生成一个2维数组，故在调用前请在外部声明一个变量
' ExecuteQuery
If Not objRs.EOF Then PreArray = objRs.GetRows()
ClearRecordSet()
ClearConnection()
ClearCommand()
End Sub

Private Sub Class_Terminate()
If objConn.State = 1 Then
Call ClearConnection()
End If
On Error Resume Next
Call ClearRecord()
Call ClearCommand()
End Sub


End Class



'=========使用示例
Dim test,i
Dim t
Set test = New Cls_dbOperate

With test
.TableName = "NewsCategory"
.Fields = "ID|||NewsCateName|||BelongID"
' .SetID = 5
' .Condition = "ABC = 4"
'查询用的
.doQuery t

' ==========第二种方式
' .SetSql = "Select ID,NewsCateName,BelongID From NewsCategory"
' .ExecuteQuery t

'修改用的
.SetID = 1
.Fields = "BelongID"
.FieldsValue = 3
.doUpdate

'添加用的
.Fields = "NewsCateName|||BelongID"
.FieldsValue = "我的测试|||0"
.doAdd

'删除时使用
.SetID = 68
.TableName = "NewsCategory"
.doDelete

End With
%>

<% If IsArray(t) = True Then %>
<table border='1' width='300'>
<%
For i = 0 To UBound(t,2) %>
<tr>
<td><%=t(0,i)%></td>
<td><%=t(1,i)%></td>
<td><%=t(2,i)%></td>
</tr>
<% Next %>
</table>
<%
Else
Response.Write "错误,没有数据"
End If
%>