这篇文章主要讲解了“怎么创建动态MSSQL数据库表”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么创建动态MSSQL数据库表”吧！
复制代码 代码如下:
ImportsSystem.Data ImportsSystem.Data.SqlClient PublicClassForm1 InheritsSystem.Windows.Forms.Form PrivateConnectionStringAsString="DataSource=.;InitialCatalog=;UserId=sa;Password=;" PrivatereaderAsSqlDataReader=Nothing PrivateconnAsSqlConnection=Nothing PrivatecmdAsSqlCommand=Nothing PrivateAlterTableBtnAsSystem.Windows.Forms.Button PrivatesqlAsString=Nothing PrivateCreateOthersBtnAsSystem.Windows.Forms.Button #Region"Windows窗体设计器生成的代码" '窗体重写处置以清理组件列表。 ProtectedOverloadsOverridesSubDispose(ByValdisposingAsBoolean) IfdisposingThen IfNot(componentsIsNothing)Then components.Dispose() EndIf EndIf MyBase.Dispose(disposing) EndSub PublicSubNew() MyBase.New() InitializeComponent() EndSub PrivatecomponentsAsSystem.ComponentModel.IContainer FriendWithEventsDataGrid1AsSystem.Windows.Forms.DataGrid FriendWithEventsCreateDBBtnAsSystem.Windows.Forms.Button FriendWithEventsCreateTableBtnAsSystem.Windows.Forms.Button FriendWithEventsCreateSPBtnAsSystem.Windows.Forms.Button FriendWithEventsCreateViewBtnAsSystem.Windows.Forms.Button FriendWithEventsbtnAlterTableAsSystem.Windows.Forms.Button FriendWithEventsbtnCreateOthersAsSystem.Windows.Forms.Button FriendWithEventsbtnDropTableAsSystem.Windows.Forms.Button FriendWithEventsbtnViewDataAsSystem.Windows.Forms.Button FriendWithEventsbtnViewSPAsSystem.Windows.Forms.Button FriendWithEventsbtnViewViewAsSystem.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 Me.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 ' Me.btnViewData.Location=NewSystem.Drawing.Point(351,43) Me.btnViewData.Name="btnViewData" Me.btnViewData.TabIndex=7 Me.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="查看存储过程" ' 'btnViewView ' Me.btnViewView.Location=NewSystem.Drawing.Point(443,43) Me.btnViewView.Name="btnViewView" Me.btnViewView.TabIndex=9 Me.btnViewView.Text="查看视图" ' '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.Size(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="动态创建SQLServer数据库、表、存储过程等架构信息" 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%)logon"+"(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 '创建表 PrivateSubCreateTableBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesCreateTableBtn.Click conn=NewSqlConnection(ConnectionString) '打开连接 Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;" conn.ConnectionString=ConnectionString conn.Open() sql="CREATETABLEmyTable"+"(myIdINTEGERCONSTRAINTPKeyMyIdPRIMARYKEY,"+_ "myNameCHAR(50)NOTNull,myAddressCHAR(255),myValuesFLOAT)" cmd=NewSqlCommand(sql,conn) Try 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,'【孟宪会之精彩世界】之二','http://www.erp800.com/net_lover/',99)"
#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"+"SELECTmyName,myAddressFROMmyTableGO" ExecuteSQLStmt(sql) EndSub '创建视图 PrivateSubCreateViewBtn_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ HandlesCreateViewBtn.Click sql="CREATEVIEWmyViewASSELECTmyNameFROMmyTable" ExecuteSQLStmt(sql) EndSub '修改表 PrivateSubbtnAlterTable_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)_ 54com.cn HandlesbtnAlterTable.Click sql="ALTERTABLEMyTableADDnewColdatetimeNOTNULLDEFAULT(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.Click conn=NewSqlConnection(ConnectionString) Ifconn.State=ConnectionState.OpenThen conn.Close() EndIf ConnectionString="DataSource=.;InitialCatalog=MyDataBase;UserId=sa;Password=;" conn.ConnectionString=ConnectionString conn.Open() DimdaAsNewSqlDataAdapter("myProc",conn) DimdsAsNewDataSet("SP") da.Fill(ds,"SP") DataGrid1.DataSource=ds.DefaultViewManager EndSub '浏览视图 PrivateSubbtnViewView_Click(ByValsenderAsSystem.Object,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感谢各位的阅读，以上就是“怎么创建动态MSSQL数据库表”的内容了，经过本文的学习后，相信大家对怎么创建动态MSSQL数据库表这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
