The following is a reference fragment:
Copy Code code as follows:
Importssystem.data
ImportsSystem.Data.SqlClient
PublicClassForm1
InheritsSystem.Windows.Forms.Form
Privateconnectionstringasstring= "Datasource=.;i nitialcatalog=; 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 overrides disposition 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 ()
Copy Code code 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= "Creating a Database"
'
' Createtablebtn
'
Me.createtablebtn.location=newsystem.drawing.point (139,9)
Me.createtablebtn.name= "Createtablebtn"
Me.createtablebtn.tabindex=1
me.createtablebtn.text= "Create a 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 China Network Management Alliance 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 Code code 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 Procedures"
'
' Btnviewview
'
Me.btnviewview.location=newsystem.drawing.point (443,43)
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 (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,_
Copy Code code as follows:
ME.CREATEVIEWBTN,ME.CREATESPBTN,ME.CREATETABLEBTN,ME.CREATEDBBTN})
Me.name= "Form1"
Me.text= "Dynamically create schema information such as SQL Server databases, tables, stored Procedures"
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 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 connection
Ifconn.state=connectionstate.openthen
Conn. Close ()
EndIf
Connectionstring= "Datasource=.;i Nitialcatalog=mydatabase; Userid=sa; password=; "
Conn. Connectionstring=connectionstring
Conn. Open ()
Sql= "createtablemytable" + "(Myidintegerconstraintpkeymyidprimarykey," +_
"Mynamechar (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 Mengxian's Wonderful Worlds ", ' http://xml.sz.luohuedu.net/', 100)"
Cmd=newsqlcommand (Sql,conn)
Cmd. ExecuteNonQuery ()
Sql= "insertintomytable (myid,myname,myaddress,myvalues)" +_
"VALUES (1002, '" Mengxian The Wonderful World "bis", ' http://www.erp800.com/net_lover/', 99) "
#p #
Copy Code code as follows:
Cmd=newsqlcommand (Sql,conn)
Cmd. ExecuteNonQuery ()
Sql= "insertintomytable (myid,myname,myaddress,myvalues)" +_
"VALUES (1003, '" Mengxian of the Wonderful World "III", ' http://xml.sz.luohuedu.net/', 99) "
Cmd=newsqlcommand (Sql,conn)
Cmd. ExecuteNonQuery ()
Sql= "insertintomytable (myid,myname,myaddress,myvalues)" +_
"VALUES (1004, '" Mengxian the Wonderful World of four ", ' http://www.erp800.com/net_lover/', 100)"
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 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<9999"
EXECUTESQLSTMT (SQL)
Endsub
' Delete table
Privatesubbtndroptable_click (Byvalsenderassystem.object,byvaleassystem.eventargs) _
Handlesbtndroptable.click
Dimsqlasstring= "Droptablemytable"
EXECUTESQLSTMT (SQL)
Endsub
Copy Code code 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=.;i Nitialcatalog=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
' Browse stored Procedures
Privatesubbtnviewsp_click (Byvalsenderassystem.object,byvaleassystem.eventargs) _
Handlesbtnviewsp.click
Conn=newsqlconnection (ConnectionString)
Ifconn.state=connectionstate.openthen
Conn. Close ()
EndIf
Connectionstring= "Datasource=.;i Nitialcatalog=mydatabase; Userid=sa; password=; "
Conn. Connectionstring=connectionstring
Conn. Open ()
Dimdaasnewsqldataadapter ("MyProc", conn)
Dimdsasnewdataset ("SP")
Da. Fill (ds, "SP")
Datagrid1.datasource=ds. Defaultviewmanager
Endsub
' Browse view
Privatesubbtnviewview_click (Byvalsenderassystem.object,byvaleassystem.eventargs) _
Handlesbtnviewview.click
Conn=newsqlconnection (ConnectionString)
Ifconn.state=connectionstate.openthen
Conn. Close ()
EndIf
Connectionstring= "Datasource=.;i Nitialcatalog=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 connection
Ifconn.state=connectionstate.openthen
Conn. Close ()
EndIf
Connectionstring= "Datasource=.;i Nitialcatalog=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
