ASP.NET is used to maintain and manage data in the web mode, realizing the creation of databases, the operation of SQL syntax, and the import and export of data. Let the editor of the Foxin Technology Channel take you to the following article to learn about the steps of online database management!
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
'Set the database path
Public Property Let DBPath(ByVal strVar)
sDBPath = strVar
Select Case sDBType
Case "SQL"
StrServer = sServerName 'Database Server Name
StrUid = sUserName 'Your login account
StrSaPwd = sPassword 'Your login password
StrDbName = sDBPath 'Your database name
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
'Check the database link, (variable name, connection string)
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("Database connection error:" & Replace(ConnStr,"/","//") & ", /nPlease check the connection string to confirm whether the database information you entered is correct.")
Response.End
End If
End Sub
'Check whether the table exists
Function CheckTable(TableName)
On Error Resume Next
RLConn.Execute("select * From " & TableName)
If Err.Number <> 0 Then
Err.Clear()
Call ErrMsg("Error prompt:" & Err.Description)
CheckTable = False
Else
CheckTable = True
End If
End Function
'Error message (message)
Private Sub ErrMsg(msg)
Response.Write msg
Response.Flush
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Modify the value of the field
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("Error prompt:" & Err.Description)
Err.Clear()
End If
End Sub
'Execute SQL statement
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("Error prompt:" & Err.Description)
Err.Clear()
End If
RsCount.Close
Set RsCount = Nothing
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Add field index
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 ("Create in " & TableName & "TableName & "TableName & "IndexName & "IndexName & "Cause" & Err.Description & "Please modify the index manually.")
Err.Clear()
AddIndex = False
Else
AddIndex = True
End If
End Function
'Delete the table index
Public Function DelIndex(ByVal TableName, ByVal IndexName)
On Error Resume Next
RLConn.Execute("drop space INDEX [" & TableName & "]." & IndexName)
If Err.Number <> 0 Then
Call ErrMsg ("In " & TableName & "TableDelete" & IndexName & "Index Error, Cause" & Err.Description & "Please delete the index manually.")
Err.Clear()
DelIndex = False
Else
DelIndex = True
End If
End Function
'Change the definition of table TableName to set the field ColumnName as the primary key
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 ("Error when adding " & TableName & " Field" & ColumnName & " as primary key, reason " & Err.Description & "Please modify the field properties manually.")
Err.Clear()
AddPRIMARYKEY = False
Else
AddPRIMARYKEY = True
End If
End Function
'Change the definition of table TableName to delete the definition of field ColumnName primary key
Public Function DelPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
RLConn.Execute("ALTER TABLE "& TableName & " drop space PRIMARY KEY (" & ColumnName & ")")
If Err.Number <> 0 Then
Call ErrMsg ("An error occurred while deleting the definition of the primary key " & TableName & " "According to "Asking the field" & ColumnName & ", reason" & Err.Description & "Please modify the field properties manually.")
Err.Clear()
DelPRIMARYKEY = False
Else
DelPRIMARYKEY = True
End If
End Function
'Check whether the primary key exists and return the primary key name of the table
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("The database does not support the primary key for detecting data table " & TableName & ". Reason: " & Err.Description)
Err.Clear()
End If
End Function
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Add a new field
Public Function AddColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
RLConn.Execute("Alter Table [" & TableName & "] Add [" & ColumnName & "] " & ColumnType & "")
If Err Then
ErrMsg ("New" & TableName & " field error in the table, please manually create the " & ColumnName & " field in the database, the attribute is "&ColumnType& " , reason" & Err.Description)
Err.Clear
AddColumn = False
Else
AddColumn = True
End If
End Function
'Change field generic 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 ("Change" & TableName & "The field attribute in the table is incorrect, please manually change the database " & ColumnName & " field to " & ColumnType & " Properties, Causes" & Err.Description)
Err.Clear
ModColumn = False
Else
ModColumn = True
End If
End Function
'Delete field generic function
Public Function DelColumn(TableName,ColumnName)
On Error Resume Next
If sDBType = "SQL" THEn
RLConn.Execute("Alter Table [" & TableName & "] drop space Column [" & ColumnName & "]")
Else
RLConn.Execute("Alter Table [" & TableName & "] drop space[" & ColumnName & "]")
End if
If Err Then
Call ErrMsg ("Delete" & TableName & "Field errors in the table, please manually delete " & ColumnName & " Field Delete, Cause" & Err.Description)
Err.Clear
DelColumn = False
Else
DelColumn = True
End If
End Function
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Open the table name object
Private Sub ReNameTableConn()
On Error Resume Next
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.ActiveConnection = ConnStr
If Err Then
ErrMsg("An error occurred when creating a change table name object. The space you are upgrading does not support this object. You are most likely to need to change the table name manually, reason" & Err.Description)
Response.End
Err.Clear
End If
End Sub
'Close the table name object
Private Sub CloseReNameTableConn()
Set objADOXDatabase = Nothing
Conn.Close
Set Conn=Nothing
End Sub
'Change the database table name, entry parameters: old table name, new table name
Public Function RenameTable(oldName, newName)
On Error Resume Next
Call ReNameTableConn
objADOXDatabase.Tables(oldName).Name = newName
If Err Then
Call ErrMsg ("Change the table name error, please manually change the table name in the database " & oldName & " table name to < B>" & newName & ", reason" & Err.Description)
Err.Clear
RenameTable = False
Else
RenameTable = True
End If
Call CloseReNameTableConn
End Function
'Delete table general function
Public Function DelTable(TableName)
On Error Resume Next
RLConn.Execute("drop space Table [" & TableName & "]")
If Err Then
ErrMsg ("Delete" & TableName & "Table error, please manually delete the " & TableName & " Table in the database, reason" & Err.Description)
Err.Clear
DelTable = False
Else
DelTable = True
End If
End Function
'Create a new table
Public Function CreateTable(ByVal TableName,ByVal FieldList)
Dim StrSql
If sDBType = "SQL" THEn
StrSql = "CREATE TABLE [" & TableName & "]( " & FieldList & ")"
Else
StrSql = "CREATE TABLE [" & TableName & "]"
End if
RLConn.Execute(StrSql)
If Err.Number <> 0 Then
Call ErrMsg("New" & TableName & "Table Error, Cause" & Err.Description & "")
Err.Clear()
CreateTable = False
Else
CreateTable = True
End If
End Function
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Create a database file
Public function CreateDBfile(byVal dbFileName, byVal SavePath)
On error resume Next
SavePath = Replace(SavePath,"/","/")
If Right(SavePath,1)<>"/" Or Right(SavePath,1)<>"/" Then SavePath = Trim(SavePath) & "/"
If Left(dbFileName,1)="/" Or Left(dbFileName,1)="/" Then dbFileName = Trim(Mid(dbFileName,2,Len(dbFileName)))
If DbExists(AppPath() & SavePath & dbFileName) Then
ErrMsg("Sorry, this database already exists!" & AppPath() & SavePath & dbFileName)
CreateDBfile = False
Else
Response.Write AppPath() & SavePath & dbFileName
Dim Ca
Set Ca = Server.CreateObject("ADOX.Catalog")
If Err.number<>0 Then
ErrMsg("Cannot be established, please check the error message
" & Err.number & "
" & Err.Description)
Err.Clear
CreateDBfile = False
Exit function
End If
call Ca.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath() & SavePath & dbFileName)
Set Ca = Nothing
CreateDBfile = True
End If
End function
'Find whether the database file exists
Private function DbExists(byVal dbPath)
On Error resume Next
Dim
Set c = Server.CreateObject("ADODB.Connection")
c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
If Err.number<>0 Then
Err.Clear
DbExists = false
else
DbExists = True
End If
set c = nothing
End function
'Take the current real path
Private function AppPath()
AppPath = Server.MapPath("./")
If Right(AppPath,1) = "/" THEn
AppPath = AppPath
ELse
AppPath = AppPath & "/"
End if
End function
'Delete a database file
Public function DeleteDBFile(filespec)
filespec = AppPath() & filespec
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Err.number<>0 Then
ErrMsg("An error occurred when deleting the file! Please check the error message: " & Err.number & " " & Err.Description & "
")
Err.Clear
DeleteDBFile = False
End If
If DbExists(filespec) THEn
call fso.DeleteFile(filespec)
DeleteDBFile = True
Else
ErrMsg("An error occurred when deleting the file! Please view the error message:" & Err.number & " " & Err.Description & "
")
DeleteDBFile = False
Exit Function
End if
Set fso = Nothing
End function
'Modify a database name
Public function RenameDBFile(filespec1,filespec2)
filespec1 = AppPath() & filespec1:filespec2 = AppPath() & filespec2
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Err.number<>0 Then
ErrMsg("An error occurred while modifying the file name! Please check the error message:" & Err.number & " " & Err.Description)
Err.Clear
RenameDBFile = False
End If
If DbExists(filespec1) THEn
call fso.CopyFile(filespec1,filespec2,True)
call fso.DeleteFile(filespec1)
RenameDBFile = True
Else
ErrMsg("The source file does not exist!!!")
RenameDBFile = False
Exit Function
End if
Set fso = Nothing
End function
'Compressed database
Public Function CompactDBFile(strDBFileName)
Dim Jet_Conn_Partial
Dim SourceConn
Dim DestConn
Dim oJetEngine
Dim oFSO
Jet_Conn_Partial = "Provider=Microsoft.Jet.OLEDB.4.0; Data source="
SourceConn = Jet_Conn_Partial & AppPath() & strDBFileName
DestConn = Jet_Conn_Partial & AppPath() & "Temp" & strDBFileName
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set oJetEngine = Server.CreateObject("JRO.JetEngine")
With oFSO
If Not .FileExists(AppPath() & strDBFileName) Then
ErrMsg ("Database file not found!!!" )
Stop
CompactDBFile = False
Exit Function
Else
If .FileExists( AppPath() & "Temp" & strDBFileName) Then
ErrMsg("Unknown error!!!")
.DeleteFile (AppPath() & "Temp" & strDBFileName)
CompactDBFile = False
Exit Function
End If
End If
End With
With oJetEngine
.CompactDatabase SourceConn, DestConn
End With
oFSO.DeleteFile AppPath() & strDBFileName
oFSO.MoveFile AppPath() & "Temp" & strDBFileName,AppPath() & strDBFileName
Set oFSO = Nothing
Set oJetEngine = Nothing
CompactDBFile = True
End Function
End Class
Dim ManDb
Set ManDb = New RLManDBCls
'//---------------------------------------------------------------------------------------------------------------------------
'ManDb.DBType = "SQL"
'ManDb.ServerName = "TAO-KUIZU"
'ManDb.UserName = "sa"
'ManDb.Password = "123456"
'ManDb.DBPath = "hhstuss"
'ManDb.CreateTable "cexo255","id int Not Null PRIMARY KEY, Name varchar(20) Not Null" 'Create table (table name)
'ManDb.ReNameTable "cexo255","cexo2552" 'Table name (old table name, new table name) (using component)
'ManDb.DelTable "cexo255" 'Delete table (table name)
'ManDb.AddColumn "cexo255", "Sex", "varchar(2) null" 'Create a table structure (table name, field name, data type)
'ManDb.ModColumn "cexo255", "name", "int Not null" 'Modify the table structure (table name, field name, new data type) _
'ManDb.DelColumn "cexo255", "Sex" 'Delete the table structure (table name, field name)
'ManDb.AddIndex "cexo255", "i_ID", "ID" 'Create table index (table name, index name, index field name)
'ManDb.DelIndex "cexo255", "i_ID" 'Delete the table index (table name, index name)
'ManDb.AddPRIMARYKEY "cexo255","name" 'Create the table primary key (table name, primary key field name)
'ManDb.DelPRIMARYKEY "cexo255","name" 'Delete the table primary key (table name, primary key field name)_
'Response.Write ManDb.GetPrimaryKey("cexo255") 'Get the primary key of the table (table name)
'ManDb.upColumn "cexo255","id",12345,"name = 1" 'Modify the value of the field
'ManDb.Execute "insert space into cexo255(id,Name) values (2,2)" 'Add record
'ManDb.Execute "Update cexo255 Set id = 3 Where Name = 2" 'Modify record
'ManDb.Execute "delete space From cexo255 Where Name = 2" 'Delete record
'ManDb.Execute("Select Count(*) From cexo255"):Response.Write ManDb.Count 'Statistics number of records
'If ManDb.CheckTable("StudInfo") THEn Response.Write "StudInfo table exists!!!" Else Response.Write "StudInfo table does not exist!!!"
'//---------------------------------------------------------------------------------------------------------------------------
'//---------------------------------------------------------------------------------------------------------------------------
ManDb.DBType = "ACCESS"
ManDb.DBPath = "test.mdb"
'ManDb.CreateDBfile "test2.mdb","" 'Create database (database name, save path)
'ManDb.DeleteDBFile("test2.mdb") 'Delete database (database name)
'ManDb.RenameDBFile "test2.mdb","test3.mdb" 'Domain name (old database name, new database name)
'ManDb.CompactDBFile("test3.mdb") 'Compressed database (database name)
'ManDb.CreateTable "dw","" 'Create table (table name)
'ManDb.ReNameTable "dw","dw2" 'Table name (old table name, new table name) (using component)_
'ManDb.DelTable "dw" 'Delete table (table name)
'ManDb.AddColumn "cexo255", "name", "varchar(255) Not null" 'Create a table structure (table name, field name, data type)
'ManDb.ModColumn "cexo255", "name", "int Not null" 'Modify the table structure (table name, field name, new data type)
'ManDb.DelColumn "cexo255", "name" 'Delete the table structure (table name, field name)
'ManDb.AddIndex "cexo255", "UserID", "ID" 'Create table index (table name, index name, index field name)
'ManDb.DelIndex "cexo255", "UserID" 'Delete the table index (table name, index name) _
'ManDb.AddPRIMARYKEY "cexo255","id" 'Create the table primary key (table name, primary key field name)
'ManDb.DelPRIMARYKEY "cexo255","id" 'Delete the table primary key (table name, primary key field name)_
'Response.Write ManDb.GetPrimaryKey("cexo255") 'Get the primary key of the table (table name)
'ManDb.upColumn "cexo255","id","12345","id = '12'" 'Modify the value of the field
'ManDb.Execute "insert space into cexo255(id) values ('789')" 'Add record
'ManDb.Execute "Update cexo255 Set id = 'wxf' Where id = '789'" 'Modify record
'ManDb.Execute "delete space From cexo255 Where id = 'wxf'" 'Delete record
ManDb.Execute("Select Count(*) From cexo255"):Response.Write ManDb.Count 'Statistics number of records
'If ManDb.CheckTable("StudInfo") THEn Response.Write "StudInfo table exists!!!" Else Response.Write "StudInfo table does not exist!!!"
'//---------------------------------------------------------------------------------------------------------------------------
Set ManDb = Nothing
%>
The above is an introduction to the steps for online database management. I believe everyone has a certain understanding. If you want to know more technical information, please continue to pay attention to the wrong new technology channel!