您现在的位置是：网站首页> 编程资料编程资料
在线管理数据库 类_数据库相关_
2023-05-25
208人已围观
简介 在线管理数据库 类_数据库相关_
<%
Class RLManDBCls
Private sDBPath, RLConn, sDBType, sServerName, sUserName, sPassword
Public Count
Private Sub Class_Initialize()
sDBType = ""
End Sub
Private Sub Class_Terminate()
If IsObject(RlConn) Then
RlConn.Close
Set RlConn = Nothing
End if
End Sub
Public Property Let DBType(ByVal strVar)
sDBType = strVar
End Property
Public Property Let ServerName(ByVal strVar)
sServerName = strVar
End Property
Public Property Let UserName(ByVal strVar)
sUserName = strVar
End Property
Public Property Let Password(ByVal strVar)
sPassword = strVar
End Property
'设置数据库路径
Public Property Let DBPath(ByVal strVar)
sDBPath = strVar
Select Case sDBType
Case "SQL"
StrServer = sServerName '数据库服务器名
StrUid = sUserName '您的登录帐号
StrSaPwd = sPassword '您的登录密码
StrDbName = sDBPath '您的数据库名称
sDBPath = "driver={SQL server};server=" & StrServer & ";uid=" & StrUid & ";pwd=" & StrSaPwd & ";database=" & StrDbName
Case "ACCESS",""
sDBPath = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(sDBPath)
End Select
CheckData RLConn,sDbPath
End Property
'检查数据库链接，（变量名，连接字串）
Private Sub CheckData(DataConn,ConnStr)
On Error Resume Next
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open ConnStr
If Err Then
Err.Clear
Set DataConn = Nothing
ErrMsg("数据库连接出错:" & Replace(ConnStr,"\","\\") & "，\n请检查连接字串，确认您输入的数据库信息是否正确。")
Response.End
End If
End Sub
'检查表是否存在
Function CheckTable(TableName)
On Error Resume Next
RLConn.Execute("select * From " & TableName)
If Err.Number <> 0 Then
Err.Clear()
Call ErrMsg("错误提示：" & Err.Description)
CheckTable = False
Else
CheckTable = True
End If
End Function
'错误提示信息（消息）
Private Sub ErrMsg(msg)
Response.Write msg
Response.Flush
End Sub
'---------------------------------------字段值的操作-----------------------------------------------
'修改字段的值
Public Sub upColumn(ByVal TableName, ByVal ColumnName, ByVal ValueText,ByVal WhereStr)
On Error Resume Next
If WhereStr <> "" Then
If InStr(WhereStr,"Where ")<=0 Then
WhereStr = "Where " & WhereStr
End if
Else
WhereStr = ""
End if
RLConn.Execute("update " & TableName & " set " & ColumnName & "=" & ValueText & " " & WhereStr)
If Err.Number <> 0 Then
Call ErrMsg("错误提示：" & Err.Description)
Err.Clear()
End If
End Sub
'执行SQL语句
Public Sub Execute(StrSql)
Set RsCount=Server.CreateObject("ADODB.RecordSet")
On Error Resume Next
RsCount = RLConn.Execute(StrSql)
If Left(StrSql,12) = "Select Count" Then Count = RsCount(0)
If Err.Number <> 0 Then
Call ErrMsg("错误提示：" & Err.Description)
Err.Clear()
End If
RsCount.Close
Set RsCount = Nothing
End Sub
'---------------------------------------索引(Index),视图（View）,主键操作-----------------------------------------------
'添加字段索引
Public Function AddIndex(ByVal TableName, ByVal IndexName, ByVal ValueText)
On Error Resume Next
RLConn.Execute("CREATE INDEX " & IndexName & " ON [" & TableName & "]([" & ValueText & "])")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 表新建" & IndexName & "索引错误，原因" & Err.Description & "请手工修改该索引。")
Err.Clear()
AddIndex = False
Else
AddIndex = True
End If
End Function
'删除表索引
Public Function DelIndex(ByVal TableName, ByVal IndexName)
On Error Resume Next
RLConn.Execute("drop空格INDEX [" & TableName & "]." & IndexName)
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 表删除" & IndexName & "索引错误，原因" & Err.Description & "请手工删除该索引。")
Err.Clear()
DelIndex = False
Else
DelIndex = True
End If
End Function
'更改表TableName的定义把字段ColumnName设为主键
Public Function AddPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
TableName = Replace(Replace(TableName,"[",""),"]","")
RLConn.Execute("ALTER TABLE "& TableName & " ADD CONSTRAINT PK_"&TableName&" PRIMARY KEY (" & ColumnName & ")")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 将字段" & ColumnName & " 添加为主键时出错，原因 " & Err.Description & "请手工修改该字段属性。")
Err.Clear()
AddPRIMARYKEY = False
Else
AddPRIMARYKEY = True
End If
End Function
'更改表TableName的定义把字段ColumnName主键的定义删除
Public Function DelPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
RLConn.Execute("ALTER TABLE "& TableName & " drop空格PRIMARY KEY (" & ColumnName & ")")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 将字段" & ColumnName & " 主键的定义删除时出错，原因" & Err.Description & "请手工修改该字段属性。")
Err.Clear()
DelPRIMARYKEY = False
Else
DelPRIMARYKEY = True
End If
End Function
'检查主键是否存在，返回该表的主键名
Function GetPrimaryKey(TableName)
on error Resume Next
Dim RsPrimary
GetPrimaryKey = ""
Set RsPrimary = RLConn.OpenSchema(28,Array(Empty,Empty,TableName))
If Not RsPrimary.Eof Then GetPrimaryKey = RsPrimary("COLUMN_NAME")
Set RsPrimary = Nothing
If Err.Number <> 0 Then
Call ErrMsg("数据库不支持检测数据表 " & TableName & " 的主键。原因 ：" & Err.Description)
Err.Clear()
End If
End Function
'---------------------------------------表结构操作-----------------------------------------------
'添加新字段
Public Function AddColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
RLConn.Execute("Alter Table [" & TableName & "] Add [" & ColumnName & "] " & ColumnType & "")
If Err Then
ErrMsg ("新建 " & TableName & " 表中字段错误，请手动将数据库中 " & ColumnName & " 字段建立，属性为 "&ColumnType& "，原因" & Err.Description)
Err.Clear
AddColumn = False
Else
AddColumn = True
End If
End Function
'更改字段通用函数
Public Function ModColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
RLConn.Execute("Alter Table [" & TableName & "] Alter Column [" & ColumnName & "] " & ColumnType & "")
If Err Then
Call ErrMsg ("更改 " & TableName & " 表中字段属性错误，请手动将数据库中 " & ColumnName & " 字段更改为 " & ColumnType & " 属性，原因" & Err.Description)
Err.Clear
ModColumn = False
Else
ModColumn = True
End If
End Function
'删除字段通用函数
Public Function DelColumn(TableName,ColumnName)
On Error Resume Next
If sDBType = "SQL" THen
RLConn.Execute("Alter Table [" & TableName & "] drop空格Column [" & ColumnName & "]")
Else
RLConn.Execute("Alter Table [" & TableName & "] drop空格[" & ColumnName & "]")
End if
If Err Then
Call ErrMsg ("删除 " & TableName & " 表中字段错误，请手动将数据库中 " & ColumnName & " 字段删除，原因" & Err.Description)
Err.Clear
DelColumn = False
Else
DelColumn = True
End If
End Function
'---------------------------------------表操作---------------------------------------------------
'打开表名对象
Private Sub ReNameTableConn()
On Error Resume Next
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.ActiveConnection = ConnStr
If Err Then
ErrMsg("建立更改表名对象出错，您所要升级的空间不支持此对象，您很可能需要手动更改表名，原因" & Err.Description)
Response.End
Err.Clear
End If
End Sub
'关闭表名对象
Private Sub CloseReNameTableConn()
Set objADOXDatabase = Nothing
Conn.Close
Set Conn=Nothing
End Sub
'更改数据库表名，入口参数：老表名、新表名
Public Function RenameTable(oldName, newName)
On Error Resume Next
Call ReNameTableConn
objADOXDatabase.Tables(oldName).Name = newName
If Err Then
Call ErrMsg ("更改表名错误，请手动将数据库中 " & oldName & " 表名更改为 < B>" & newName & "，原因" & Err.Description)
Err.Clear
RenameTable = False
Else
RenameTable = True
End If
Call CloseReNameTableConn
End Function
'删除表通用函数
Public Function DelTable(TableName)
On Error Resume Next
RLConn.Execute("drop空格Table [" & TableName & "]")
If Err Then
ErrMsg ("删除 " & TableName & " 表错误，请手动将数据库中 " & TableName&" 表删除，原因" &
Class RLManDBCls
Private sDBPath, RLConn, sDBType, sServerName, sUserName, sPassword
Public Count
Private Sub Class_Initialize()
sDBType = ""
End Sub
Private Sub Class_Terminate()
If IsObject(RlConn) Then
RlConn.Close
Set RlConn = Nothing
End if
End Sub
Public Property Let DBType(ByVal strVar)
sDBType = strVar
End Property
Public Property Let ServerName(ByVal strVar)
sServerName = strVar
End Property
Public Property Let UserName(ByVal strVar)
sUserName = strVar
End Property
Public Property Let Password(ByVal strVar)
sPassword = strVar
End Property
'设置数据库路径
Public Property Let DBPath(ByVal strVar)
sDBPath = strVar
Select Case sDBType
Case "SQL"
StrServer = sServerName '数据库服务器名
StrUid = sUserName '您的登录帐号
StrSaPwd = sPassword '您的登录密码
StrDbName = sDBPath '您的数据库名称
sDBPath = "driver={SQL server};server=" & StrServer & ";uid=" & StrUid & ";pwd=" & StrSaPwd & ";database=" & StrDbName
Case "ACCESS",""
sDBPath = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(sDBPath)
End Select
CheckData RLConn,sDbPath
End Property
'检查数据库链接，（变量名，连接字串）
Private Sub CheckData(DataConn,ConnStr)
On Error Resume Next
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open ConnStr
If Err Then
Err.Clear
Set DataConn = Nothing
ErrMsg("数据库连接出错:" & Replace(ConnStr,"\","\\") & "，\n请检查连接字串，确认您输入的数据库信息是否正确。")
Response.End
End If
End Sub
'检查表是否存在
Function CheckTable(TableName)
On Error Resume Next
RLConn.Execute("select * From " & TableName)
If Err.Number <> 0 Then
Err.Clear()
Call ErrMsg("错误提示：" & Err.Description)
CheckTable = False
Else
CheckTable = True
End If
End Function
'错误提示信息（消息）
Private Sub ErrMsg(msg)
Response.Write msg
Response.Flush
End Sub
'---------------------------------------字段值的操作-----------------------------------------------
'修改字段的值
Public Sub upColumn(ByVal TableName, ByVal ColumnName, ByVal ValueText,ByVal WhereStr)
On Error Resume Next
If WhereStr <> "" Then
If InStr(WhereStr,"Where ")<=0 Then
WhereStr = "Where " & WhereStr
End if
Else
WhereStr = ""
End if
RLConn.Execute("update " & TableName & " set " & ColumnName & "=" & ValueText & " " & WhereStr)
If Err.Number <> 0 Then
Call ErrMsg("错误提示：" & Err.Description)
Err.Clear()
End If
End Sub
'执行SQL语句
Public Sub Execute(StrSql)
Set RsCount=Server.CreateObject("ADODB.RecordSet")
On Error Resume Next
RsCount = RLConn.Execute(StrSql)
If Left(StrSql,12) = "Select Count" Then Count = RsCount(0)
If Err.Number <> 0 Then
Call ErrMsg("错误提示：" & Err.Description)
Err.Clear()
End If
RsCount.Close
Set RsCount = Nothing
End Sub
'---------------------------------------索引(Index),视图（View）,主键操作-----------------------------------------------
'添加字段索引
Public Function AddIndex(ByVal TableName, ByVal IndexName, ByVal ValueText)
On Error Resume Next
RLConn.Execute("CREATE INDEX " & IndexName & " ON [" & TableName & "]([" & ValueText & "])")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 表新建" & IndexName & "索引错误，原因" & Err.Description & "请手工修改该索引。")
Err.Clear()
AddIndex = False
Else
AddIndex = True
End If
End Function
'删除表索引
Public Function DelIndex(ByVal TableName, ByVal IndexName)
On Error Resume Next
RLConn.Execute("drop空格INDEX [" & TableName & "]." & IndexName)
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 表删除" & IndexName & "索引错误，原因" & Err.Description & "请手工删除该索引。")
Err.Clear()
DelIndex = False
Else
DelIndex = True
End If
End Function
'更改表TableName的定义把字段ColumnName设为主键
Public Function AddPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
TableName = Replace(Replace(TableName,"[",""),"]","")
RLConn.Execute("ALTER TABLE "& TableName & " ADD CONSTRAINT PK_"&TableName&" PRIMARY KEY (" & ColumnName & ")")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 将字段" & ColumnName & " 添加为主键时出错，原因 " & Err.Description & "请手工修改该字段属性。")
Err.Clear()
AddPRIMARYKEY = False
Else
AddPRIMARYKEY = True
End If
End Function
'更改表TableName的定义把字段ColumnName主键的定义删除
Public Function DelPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
RLConn.Execute("ALTER TABLE "& TableName & " drop空格PRIMARY KEY (" & ColumnName & ")")
If Err.Number <> 0 Then
Call ErrMsg ("在 " & TableName & " 将字段" & ColumnName & " 主键的定义删除时出错，原因" & Err.Description & "请手工修改该字段属性。")
Err.Clear()
DelPRIMARYKEY = False
Else
DelPRIMARYKEY = True
End If
End Function
'检查主键是否存在，返回该表的主键名
Function GetPrimaryKey(TableName)
on error Resume Next
Dim RsPrimary
GetPrimaryKey = ""
Set RsPrimary = RLConn.OpenSchema(28,Array(Empty,Empty,TableName))
If Not RsPrimary.Eof Then GetPrimaryKey = RsPrimary("COLUMN_NAME")
Set RsPrimary = Nothing
If Err.Number <> 0 Then
Call ErrMsg("数据库不支持检测数据表 " & TableName & " 的主键。原因 ：" & Err.Description)
Err.Clear()
End If
End Function
'---------------------------------------表结构操作-----------------------------------------------
'添加新字段
Public Function AddColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
RLConn.Execute("Alter Table [" & TableName & "] Add [" & ColumnName & "] " & ColumnType & "")
If Err Then
ErrMsg ("新建 " & TableName & " 表中字段错误，请手动将数据库中 " & ColumnName & " 字段建立，属性为 "&ColumnType& "，原因" & Err.Description)
Err.Clear
AddColumn = False
Else
AddColumn = True
End If
End Function
'更改字段通用函数
Public Function ModColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
RLConn.Execute("Alter Table [" & TableName & "] Alter Column [" & ColumnName & "] " & ColumnType & "")
If Err Then
Call ErrMsg ("更改 " & TableName & " 表中字段属性错误，请手动将数据库中 " & ColumnName & " 字段更改为 " & ColumnType & " 属性，原因" & Err.Description)
Err.Clear
ModColumn = False
Else
ModColumn = True
End If
End Function
'删除字段通用函数
Public Function DelColumn(TableName,ColumnName)
On Error Resume Next
If sDBType = "SQL" THen
RLConn.Execute("Alter Table [" & TableName & "] drop空格Column [" & ColumnName & "]")
Else
RLConn.Execute("Alter Table [" & TableName & "] drop空格[" & ColumnName & "]")
End if
If Err Then
Call ErrMsg ("删除 " & TableName & " 表中字段错误，请手动将数据库中 " & ColumnName & " 字段删除，原因" & Err.Description)
Err.Clear
DelColumn = False
Else
DelColumn = True
End If
End Function
'---------------------------------------表操作---------------------------------------------------
'打开表名对象
Private Sub ReNameTableConn()
On Error Resume Next
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.ActiveConnection = ConnStr
If Err Then
ErrMsg("建立更改表名对象出错，您所要升级的空间不支持此对象，您很可能需要手动更改表名，原因" & Err.Description)
Response.End
Err.Clear
End If
End Sub
'关闭表名对象
Private Sub CloseReNameTableConn()
Set objADOXDatabase = Nothing
Conn.Close
Set Conn=Nothing
End Sub
'更改数据库表名，入口参数：老表名、新表名
Public Function RenameTable(oldName, newName)
On Error Resume Next
Call ReNameTableConn
objADOXDatabase.Tables(oldName).Name = newName
If Err Then
Call ErrMsg ("更改表名错误，请手动将数据库中 " & oldName & " 表名更改为 < B>" & newName & "，原因" & Err.Description)
Err.Clear
RenameTable = False
Else
RenameTable = True
End If
Call CloseReNameTableConn
End Function
'删除表通用函数
Public Function DelTable(TableName)
On Error Resume Next
RLConn.Execute("drop空格Table [" & TableName & "]")
If Err Then
ErrMsg ("删除 " & TableName & " 表错误，请手动将数据库中 " & TableName&" 表删除，原因" &
