在线管理数据库的步骤

ASP.NET用于Web模式维护和管理数据,实现了数据库的创建和SQL语法的运行、数据的导入和导出,下面就让中国E盟技术频道小编带大家进入下文了解一下在线管理数据库的步骤吧!
ClassRLManDBCls
PrivatesDBPath,RLConn,sDBType,sServerName,sUserName,sPassword
PublicCount
PrivateSubClass_Initialize()
sDBType=""
EndSub
PrivateSubClass_Terminate()
IfIsObject(RlConn)Then
RlConn.Close
SetRlConn=Nothing
Endif
EndSub
PublicPropertyLetDBType(ByValstrVar)
sDBType=strVar
EndProperty
PublicPropertyLetServerName(ByValstrVar)
sServerName=strVar
EndProperty
PublicPropertyLetUserName(ByValstrVar)
sUserName=strVar
EndProperty
PublicPropertyLetPassword(ByValstrVar)
sPassword=strVar
EndProperty
'设置数据库路径
PublicPropertyLetDBPath(ByValstrVar)
sDBPath=strVar
SelectCasesDBType
Case"SQL"
StrServer=sServerName'数据库服务器名
StrUid=sUserName'您的登录帐号
StrSaPwd=sPassword'您的登录密码
StrDbName=sDBPath'您的数据库名称
sDBPath="driver={SQLserver};server="&StrServer&";uid="&StrUid&";pwd="&StrSaPwd&";database="&StrDbName
Case"ACCESS",""
sDBPath="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&Server.MapPath(sDBPath)
EndSelect
CheckDataRLConn,sDbPath
EndProperty

'检查数据库链接,(变量名,连接字串)
PrivateSubCheckData(DataConn,ConnStr)
OnErrorResumeNext
SetDataConn=Server.CreateObject("ADODB.Connection")
DataConn.OpenConnStr
IfErrThen
Err.Clear
SetDataConn=Nothing
ErrMsg("数据库连接出错:"&Replace(ConnStr,"\","\\")&",\n请检查连接字串,确认您输入的数据库信息是否正确。")
Response.End
EndIf
EndSub
'检查表是否存在
FunctionCheckTable(TableName)
OnErrorResumeNext
RLConn.Execute("select*From"&TableName)
IfErr.Number<>0Then
Err.Clear()
CallErrMsg("错误提示:"&Err.Description)
CheckTable=False
Else
CheckTable=True
EndIf
EndFunction

'错误提示信息(消息)
PrivateSubErrMsg(msg)
Response.Writemsg
Response.Flush
EndSub
'---------------------------------------字段值的操作-----------------------------------------------
'修改字段的值
PublicSubupColumn(ByValTableName,ByValColumnName,ByValValueText,ByValWhereStr)
OnErrorResumeNext
IfWhereStr<>""Then
IfInStr(WhereStr,"Where")<=0Then
WhereStr="Where"&WhereStr
Endif
Else
WhereStr=""
Endif
RLConn.Execute("update"&TableName&"set"&ColumnName&"="&ValueText&""&WhereStr)
IfErr.Number<>0Then
CallErrMsg("错误提示:"&Err.Description)
Err.Clear()
EndIf

EndSub

'执行SQL语句
PublicSubExecute(StrSql)
SetRsCount=Server.CreateObject("ADODB.RecordSet")
OnErrorResumeNext
RsCount=RLConn.Execute(StrSql)
IfLeft(StrSql,12)="SelectCount"ThenCount=RsCount(0)
IfErr.Number<>0Then
CallErrMsg("错误提示:"&Err.Description)
Err.Clear()
EndIf
RsCount.Close
SetRsCount=Nothing
EndSub
'---------------------------------------索引(Index),视图(View),主键操作-----------------------------------------------
'添加字段索引
PublicFunctionAddIndex(ByValTableName,ByValIndexName,ByValValueText)
OnErrorResumeNext
RLConn.Execute("CREATEINDEX"&IndexName&"ON["&TableName&"](["&ValueText&"])")
IfErr.Number<>0Then
CallErrMsg("在"&TableName&"表新建"&IndexName&"索引错误,原因"&Err.Description&"请手工修改该索引。")
Err.Clear()
AddIndex=False
Else
AddIndex=True
EndIf
EndFunction

'删除表索引
PublicFunctionDelIndex(ByValTableName,ByValIndexName)
OnErrorResumeNext
RLConn.Execute("drop空格INDEX["&TableName&"]."&IndexName)
IfErr.Number<>0Then
CallErrMsg("在"&TableName&"表删除"&IndexName&"索引错误,原因"&Err.Description&"请手工删除该索引。")
Err.Clear()
DelIndex=False
Else
DelIndex=True
EndIf
EndFunction
'更改表TableName的定义把字段ColumnName设为主键
PublicFunctionAddPRIMARYKEY(ByValTableName,ByValColumnName)
OnErrorResumeNext
TableName=Replace(Replace(TableName,"[",""),"]","")
RLConn.Execute("ALTERTABLE"&TableName&"ADDCONSTRAINTPK_"&TableName&"PRIMARYKEY("&ColumnName&")")
IfErr.Number<>0Then
CallErrMsg("在"&TableName&"将字段"&ColumnName&"添加为主键时出错,原因"&Err.Description&"请手工修改该字段属性。")
Err.Clear()
AddPRIMARYKEY=False
Else
AddPRIMARYKEY=True
EndIf
EndFunction
'更改表TableName的定义把字段ColumnName主键的定义删除
PublicFunctionDelPRIMARYKEY(ByValTableName,ByValColumnName)
OnErrorResumeNext
RLConn.Execute("ALTERTABLE"&TableName&"drop空格PRIMARYKEY("&ColumnName&")")
IfErr.Number<>0Then
CallErrMsg("在"&TableName&"将字段"&ColumnName&"主键的定义删除时出错,原因"&Err.Description&"请手工修改该字段属性。")
Err.Clear()
DelPRIMARYKEY=False
Else
DelPRIMARYKEY=True
EndIf
EndFunction
'检查主键是否存在,返回该表的主键名
FunctionGetPrimaryKey(TableName)
onerrorResumeNext
DimRsPrimary
GetPrimaryKey=""
SetRsPrimary=RLConn.OpenSchema(28,Array(Empty,Empty,TableName))
IfNotRsPrimary.EofThenGetPrimaryKey=RsPrimary("COLUMN_NAME")
SetRsPrimary=Nothing
IfErr.Number<>0Then
CallErrMsg("数据库不支持检测数据表"&TableName&"的主键。原因:"&Err.Description)
Err.Clear()
EndIf
EndFunction
'---------------------------------------表结构操作-----------------------------------------------
'添加新字段
PublicFunctionAddColumn(TableName,ColumnName,ColumnType)
OnErrorResumeNext
RLConn.Execute("AlterTable["&TableName&"]Add["&ColumnName&"]"&ColumnType&"")
IfErrThen
ErrMsg("新建"&TableName&"表中字段错误,请手动将数据库中"&ColumnName&"字段建立,属性为"&ColumnType&",原因"&Err.Description)
Err.Clear
AddColumn=False
Else
AddColumn=True
EndIf
EndFunction
'更改字段通用函数
PublicFunctionModColumn(TableName,ColumnName,ColumnType)
OnErrorResumeNext
RLConn.Execute("AlterTable["&TableName&"]AlterColumn["&ColumnName&"]"&ColumnType&"")
IfErrThen
CallErrMsg("更改"&TableName&"表中字段属性错误,请手动将数据库中"&ColumnName&"字段更改为"&ColumnType&"属性,原因"&Err.Description)
Err.Clear
ModColumn=False
Else
ModColumn=True
EndIf
EndFunction
'删除字段通用函数
PublicFunctionDelColumn(TableName,ColumnName)
OnErrorResumeNext
IfsDBType="SQL"THen
RLConn.Execute("AlterTable["&TableName&"]drop空格Column["&ColumnName&"]")
Else
RLConn.Execute("AlterTable["&TableName&"]drop空格["&ColumnName&"]")
Endif
IfErrThen
CallErrMsg("删除"&TableName&"表中字段错误,请手动将数据库中"&ColumnName&"字段删除,原因"&Err.Description)
Err.Clear
DelColumn=False
Else
DelColumn=True
EndIf
EndFunction
'---------------------------------------表操作---------------------------------------------------
'打开表名对象
PrivateSubReNameTableConn()
OnErrorResumeNext
SetobjADOXDatabase=Server.CreateObject("ADOX.Catalog")
objADOXDatabase.ActiveConnection=ConnStr
IfErrThen
ErrMsg("建立更改表名对象出错,您所要升级的空间不支持此对象,您很可能需要手动更改表名,原因"&Err.Description)
Response.End
Err.Clear
EndIf
EndSub
'关闭表名对象
PrivateSubCloseReNameTableConn()
SetobjADOXDatabase=Nothing
Conn.Close
SetConn=Nothing
EndSub
'更改数据库表名,入口参数:老表名、新表名
PublicFunctionRenameTable(oldName,newName)
OnErrorResumeNext
CallReNameTableConn
objADOXDatabase.Tables(oldName).Name=newName
IfErrThen
CallErrMsg("更改表名错误,请手动将数据库中"&oldName&"表名更改为<B>"&newName&",原因"&Err.Description)
Err.Clear
RenameTable=False
Else
RenameTable=True
EndIf
CallCloseReNameTableConn
EndFunction
'删除表通用函数
PublicFunctionDelTable(TableName)
OnErrorResumeNext
RLConn.Execute("drop空格Table["&TableName&"]")
IfErrThen
ErrMsg("删除"&TableName&"表错误,请手动将数据库中"&TableName&"表删除,原因"&Err.Description)
Err.Clear
DelTable=False
Else
DelTable=True
EndIf
EndFunction

'建立新表
PublicFunctionCreateTable(ByValTableName,ByValFieldList)
DimStrSql
IfsDBType="SQL"THen
StrSql="CREATETABLE["&TableName&"]("&FieldList&")"
Else
StrSql="CREATETABLE["&TableName&"]"
Endif
RLConn.Execute(StrSql)
IfErr.Number<>0Then
CallErrMsg("新建"&TableName&"表错误,原因"&Err.Description&"")
Err.Clear()
CreateTable=False
Else
CreateTable=True
EndIf
EndFunction


'---------------------------------------数据库操作-----------------------------------------------

'建立数据库文件
PublicfunctionCreateDBfile(byValdbFileName,byValSavePath)
OnerrorresumeNext
SavePath=Replace(SavePath,"/","\")
IfRight(SavePath,1)<>"\"OrRight(SavePath,1)<>"/"ThenSavePath=Trim(SavePath)&"\"
IfLeft(dbFileName,1)="\"OrLeft(dbFileName,1)="/"ThendbFileName=Trim(Mid(dbFileName,2,Len(dbFileName)))
IfDbExists(AppPath()&SavePath&dbFileName)Then
ErrMsg("对不起,该数据库已经存在!"&AppPath()&SavePath&dbFileName)
CreateDBfile=False
Else
Response.WriteAppPath()&SavePath&dbFileName
DimCa
SetCa=Server.CreateObject("ADOX.Catalog")
IfErr.number<>0Then
ErrMsg("无法建立,请检查错误信息
"&Err.number&"
"&Err.Description)
Err.Clear
CreateDBfile=False
Exitfunction
EndIf
callCa.Create("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&AppPath()&SavePath&dbFileName)
SetCa=Nothing
CreateDBfile=True
EndIf
Endfunction

'查找数据库文件是否存在
PrivatefunctionDbExists(byValdbPath)
OnErrorresumeNext
Dimc
Setc=Server.CreateObject("ADODB.Connection")
c.Open"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&dbPath
IfErr.number<>0Then
Err.Clear
DbExists=false
else
DbExists=True
EndIf
setc=nothing
Endfunction
'取当前真实路径
PrivatefunctionAppPath()
AppPath=Server.MapPath("./")
IfRight(AppPath,1)="\"THen
AppPath=AppPath
ELse
AppPath=AppPath&"\"
Endif
Endfunction

'删除一个数据库文件
PublicfunctionDeleteDBFile(filespec)
filespec=AppPath()&filespec
Dimfso
Setfso=CreateObject("Scripting.FileSystemObject")
IfErr.number<>0Then
ErrMsg("删除文件发生错误!请查看错误信息:"&Err.number&""&Err.Description&"
")
Err.Clear
DeleteDBFile=False
EndIf
IfDbExists(filespec)THen
callfso.DeleteFile(filespec)
DeleteDBFile=True
Else
ErrMsg("删除文件发生错误!请查看错误信息:"&Err.number&""&Err.Description&"
")
DeleteDBFile=False
ExitFunction
Endif
Setfso=Nothing
Endfunction

'修改一个数据库名
PublicfunctionRenameDBFile(filespec1,filespec2)
filespec1=AppPath()&filespec1:filespec2=AppPath()&filespec2
Dimfso
Setfso=CreateObject("Scripting.FileSystemObject")
IfErr.number<>0Then
ErrMsg("修改文件名时发生错误!请查看错误信息:"&Err.number&""&Err.Description)
Err.Clear
RenameDBFile=False
EndIf
IfDbExists(filespec1)THen
callfso.CopyFile(filespec1,filespec2,True)
callfso.DeleteFile(filespec1)
RenameDBFile=True
Else
ErrMsg("源文件不存在!!!")
RenameDBFile=False
ExitFunction
Endif
Setfso=Nothing
Endfunction
'压缩数据库
PublicFunctionCompactDBFile(strDBFileName)
DimJet_Conn_Partial
DimSourceConn
DimDestConn
DimoJetEngine
DimoFSO

Jet_Conn_Partial="Provider=Microsoft.Jet.OLEDB.4.0;Datasource="

SourceConn=Jet_Conn_Partial&AppPath()&strDBFileName
DestConn=Jet_Conn_Partial&AppPath()&"Temp"&strDBFileName
SetoFSO=Server.CreateObject("Scripting.FileSystemObject")
SetoJetEngine=Server.CreateObject("JRO.JetEngine")

WithoFSO
IfNot.FileExists(AppPath()&strDBFileName)Then
ErrMsg("数据库文件未找到!!!!")
Stop
CompactDBFile=False
ExitFunction
Else
If.FileExists(AppPath()&"Temp"&strDBFileName)Then
ErrMsg("不知道的错误!!!")
.DeleteFile(AppPath()&"Temp"&strDBFileName)
CompactDBFile=False
ExitFunction
EndIf
EndIf
EndWith

WithoJetEngine
.CompactDatabaseSourceConn,DestConn
EndWith

oFSO.DeleteFileAppPath()&strDBFileName
oFSO.MoveFileAppPath()&"Temp"&strDBFileName,AppPath()&strDBFileName

SetoFSO=Nothing
SetoJetEngine=Nothing
CompactDBFile=True
EndFunction
EndClass
DimManDb
SetManDb=NewRLManDBCls
'//---------连接SQL数据库--------------
'ManDb.DBType="SQL"
'ManDb.ServerName="TAO-KUIZU"
'ManDb.UserName="sa"
'ManDb.Password="123456"
'ManDb.DBPath="hhstuss"
'ManDb.CreateTable"cexo255","idintNotNullPRIMARYKEY,Namevarchar(20)NotNull"'建立表(表名)
'ManDb.ReNameTable"cexo255","cexo2552"'表改名(旧表名,新表名)(用组件)
'ManDb.DelTable"cexo255"'删除表(表名)
'ManDb.AddColumn"cexo255","Sex","varchar(2)null"'建立表结构(表名,字段名,数据类型)
'ManDb.ModColumn"cexo255","name","intNotnull"'修改表结构(表名,字段名,新数据类型)_
'ManDb.DelColumn"cexo255","Sex"'删除表结构(表名,字段名)
'ManDb.AddIndex"cexo255","i_ID","ID"'建立表索引(表名,索引名,索引字段名)
'ManDb.DelIndex"cexo255","i_ID"'删除表索引(表名,索引名)
'ManDb.AddPRIMARYKEY"cexo255","name"'建立表主键(表名,主键字段名)
'ManDb.DelPRIMARYKEY"cexo255","name"'删除表主键(表名,主键字段名)_
'Response.WriteManDb.GetPrimaryKey("cexo255")'取表的主键(表名)
'ManDb.upColumn"cexo255","id",12345,"name=1"'修改字段的值
'ManDb.Execute"insert空格intocexo255(id,Name)values(2,2)"'添加记录
'ManDb.Execute"Updatecexo255Setid=3WhereName=2"'修改记录
'ManDb.Execute"delete空格Fromcexo255WhereName=2"'删除记录
'ManDb.Execute("SelectCount(*)Fromcexo255"):Response.WriteManDb.Count'统计记录个数
'IfManDb.CheckTable("StudInfo")THenResponse.Write"StudInfo表存在!!!"ElseResponse.Write"StudInfo表不存在!!!"
'//-----------End--------------------------
'//---------连接Access数据库--------------
ManDb.DBType="ACCESS"
ManDb.DBPath="test.mdb"
'ManDb.CreateDBfile"test2.mdb",""'建立数据库(数据库名,保存路径)
'ManDb.DeleteDBFile("test2.mdb")'删除数据库(数据库名)
'ManDb.RenameDBFile"test2.mdb","test3.mdb"'数据库改名(旧数据库名,新数据库名)
'ManDb.CompactDBFile("test3.mdb")'压缩数据库(数据库名)
'ManDb.CreateTable"dw",""'建立表(表名)
'ManDb.ReNameTable"dw","dw2"'表改名(旧表名,新表名)(用组件)_
'ManDb.DelTable"dw"'删除表(表名)
'ManDb.AddColumn"cexo255","name","varchar(255)Notnull"'建立表结构(表名,字段名,数据类型)
'ManDb.ModColumn"cexo255","name","intNotnull"'修改表结构(表名,字段名,新数据类型)
'ManDb.DelColumn"cexo255","name"'删除表结构(表名,字段名)
'ManDb.AddIndex"cexo255","UserID","ID"'建立表索引(表名,索引名,索引字段名)
'ManDb.DelIndex"cexo255","UserID"'删除表索引(表名,索引名)_
'ManDb.AddPRIMARYKEY"cexo255","id"'建立表主键(表名,主键字段名)
'ManDb.DelPRIMARYKEY"cexo255","id"'删除表主键(表名,主键字段名)_
'Response.WriteManDb.GetPrimaryKey("cexo255")'取表的主键(表名)
'ManDb.upColumn"cexo255","id","12345","id='12'"'修改字段的值
'ManDb.Execute"insert空格intocexo255(id)values('789')"'添加记录
'ManDb.Execute"Updatecexo255Setid='wxf'Whereid='789'"'修改记录
'ManDb.Execute"delete空格Fromcexo255Whereid='wxf'"'删除记录
ManDb.Execute("SelectCount(*)Fromcexo255"):Response.WriteManDb.Count'统计记录个数
'IfManDb.CheckTable("StudInfo")THenResponse.Write"StudInfo表存在!!!"ElseResponse.Write"StudInfo表不存在!!!"
'//-----------End--------------------------
SetManDb=Nothing
%>

上文是关于在线管理数据库的步骤介绍,相信大家都有了一定的了解,想要了解更多的技术信息,请继续关注中国E盟技术频道吧!