Create a dynamic MSSQL database table

The following is a reference clip: CopyCode The 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 Windows Form Designer"
'Form rewriting disposal to clear 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
Friendwitheventsbtnviewassystem. Windows. Forms. Button
Privatesubinitializecomponent ()

Copy code The 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 (19,9)
Me. createdbbtn. Name = "createdbbtn"
Me. createdbbtn. size = Newsystem. Drawing. Size (104,23)
Me. createdbbtn. tabindex = 0
Me. createdbbtn. Text = "create database"
'
'Createtablebtn
'
Me. createtablebtn. Location = Newsystem. Drawing. Point (139,9)
Me. createtablebtn. Name = "createtablebtn"
Me. createtablebtn. tabindex = 1
Me. createtablebtn. Text = "create table"
'
'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 = "Create a stored procedure"
'
'Createviewbtn
'
Me. createviewbtn. Location = Newsystem. Drawing. Point (350,9)
Me. createviewbtn. Name = "createviewbtn"
Me. createviewbtn. tabindex = 3 www.bitscn.com
Me. createviewbtn. Text = "create View"
'
'Btnaltertable
'
Me. btnaltertable. Location = Newsystem. Drawing. Point (441,9)
Me. btnaltertable. Name = "btnaltertable"
Me. btnaltertable. tabindex = 4
Me. btnaltertable. Text = "Modify Table"

# P #Copy codeThe Code is as follows:

'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 = "create rules and indexes"
Feedom.net
'
'Btndroptable
'
Me. btndroptable. Location = Newsystem. Drawing. Point (138,43)
Me. btndroptable. Name = "btndroptable"
Me. btndroptable. tabindex = 6
Me. btndroptable. Text = "Delete table"
'
'Btnviewdata
'
Me. btnviewdata. Location = Newsystem. Drawing. Point (351,43)
Me. btnviewdata. Name = "btnviewdata"
Me. btnviewdata. tabindex = 7
Me. btnviewdata. Text = "View data"
'
'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 stored procedure"
'
'Btnviewview
'
Me. btnviewview. Location = Newsystem. Drawing. Point (443,43)
Me. btnviewview. Name = "btnviewview"
Me. btnviewview. tabindex = 9
Me. btnviewview. Text = "view"
'
'Create1'
'
Me. datagrid1.datamember = ""
Me. datagrid1.headerforecolor = system. Drawing. systemcolors. controltext
Me. datagrid1.location = Newsystem. Drawing. Point (20, 76) 54com.cn
Me. Maid = "maid"
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 ,_

Copy codeThe Code is as follows:

Me. createviewbtn, me. createspbtn, me. createtablebtn, me. createdbbtn })
Me. Name = "form1"
Me. Text = "dynamically create SQL Server databases, tables, stored procedures, and other Architecture Information"
Ctype (Me. datagrid1, system. componentmodel. isupportinitialize). endinit ()
Me. resumelayout (false)
Endsub
# Endregion
'Create a database
Privatesubcreatedbbtn_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlescreatedbbtn. Click
Conn = newsqlconnection (connectionstring)
'Open the connection
Ifconn. State <> connectionstate. 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 a table
Privatesubcreatetablebtn_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlescreatetablebtn. Click
Conn = newsqlconnection (connectionstring)
'Open the 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 (255), myvaluesfloat )"
Cmd = newsqlcommand (SQL, Conn)
Try
Cmd. executenonquery ()
'Add record
54com.cn
SQL = "insertintomytable (myid, myname, myaddress, myvalues)" + _
"Values (1001, _ ', one of the wonderful worlds of the mengxian meeting', 'HTTP: // xml.sz.luohuedu.net )"
Cmd = newsqlcommand (SQL, Conn)
Cmd. executenonquery ()
SQL = "insertintomytable (myid, myname, myaddress, myvalues)" + _
"Values (1002, '', 'HTTP: // www.erp800.com/net_lover/', 99 )"

# P #Copy codeThe Code is as follows:

Cmd = newsqlcommand (SQL, Conn)
Cmd. executenonquery ()
SQL = "insertintomytable (myid, myname, myaddress, myvalues)" + _
"Values (1003, 'the wonderful world of the Meng xianhui ', 'HTTP: // xml.sz.luohuedu.net/', 99 )"
Cmd = newsqlcommand (SQL, Conn)
Cmd. executenonquery ()
SQL = "insertintomytable (myid, myname, myaddress, myvalues)" + _
"Values (1004, '4th of the wonderful world of the Meng xianhui ', 'HTTP: // www.erp800.com/net_lover )"
Cmd = newsqlcommand (SQL, Conn)
Cmd. executenonquery ()
Catchaeassqlexception
MessageBox. Show (AE. Message. tostring ())
Endtry
Endsub
'Create a stored procedure
Privatesubcreatespbtn_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlescreatespbtn. Click
SQL = "createproceduremyprocas" + "selectmyname, myaddressfrommytablego"
Executesqlstmt (SQL)
Endsub
'Create a view
Privatesubcreateviewbtn_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlescreateviewbtn. Click
SQL = "createviewmyviewasselectmynamefrommytable"
Executesqlstmt (SQL)
Endsub
'Modify the table
Privatesubbtnaltertable_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
54com.cn
Handlesbtnaltertable. Click
SQL = "altertablemytableaddnewcoldatetimenotnulldefault (getdate ())"
Executesqlstmt (SQL)
Endsub
'Create a rule and an index
Privatesubbtncreateothers_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlesbtncreateothers. Click
SQL = "createuniqueindex" + "myidxonmytable (myname )"
Executesqlstmt (SQL)
SQL = "createrulemyrule" + "as @ myvalues> = 90and @ myvalues <9999"
Executesqlstmt (SQL)
Endsub
'Delete a table
Privatesubbtndroptable_click (byvalsenderassystem. Object, byvaleassystem. eventargs )_
Handlesbtndroptable. Click
Dimsqlasstring = "droptablemytable"
Executesqlstmt (SQL)
Endsub Copy code The Code is as follows: 'browse table data
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
'Browsing stored procedures
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
'Browsing view'
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)
'Open the connection
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