如何创建动态MSSQL数据库表

复制代码代码如下：

ImportsSystem.Data ImportsSystem.Data.SqlClientPublicClassForm1 InheritsSystem 。视窗。 Forms.Form ​PrivateConnectionStringAsString="DataSource=.;InitialCatalog=;UserId=sa;Password=;" PrivatereaderAsSqlDataReader=Nothing ​PrivateconnAsSqlConnection=Nothing ​PrivatecmdAsSqlCommand=Nothing ​PrivateAlterTableBtnAsSystem.Windows.Forms.Button vatesqlAsString=Nothing ​PrivateCreateOthersBtnAsSystem.Windows.Forms.Button ​#Region“Windows 窗体设计器生成的代码”'表单重写句柄清理组件列表。 ProtectedOverloadsOverridesSubDispose(ByValdisusingAsBoolean) IfdisusingThzh_cn IfNot(componentsIsNothing)Then Components.Dispose() EndIf EndIf MyBase.Dispose(disducing) EndSub PublicSubNew() MyBase.New() InitializeComponent() EndSub PrivatecomponentsAsSystem.ComponentModel.IContainer FriendWithEventsDataGrid1AsSystem.Windows.Forms.DataGrid FriendWithEventsCreateDBBtnAsSystem.Windows.Forms。按钮 FriendWithEventsCreateTableBtnAsSystem.Windows.Forms.Button FriendWithEvent sCreateSPBtnAsSystem.Windows.Forms.Button ＜FriendWithEventsCreateViewBtnAsSystem.Windows.Forms.Button ＜FriendWithEventsbtnAlterTableAsSystem.Windows .Forms.Button FriendWithEventsbtnCreateOthersAsSystem.Windows.Forms.Button ＜FriendWithEventsbtnDropTable AsSystem.Windows.Forms.Button ​FriendWithEventsbtnViewDataAsSystem.Windows.Forms。按钮 ​FriendWithEventsbtnViewSPAsSystem.Windows.Forms.Button ​FriendWithEventsbtnViewView AsSystem.Windows.Forms.Button ​PrivateSubInitializeComponent()

复制代码 代码如下：

Me.CreateDBBtn=NewSystem.Windows.Forms.Button() Me.CreateTableBtn=NewSystem.Windows.Forms.Button() Me.CreateSPBtn=NewSystem.Windows.Forms.Button() Me.CreateViewBtn=NewSystem.Windows.Forms.Button() Me.btnAlterTable=NewSystem.Windows.Forms.Button() Me.btnCreateOthers=NewSystem.Windows.Forms.Button() Me.btnDropTable=NewSystem.Windows.Forms.Button() Me.btnViewData=NewSystem.Windows.Forms.Button () Me.btnViewSP=NewSystem.Windows.Forms.Button () ＜Me.btnViewView=NewSystem.Windows.Forms.Button() ＜Me.DataGrid1=NewSystem.Windows.Forms.DataGrid() ＜CType(Me.DataGrid1,System.ComponentModel) .ISupportInitialize).BeginInit() ​Me.SuspendLayout() ​'CreateDBBtn ​​​Me.CreateDBBtn.Location=NewSystem.Drawing.Point(19,9) ​Me.CreateDBBtn.Name=“CreateDBBtn”​​Me.CreateDBBtn.Size=NewSystem.Drawing.Size (104,23) ​Me.CreateDBBtn.TabIndex= 0 ​Me.CreateDBBtn.Text=“创建数据库” ​​'CreateTableBtn ​​' ​Me.CreateTableBtn.Location=NewSystem.Drawing.Point(139,9) ​Me.CreateTableBtn.Name=“CreateTableBtn” Me.CreateTableBtn.TabIndex=1 Me.CreateTableBtn.Text="创建表" 'CreateSPBtn Me. CreateSPBtn.Location=NewSystem.Drawing.Point(230,9) Me.CreateSPBtn.Name="CreateSPBtn" Me.CreateSPBtn.Size=NewSystem.Drawing.Size(104,23) Me.CreateSPBtn.TabIndex=2 Me.CreateSPBtn. Text="创建存储过程" ' 'CreateViewBtn ' ' Me.CreateViewBtn.Location=NewSystem.Drawing.Point(350,9) Me.CreateViewBtn.Name="CreateViewBtn" Me.CreateViewBtn.TabIndex=3中国网络管理联盟 www.bitscn 。 com 　　Me.CreateViewBtn.Text =“创建视图” 　　'btnAlterTable 　　Me.btnAlterTable.Location=NewSystem.Drawing.Point(441,9) 　　Me.btnAlterTable.Name =“btnAlterTable” 　　Me.btnAlterTable.TabIndex=4 　　Me.btnAlterTable.Text ="修改表格"#p#

复制代码代码如下：

'btnCreateOthers ' Me.btnCreateOthers.Location=NewSystem.Drawing.Point(17,43) Me.btnCreateOthers.Name="btnCreateOthers" Me.btnCreateOthers.Size=NewSystem.Drawing.Size(104,23) Me.btnCreateOthers.TabIndex=5 .btnCreateOthers.Text="创建规则和索引" feedom.net ' 'btnDropTable ' Me.btnDropTable.Location=NewSystem.Drawing.Point(138,43) Me.btnDropTable.Name="btnDropTable" Me.btnDropTable.TabIndex =6 Me.btnDropTable.Text="删除表格" 'btnViewData Text="查看数据" ★'btnViewSP ★Me.btnViewSP.Location=NewSystem.Drawing.Point(230,43) feedom.net Me.btnViewSP.Name="btnViewSP" Me.btnViewSP.Size=NewSystem.Drawing.Size(104,23) Me.btnViewSP.TabIndex=8 Me.btnViewSP.Text "查看存储过程" View" ' ' DataGrid1 ' ' Me.DataGrid1.DataMember="" Me .DataGrid1.HeaderForeColor=System.Drawing.SystemColors.ControlText Me.DataGrid1.Location=NewSystem.Drawing.Point(20,76)54com.cn Me.DataGrid1.Name=“DataGrid1” Me.DataGrid1.Size=NewSystem.Drawing.大小(500,183) Me.DataGrid1.TabIndex=10 ' 'Form1 ' Me.AutoScaleBaseSize=NewSystem.Drawing.Size(5,13) Me.ClientSize=NewSystem.Drawing.Size(538,281)Me.Controls.AddRange(NewSystem.Windows .Forms.Control(){Me.DataGrid1,Me.btnViewView,_ 　Me.btnViewSP,Me.btnViewData,Me.btnDropTable,Me.btnCreateOthers,Me.btnAlterTable,_

复制代码 代码如下：

Me.CreateViewBtn,Me.CreateSPBtn,Me.CreateTableBtn,Me.CreateDBBtn}) Me.Name="Form1" Me.Text="动态创建SQL Server数据库、表、存储过程等架构信息" CType( Me.DataGrid1,System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) EndSub #EndRegion '创建数据库 PrivateSubCreateDBBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesCreateDBBtn.Click conn=NewSqlConnection(ConnectionString) '打开连接 Ifconn .State<>ConnectionState.OpenThen conn.Open() EndIf 'MyDataBase 为数据库名 DimsqlAsString="CREATEDATABASEMyDataBaseONPRIMARY( Name=MyDataBase_data,filename="+_ ＜54com.cn ＜"'D:\MyDataBase.mdf',size=3, "+"maxsize=5,filegrowth=10%)登录"+"(name=MyDataBase_log,"+_ Ф"filename='D:\MyDataBase.ldf', size=3,"+"maxsize=20,filegrowth=1 )" cmd=NewSqlCommand(sql,conn) Try cmd.ExecuteNonQuery() CatchaeAsSqlException MessageBox.Show(ae.Message.ToString()) EndTry EndSub '创建表 PrivateSubCreateTableB tn_Click( ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesCreateTableBtn.Click conn=NewSqlConnection(ConnectionString ) '打开连接 Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa; 密码 =;" conn.ConnectionString=ConnectionString conn.Open() sql="CREATETABLEmyTable"+"(myIdINTEGERCONSTRAINTPKeyMyIdPRIMARYKEY,"+_ "myNameCHAR(50)NOTNull,myAddressCHAR(255),myValuesFLOAT)" cmd=NewSqlCommand(sql,conn) 尝试 cmd.ExecuteNonQuery () '添加记录 54com.cn sql="INSERTINTOmyTable(myId,myName,myAddress,myValues)"+_ "VALUES(1001,_'【孟宪惠精彩世界之一','http://xml.sz. luohuedu.net/',100)" cmd=NewSqlCommand(sql,conn) cmd.ExecuteNonQuery() sql="INSERTINTOmyTable(myId,myName,myAddress, myValues)"+_ "VALUES(1002,'[萌的奇妙世界]席安徽]第2部分','http://www.erp800.com/net_lover/',99)"#p#

复制代码代码如下：< /p>

cmd=NewSqlCommand(sql,conn) cmd.ExecuteNonQuery() sql="INSERTINTOmyTable(myId ,myName,myAddress,myValues)"+_ "VALUES(1003) ,'[孟贤慧的奇妙世界]第三部分','http://xml.sz.luohuedu.net/',99)" cmd=NewSqlCommand( sql,conn) cmd.ExecuteNonQuery() sql="INSERTINTOMyTable(myId, myName,myAddress,myValues)"+_ "VALUES(1004,'【孟贤慧的奇妙世界】第四部分','http://www.erp800.com/net_lover/',100)" cmd=NewSqlCommand(sql ,conn) cmd.ExecuteNonQuery() CatchaeAsSqlException MessageBox .Show(ae.Message.ToString()) ​EndTry ​EndSub ​创建存储过程 ​PrivateSubCreateSPBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ ​HandlesCreateSPBtn.Click ​​sql="CREATEPROCEDUREmyProcaS"+"SELECTmy姓名，我的地址来自myTableGO" ExecuteSQLStmt(sql) EndSub '创建视图 PrivateSubCreateViewBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesCreateViewBtn.Click sql="CREATEVIEWmyViewASSELECTmyNameFROMmyTable" ExecuteSQLStmt(sql) EndSub '修改表 PrivateSubbtnAlterTable_Click(ByVals enderAsSystem.Object,ByValeAsSystem.EventArgs)_ 54com.cn HandlesbtnAlterTable.Click sql="ALTERTABLEMyTableADDnewColdatetimeNOTNULL DEFAULT(getdate())" ExecuteSQLStmt (sql) EndSub '创建规则和索引 PrivateSubbtnCreateOthers_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesbtnCreateOthers.Click sql="CREATEUNIQUEINDEX"+"myIdxONmyTable(myName )" ExecuteSQLStmt(sql) sql="CREATERULEmyRule" +"AS@myValues> =90AND@myValues<9999" ExecuteSQLStmt(sql) EndSub '删除表 PrivateSubbtnDropTable_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesbtnDropTable.Click DimsqlAsString="DROPTABLEMyTable " ExecuteSQLStmt(sql) EndSub 复制代码 代码如下： '浏览表数据 PrivateSubbtnViewData_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesbtnViewData.Click conn=NewSqlConnection(ConnectionString) Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;" conn.ConnectionString=ConnectionString conn.Open() DimdaAsNewSqlDataAdapter("SELECT*FROMmyTable",conn) DimdsAsNewDataSet("myTable") da.Fill(ds,"myTable") DataGrid1. DataSource=ds.Tables("myTable").DefaultView EndSub浏览存储过程PrivateSubbtnViewSP_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_HandlesbtnViewSP.Clickconn=NewSqlConnection(ConnectionString)Ifconn.State=ConnectionState.OpenThenconn .Close() EndIf ConnectionString ="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;密码=;" conn.connectring = connectionString conn.open（）dimdaasnewsqldataaDapter（“ myproc”，conn）dimdsasNewDataSet（“ sp”）da.fill（ds，ds，sp，“ sp”，“ sp”）datagrid1.datasource ByValeAsSystem.EventArgs)_HandlesbtnViewView.Click conn=NewSqlConnection(ConnectionString) Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId= sa;Password= ;" conn.ConnectionString=ConnectionString conn.Open() DimdaAsNewSqlDataAdapter("SELECT*FROMmyView",conn) DimdsAsNewDataSet() da.Fill(ds) DataGrid1.DataSource=ds.DefaultViewManager EndSub PrivateSubExecuteSQLStmt( ByValsqlAsString) conn=NewSqlConnection(ConnectionString) 打开连接Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;" conn.ConnectionString=ConnectionString conn.Open() cmd=NewSqlCommand(sql, conn) ​Try ​cmd.ExecuteNonQuery()  CatchaeAsSqlException MessageBox.Show(ae.Message.ToString()) EndTry EndSub EndClass

数据库