作者：tonny转载请显示出处：http://www.weiw.com
数据库结构操作。适应于access，sql server等常见的数据库。
1。建立连接。可以通过ODBC或OLEDB连接。Set gObjDC = Server.CreateObject("ADODB.Connection")dim strconn,myDSNmyDSN="test"strconn="DSN="&myDSN&";uid=sa;pwd="'strconn ="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=MeiSha;Data Source=tonny"gObjDC.ConnectionString=strconngObjDC.Open
2。显示所有表set gObjRS = gObjDC.OpenSchema(adSchemaTables)Do While Not gObjRS.EOFIf gObjRS.Fields("TABLE_TYPE") = "TABLE" AND Left(gObjRS.Fields("TABLE_NAME"), 4) <> "MSys" Then'不必把系统表显示出来Response.Write "<TR>"Response.Write "<TD>" & gObjRS.Fields("TABLE_NAME") & "</TD>"myPLink = "?DSN_NAME=" & myDSN & "&Table_Name=" & gObjRS.Fields("TABLE_NAME") Response.Write "<TD> <A HREF=tablestruct.asp" & myPLink & ">Structure</A> </TD>"Response.Write "<TD> <A HREF=tablecontent.asp" & myPLink & ">Content</A> </TD>"Response.Write "</TR>" & vbCrLfEnd IfgObjRS.MoveNextLoopgObjRS.Close
3。新建表<FORM METHOD=POST ACTION="definetable.asp?DSN_Name=<% =myDSN %>">Table Name :<BR><INPUT TYPE="text" NAME="Table_Name"><BR>Field Count :<BR><INPUT TYPE="text" NAME="Field_Count"><BR> <BR><INPUT TYPE="submit" VALUE="Create"></FORM>
definetable.asp中主要源码myFieldCount = Request.Form("Field_Count")<FORM METHOD=POST ACTION="createtable.asp?DSN_Name=<% =Request.Form("DSN_Name") %>&Table_Name=<% =Request.Form("Table_Name") %>&Field_Count=<%=myFieldCount %>" ID="Form1"> <TABLE ID="Table2"><TR><TD>Name</TD><TD>Type</TD><TD>Length</TD><TD>Null</TD><TD>Primary Key</TD><TD>Unique Index</TD></TR><% For i=1 to myFieldCount%><TR><TD><INPUT TYPE="text" NAME=<% ="FieldName_" & i %> ></TD><TD><SELECT SIZE=1 NAME=<% ="FieldType_" & i %> ><!-请注意：此处根据不同数据库填写字段类型-><OPTION>BINARY<OPTION>BIT<OPTION>BYTE<OPTION>COUNTER<OPTION>CURRENCY<OPTION>DATETIME<OPTION>SINGLE<OPTION>DOUBLE<OPTION>SHORT<OPTION>LONG<OPTION>LONGTEXT<OPTION>LONGBINARY<OPTION>TEXT</SELECT></TD><TD><INPUT TYPE="text" SIZE=5 NAME=<% ="FieldLength_" & i %> ID="Text2"></TD><TD><SELECT SIZE=1 NAME=<% ="FieldNull_" & i %> ID="Select2"><OPTION>not null<OPTION>null</SELECT></TD><TD><INPUT TYPE="checkbox" NAME=<% ="FieldPrimary_" & i %> ID="Checkbox1"></TD><TD><INPUT TYPE="checkbox" NAME=<% ="FieldUnique_" & i %> ID="Checkbox2"></TD></TR><%Next%></TABLE><INPUT TYPE="reset" VALUE="Clear" ID="Reset1" NAME="Reset1"> <INPUT TYPE="submit" VALUE="Create" ID="Submit1" NAME="Submit1"></FORM>
createtable.asp中主要源码myPrimary = ""mySQLQueryString = "CREATE TABLE " & myTable &" ("myFieldCount = CInt(Request.QueryString("Field_Count"))For i = 1 to myFieldCountmyFieldName = Request.Form("FieldName_"&i)mySQLQueryString = mySQLQueryString & Chr(34) & _myFieldName & Chr(34) & " " &_Request.Form("FieldType_"&i)myLength = Request.Form("FieldLength_"&i)If isNumeric(myLength) ThenmySQLQueryString = mySQLQueryString & " (" & myLength & ") "End IfmySQLQueryString = mySQLQueryString & " " & Request.Form("FieldNull_"&i)If Request.Form("FieldUnique_"&i) <> "" ThenmySQLQueryString = mySQLQueryString & " CONSTRAINT pk" & myFieldName & " UNIQUE"End IfmySQLQueryString = mySQLQueryString & ", "If Request.Form("FieldPrimary_"&i) <> "" ThenmyPrimary = myPrimary & Chr(34) & myFieldName & Chr(34) & ", "End IfNextmySQLQueryString = Left(mySQLQueryString, Len(mySQLQueryString)-2)If myPrimary <> "" ThenmyPrimary = Left(myPrimary, Len(myPrimary)-2)mySQLQueryString = mySQLQueryString & ", " & "CONSTRAINT Contraint PRIMARY KEY(" & myPrimary & ")"End IfmySQLQueryString = mySQLQueryString & ");"'Response.Write mySQLQueryStringgObjDC.execute mySQLQueryString
4。显示表结构set gObjRS = Server.CreateObject("ADODB.Recordset")gObjRS.Open "[" & myTable & "]", gObjDC, adOpenForwardOnly, adLockReadOnlyFor i = 0 to gObjRS.Fields.Count - 1Response.Write "<TR>" & vbCrlfResponse.Write "<TD>" & gObjRS.Fields(i).Name & "</TD>" & vbCrlfmyType = GetType(gObjRS.Fields(i).Type)Response.Write "<TD>" & myType & "</TD>"& vbCrlfmyLength = " "If myType <> "LONGTEXT" AND myType <> "LONGBINARY" ThenmyLength = gObjRS.Fields(i).DefinedSize End IfResponse.Write "<TD>" & myLength & "</TD>"& vbCrlfResponse.Write "<TD>"& vbCrlfmyLink = "dropfield1.asp?DSN_Name=" & myDSN & "&Table_Name=" & myTable & "&Field_Name=" & gObjRS.Fields(i).NameResponse.Write "<A HREF='" & myLink & "'><B> Drop " & gObjRS.Fields(i).Name & " field</B></A>"Response.Write "</TD>"& vbCrlfResponse.Write "</TR>"& vbCrlfNextgObjRS.Close
Function GetType(pConstant)Select Case pConstantCase adBinary ’128GetType = "BINARY"Case adBoolean ‘11GetType = "BOOLEAN"Case adUnsignedTinyInt ’17GetType = "BYTE"Case adInteger ‘3GetType = "LONG"Case adCurrency ’6GetType = "CURRENCY"Case adDBTimeStamp ‘135GetType = "DATETIME"Case adSingle ’4GetType = "SINGLE"Case adDouble ‘5GetType = "DOUBLE"Case adSmallInt ’2GetType = "SHORT"Case adLongVarChar ‘201GetType = "LONGTEXT"Case adLongVarBinary ’205GetType = "LONGBINARY"Case adVarChar ‘200GetType = "TEXT"Case ElseGetType = "UNKNOW(" & pConstant & ")"End SelectEnd Function
5。添加一字段mySQLQueryString = "ALTER TABLE " & myTable & " ADD COLUMN " & Request.Form("FieldName") & " "mySQLQueryString = mySQLQueryString & Request.Form("FieldType") & " "myLength = Request.Form("FieldLength")If isNumeric(myLength) ThenmySQLQueryString = mySQLQueryString & "(" & myLength & ") "End IfmySQLQueryString = mySQLQueryString & Request.Form("FieldNull") & " "If Request.Form("FieldUnique") <> "" ThenmySQLQueryString = mySQLQueryString & " CONSTRAINT pk" & myFieldName & " UNIQUE"End IfgObjDC.execute mySQLQueryString
6。删除一字段mySQLQueryString = "ALTER TABLE " & myTable & " DROP COLUMN " & Request.QueryString("Field_Name") & ";"gObjDC.execute mySQLQueryString
7。删除一表mySQLQueryString = "DROP TABLE " & myTablegObjDC.execute mySQLQueryString
附：'---- DataTypeEnum Values ----Const adEmpty = 0Const adTinyInt = 16Const adSmallInt = 2Const adInteger = 3Const adBigInt = 20Const adUnsignedTinyInt = 17Const adUnsignedSmallInt = 18Const adUnsignedInt = 19Const adUnsignedBigInt = 21Const adSingle = 4Const adDouble = 5Const adCurrency = 6Const adDecimal = 14Const adNumeric = 131Const adBoolean = 11Const adError = 10Const adUserDefined = 132Const adVariant = 12Const adIDispatch = 9Const adIUnknown = 13Const adGUID = 72Const adDate = 7Const adDBDate = 133Const adDBTime = 134Const adDBTimeStamp = 135Const adBSTR = 8Const adChar = 129Const adVarChar = 200Const adLongVarChar = 201Const adWChar = 130Const adVarWChar = 202Const adLongVarWChar = 203Const adBinary = 128Const adVarBinary = 204Const adLongVarBinary = 205Const adChapter = 136Const adFileTime = 64Const adDBFileTime = 137Const adPropVariant = 138Const adVarNumeric = 139
