Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to create a dynamic MSSQL database table". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to create a dynamic MSSQL database table".

The copy code is as follows:

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 "code generated by the Windows forms designer" 'form rewrite disposal to clean up the component list. 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 ()

The copy code is as follows:

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 (19Magazine 9) Me.CreateDBBtn.Name= "CreateDBBtn" Me.CreateDBBtn.Size=NewSystem.Drawing.Size (104 Magazine 23) Me.CreateDBBtn.TabIndex=0 Me.CreateDBBtn.Text= "create Database" 'CreateTableBtn' Me.CreateTableBtn.Location=NewSystem.Drawing.Point (139Lei9) Me.CreateTableBtn.Name= "CreateTableBtn" Me.CreateTableBtn.TabIndex=1 Me.CreateTableBtn.Text= create table "'CreateSPBtn' Me.CreateSPBtn.Location=NewSystem.Drawing.Point (230Magazine 9) Me.CreateSPBtn.Name=" CreateSPBtn "Me.CreateSPBtn.Size=NewSystem.Drawing.Size (104 Magazine 23) Me.CreateSPBtn.TabIndex=2 Me.CreateSPBtn.Text=" create stored procedure "'CreateViewBtn' Me.CreateViewBtn.Location=NewSystem.Drawing.Point (350 Me.btnAlterTable.TabIndex=4 Me.btnAlterTable.Text= 9) Me.CreateViewBtn.Name= "CreateViewBtn" Me.CreateViewBtn.TabIndex=3 China Network Management Alliance www.bitscn.com Me.CreateViewBtn.Text= "create View" 'btnAlterTable' Me.btnAlterTable.Location=NewSystem.Drawing.Point (441 Magazine 9) Me.btnAlterTable.Name= "btnAlterTable" Me.btnAlterTable.TabIndex=4 Me.btnAlterTable.Text= "modify Table" # p#

The copy code is as follows:

'btnCreateOthers' Me.btnCreateOthers.Location=NewSystem.Drawing.Point (175.43) Me.btnCreateOthers.Name= "btnCreateOthers" Me.btnCreateOthers.Size=NewSystem.Drawing.Size (104J 23) Me.btnCreateOthers.TabIndex=5 Me.btnCreateOthers.Text= "create rules and indexes" feedom.net''btnDropTable' Me.btnDropTable.Location=NewSystem.Drawing.Point (138Pol 43) Me.btnDropTable.Name= "btnDropTable" Me.btnDropTable. TabIndex=6 Me.btnDropTable.Text= "delete table" 'btnViewData' Me.btnViewData.Location=NewSystem.Drawing.Point (351 btnViewData 43) Me.btnViewData.Name= "btnViewData" Me.btnViewData.TabIndex=7 Me.btnViewData.Text= "view data" 'btnViewSP' Me.btnViewSP.Location=NewSystem.Drawing.Point (230 Me 43) feedom.net Me.btnViewSP.Name= "btnViewSP" Me. BtnViewSP.Size=NewSystem.Drawing.Size (104 Me.DataGrid1.DataMember= 23) Me.btnViewSP.TabIndex=8 Me.btnViewSP.Text= "View stored procedures" 'btnViewView' Me.btnViewView.Location=NewSystem.Drawing.Point (443) Me.btnViewView.Name= "btnViewView" Me.btnViewView.TabIndex=9 Me.btnViewView.Text= "View View" 'DataGrid1' Me.DataGrid1.DataMember= "" Me.DataGrid1 .HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location=NewSystem.Drawing.Point (20Magne76) 54com.cn Me.DataGrid1.Name= "DataGrid1" Me.DataGrid1.Size=NewSystem.Drawing.Size (500183) Me.DataGrid1.TabIndex=10''Form1' Me.AutoScaleBaseSize=NewSystem.Drawing.Size (513) Me.ClientSize=NewSystem.Drawing.Size (538281) Me.Controls.AddRange (NewSystem.Windows.Forms.Control () {Me.DataGrid1) Me.btnViewView,_ Me.btnViewSP,Me.btnViewData,Me.btnDropTable,Me.btnCreateOthers,Me.btnAlterTable,_

The copy code is as follows:

Me.CreateViewBtn,Me.CreateSPBtn,Me.CreateTableBtn,Me.CreateDBBtn}) Me.Name= "Form1" Me.Text= dynamically create SQLServer databases, tables, stored procedures and other schema information "CType (Me.DataGrid1,System.ComponentModel.ISupportInitialize) .EndInit () Me.ResumeLayout (False) EndSub # EndRegion 'create database PrivateSubCreateDBBtn_Click (ByValsenderAsSystem.Object ByValeAsSystem.EventArgs) _ HandlesCreateDBBtn.Click conn=NewSqlConnection (ConnectionString) 'Open connection Ifconn.StateConnectionState.OpenThen conn.Open () EndIf' MyDataBase is the database name 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 'create table PrivateSubCreateTableBtn_Click (ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs) _ HandlesCreateTableBtn.Click conn=NewSqlConnection (ConnectionString)' Open connection 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, myValuesFLOAT) "cmd=NewSqlCommand (sql,conn) Try cmd.ExecuteNonQuery () 'add record 54com.cn sql=" INSERTINTOmyTable (myId,myName,myAddress,myValues) "+ _" VALUES Http://xml.sz.luohuedu.net/',100)" cmd=NewSqlCommand (sql,conn) cmd.ExecuteNonQuery () sql= "INSERTINTOmyTable (myId,myName,myAddress,myValues)" + _ "VALUES (1002) [Meng Xianhui's wonderful World] II, 'http://www.erp800.com/net_lover/',99)"#p#

The copy code is as follows:

Cmd=NewSqlCommand (sql,conn) cmd.ExecuteNonQuery () sql= "INSERTINTOmyTable (myId,myName,myAddress,myValues)" + _ "VALUES (1003) [Meng Xianhui's wonderful World], 'http://xml.sz.luohuedu.net/',99)" cmd=NewSqlCommand (sql,conn) cmd.ExecuteNonQuery () sql=" INSERTINTOmyTable (myId,myName,myAddress,myValues) "+ _" VALUES (1004) [Meng Xianhui's wonderful World] IV' 'http://www.erp800.com/net_lover/',100)" cmd=NewSqlCommand (sql,conn) cmd.ExecuteNonQuery () CatchaeAsSqlException MessageBox.Show (ae.Message.ToString ()) EndTry EndSub 'creates the stored procedure PrivateSubCreateSPBtn_Click (ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs) _ HandlesCreateSPBtn.Click sql= "CREATEPROCEDUREmyProcAS" + "SELECTmyName MyAddressFROMmyTableGO "ExecuteSQLStmt (sql) EndSub 'create view PrivateSubCreateViewBtn_Click (ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs) _ HandlesCreateViewBtn.Click sql=" CREATEVIEWmyViewASSELECTmyNameFROMmyTable "ExecuteSQLStmt (sql) EndSub' modify table PrivateSubbtnAlterTable_Click (ByValsenderAsSystem.Object) ByValeAsSystem.EventArgs) _ 54com.cn HandlesbtnAlterTable.Click sql= "ALTERTABLEMyTableADDnewColdatetimeNOTNULLDEFAULT (getdate ())" ExecuteSQLStmt (sql) EndSub 'create rules and indexes PrivateSubbtnCreateOthers_Click (ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs) _ HandlesbtnCreateOthers.Click sql= "CREATEUNIQUEINDEX" + "myIdxONmyTable (myName)" ExecuteSQLStmt (sql) sql= "CREATERULEmyRule" + "AS@myValues > = 90AND@myValues

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.