資料庫結構操作 (轉)
作者:tonny
轉載請顯示出處:
結構操作。適應於access, SERVER等常見的資料庫。
1。建立連線。
可以透過OC或OLEDB連線。
Set gObjDC = Server.Create("ADODB.Connection")
dim strconn,myDSN
myDSN="test"
strconn="DSN="&myDSN&";uid=sa;pwd="
'strconn ="Provr=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=MeiSha;Data =tonny"
gObjDC.ConnectionString=strconn
gObjDC.Open
2。顯示所有表
set gObjRS = gObjDC.OpenSchema(adSchemaTables)
Do While Not gObjRS.EOF
If gObjRS.Fields("TABLE_TYPE") = "TABLE" AND Left(gObjRS.Fields("TABLE_NAME"), 4) <> "MSys" Then
'不必把表顯示出來
Response.Write "
Response.Write "
myPLink = "?DSN_NAME=" & myDSN & "&Table_Name=" & gObjRS.Fields("TABLE_NAME")
Response.Write "
Response.Write "
Response.Write "
End If
gObjRS.MoveNext
L
gObjRS.Close
3。新建表
Table Name :
Field Count :
definetable.asp中主要原始碼
myFieldCount = Request.Form("Field_Count")
createtable.asp中主要原始碼
myPrimary = ""
QueryString = "CREATE TABLE " & myTable &" ("
myFieldCount = CInt(Request.QueryString("Field_Count"))
For i = 1 to myFieldCount
myFieldName = Request.Form("FieldName_"&i)
mySQLQueryString = mySQLQueryString & Chr(34) & _
myFieldName & Chr(34) & " " &_
Request.Form("FieldType_"&i)
myLength = Request.Form("FieldLength_"&i)
If isNumeric(myLength) Then
mySQLQueryString = mySQLQueryString & " (" & myLength & ") "
End If
mySQLQueryString = mySQLQueryString & " " & Request.Form("FieldNull_"&i)
If Request.Form("FieldUnique_"&i) <> "" Then
mySQLQueryString = mySQLQueryString & " CONSTRAINT pk" & myFieldName & " UNIQUE"
End If
mySQLQueryString = mySQLQueryString & ", "
If Request.Form("FieldPrimary_"&i) <> "" Then
myPrimary = myPrimary & Chr(34) & myFieldName & Chr(34) & ", "
End If
Next
mySQLQueryString = Left(mySQLQueryString, Len(mySQLQueryString)-2)
If myPrimary <> "" Then
myPrimary = Left(myPrimary, Len(myPrimary)-2)
mySQLQueryString = mySQLQueryString & ", " & "CONSTRAINT Contraint PRIMARY KEY(" & myPrimary & ")"
End If
mySQLQueryString = mySQLQueryString & ");"
'Response.Write mySQLQueryString
gObjDC.execute mySQLQueryString
4。顯示錶結構
set gObjRS = Server.CreateObject("ADODB.Recordset")
gObjRS.Open "[" & myTable & "]", gObjDC, adOpenForwardOnly, adLockReadOnly
For i = 0 to gObjRS.Fields.Count - 1
Response.Write "
Response.Write "
myType = GetType(gObjRS.Fields(i).Type)
Response.Write "
myLength = " "
If myType <> "LONGTEXT" AND myType <> "LONGBINARY" Then
myLength = gObjRS.Fields(i).DefinedSize
End If
Response.Write "
Response.Write "
myLink = "dropfield1.asp?DSN_Name=" & myDSN & "&Table_Name=" & myTable & "&Field_Name=" & gObjRS.Fields(i).Name
Response.Write " Drop " & gObjRS.Fields(i).Name & " field"
Response.Write "
Response.Write "
Next
gObjRS.Close
Function GetType(pConstant)
Select Case pConstant
Case adBinary ’128
GetType = "BINARY"
Case adBoolean ‘11
GetType = "BOOLEAN"
Case adUnsignedTinyInt ’17
GetType = "BYTE"
Case adInteger ‘3
GetType = "LONG"
Case adCurrency ’6
GetType = "CURRENCY"
Case adDBTimeStamp ‘135
GetType = "DATETIME"
Case adSingle ’4
GetType = "SINGLE"
Case adDouble ‘5
GetType = "DOUBLE"
Case adSmallInt ’2
GetType = "SHORT"
Case adLongVarChar ‘201
GetType = "LONGTEXT"
Case adLongVarBinary ’205
GetType = "LONGBINARY"
Case adVarChar ‘200
GetType = "TEXT"
Case Else
GetType = "UNKNOW(" & pConstant & ")"
End Select
End Function
5。新增一欄位
mySQLQueryString = "ALTER TABLE " & myTable & " ADD COLUMN " & Request.Form("FieldName") & " "
mySQLQueryString = mySQLQueryString & Request.Form("FieldType") & " "
myLength = Request.Form("FieldLength")
If isNumeric(myLength) Then
mySQLQueryString = mySQLQueryString & "(" & myLength & ") "
End If
mySQLQueryString = mySQLQueryString & Request.Form("FieldNull") & " "
If Request.Form("FieldUnique") <> "" Then
mySQLQueryString = mySQLQueryString & " CONSTRAINT pk" & myFieldName & " UNIQUE"
End If
gObjDC.execute mySQLQueryString
6。刪除一欄位
mySQLQueryString = "ALTER TABLE " & myTable & " DROP COLUMN " & Request.QueryString("Field_Name") & ";"
gObjDC.execute mySQLQueryString
7。刪除一表
mySQLQueryString = "DROP TABLE " & myTable
gObjDC.execute mySQLQueryString
附:'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10752019/viewspace-959063/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Python3結構化資料庫操作包pymysqlPython資料庫MySql
- mysql資料庫-資料結構MySql資料庫資料結構
- 資料結構之連結串列操作資料結構
- database資料庫的資料結構Database資料庫資料結構
- Oracle資料庫日期格式轉換操作Oracle資料庫
- C++資料結構和pb資料結構的轉換C++資料結構
- Golang 將資料庫轉換為gorm結構和RESTful apiGolang資料庫ORMRESTAPI
- 資料結構——單鍵表操作集資料結構
- PostgreSQL 資料庫學習 - 1.資料庫體系結構之儲存結構SQL資料庫
- 資料庫結構的優化資料庫優化
- 資料庫索引背後的資料結構資料庫索引資料結構
- C++資料結構連結串列的基本操作C++資料結構
- 資料庫操作資料庫
- 資料庫操作·資料庫
- linux核心資料結構之kfifo【轉】Linux資料結構
- 玩轉資料結構之陣列資料結構陣列
- JavaScript 資料由駝峰結構轉下劃線結構JavaScript
- Java刷題時常用的標準庫資料結構和相應操作Java資料結構
- mysql資料庫多表同結構合併資料MySql資料庫
- 【SqlServer】 理解資料庫中的資料頁結構SQLServer資料庫
- 資料結構3-4周總結(1) 基本操作資料結構
- Agile PLM資料庫表結構(Oracle)資料庫Oracle
- SQL歷理 neirongxitong資料庫結構SQL資料庫
- Zookeeper資料結構以及實際場景操作資料結構
- 【Falsk 使用資料庫】---- 資料庫基本操作資料庫
- 轉載] magento 產品資料表結構
- 資料結構–進位制(任意)轉換資料結構
- 資料結構 中綴表示式轉化資料結構
- 【PG結構】Postgresql資料庫資料目錄說明SQL資料庫
- MySQL 資料庫操作MySql資料庫
- mongodb資料庫操作MongoDB資料庫
- 資料庫基本操作資料庫
- MongoDB 資料庫操作MongoDB資料庫
- laravel 資料庫操作Laravel資料庫
- Postgresql資料庫體系結構-程式和記憶體結構SQL資料庫記憶體
- 結構化資料、半結構化資料和非結構化資料
- Oracle - 資料庫的記憶體結構Oracle資料庫記憶體
- 1.1資料庫物件結構遷移方法資料庫物件
- 資料庫內部儲存結構探索資料庫