下面是利用SQL语句创建数据库表存储过程视图索引规则修改表查看数据等的方法所要增加的控件如下
以下是引用片段
ImportsSystemData
ImportsSystemDataSqlClient
PublicClassForm
InheritsSystemWindowsFormsForm
PrivateConnectionStringAsString=DataSource=;InitialCatalog=;UserId=sa;Password=;
PrivatereaderAsSqlDataReader=Nothing
PrivateconnAsSqlConnection=Nothing
PrivatecmdAsSqlCommand=Nothing
PrivateAlterTableBtnAsSystemWindowsFormsButton
PrivatesqlAsString=Nothing
PrivateCreateOthersBtnAsSystemWindowsFormsButton
#RegionWindows窗体设计器生成的代码
窗体重写处置以清理组件列表
ProtectedOverloadsOverridesSubDispose(ByValdisposingAsBoolean)
IfdisposingThen
IfNot(componentsIsNothing)Then
componentsDispose()
EndIf
EndIf
MyBaseDispose(disposing)
EndSub
PublicSubNew()
MyBaseNew()
InitializeComponent()
EndSub
PrivatecomponentsAsSystemComponentModelIContainer
FriendWithEventsDataGridAsSystemWindowsFormsDataGrid
FriendWithEventsCreateDBBtnAsSystemWindowsFormsButton
FriendWithEventsCreateTableBtnAsSystemWindowsFormsButton
FriendWithEventsCreateSPBtnAsSystemWindowsFormsButton
FriendWithEventsCreateViewBtnAsSystemWindowsFormsButton
FriendWithEventsbtnAlterTableAsSystemWindowsFormsButton
FriendWithEventsbtnCreateOthersAsSystemWindowsFormsButton
FriendWithEventsbtnDropTableAsSystemWindowsFormsButton
FriendWithEventsbtnViewDataAsSystemWindowsFormsButton
FriendWithEventsbtnViewSPAsSystemWindowsFormsButton
FriendWithEventsbtnViewViewAsSystemWindowsFormsButton
<SystemDiagnosticsDebuggerStepThrough()>PrivateSubInitializeComponent()
MeCreateDBBtn=NewSystemWindowsFormsButton()
MeCreateTableBtn=NewSystemWindowsFormsButton()
MeCreateSPBtn=NewSystemWindowsFormsButton()
MeCreateViewBtn=NewSystemWindowsFormsButton()
MebtnAlterTable=NewSystemWindowsFormsButton()
MebtnCreateOthers=NewSystemWindowsFormsButton()
MebtnDropTable=NewSystemWindowsFormsButton()
MebtnViewData=NewSystemWindowsFormsButton()
MebtnViewSP=NewSystemWindowsFormsButton()
MebtnViewView=NewSystemWindowsFormsButton()
MeDataGrid=NewSystemWindowsFormsDataGrid()
CType(MeDataGridSystemComponentModelISupportInitialize)BeginInit()
MeSuspendLayout()
CreateDBBtn
MeCreateDBBtnLocation=NewSystemDrawingPoint()
MeCreateDBBtnName=CreateDBBtn
MeCreateDBBtnSize=NewSystemDrawingSize()
MeCreateDBBtnTabIndex=
MeCreateDBBtnText=创建数据库
CreateTableBtn
MeCreateTableBtnLocation=NewSystemDrawingPoint()
MeCreateTableBtnName=CreateTableBtn
MeCreateTableBtnTabIndex=
MeCreateTableBtnText=创建表
CreateSPBtn
MeCreateSPBtnLocation=NewSystemDrawingPoint()
MeCreateSPBtnName=CreateSPBtn
MeCreateSPBtnSize=NewSystemDrawingSize()
MeCreateSPBtnTabIndex=
MeCreateSPBtnText=创建存储过程
CreateViewBtn
MeCreateViewBtnLocation=NewSystemDrawingPoint()
MeCreateViewBtnName=CreateViewBtn
MeCreateViewBtnTabIndex=
MeCreateViewBtnText=创建视图
btnAlterTable
MebtnAlterTableLocation=NewSystemDrawingPoint()
MebtnAlterTableName=btnAlterTable
MebtnAlterTableTabIndex=
MebtnAlterTableText=修改表
btnCreateOthers
MebtnCreateOthersLocation=NewSystemDrawingPoint()
MebtnCreateOthersName=btnCreateOthers
MebtnCreateOthersSize=NewSystemDrawingSize()
MebtnCreateOthersTabIndex=
MebtnCreateOthersText=创建规则和索引
btnDropTable
MebtnDropTableLocation=NewSystemDrawingPoint()
MebtnDropTableName=btnDropTable
MebtnDropTableTabIndex=
MebtnDropTableText=删除表
btnViewData
MebtnViewDataLocation=NewSystemDrawingPoint()
MebtnViewDataName=btnViewData
MebtnViewDataTabIndex=
MebtnViewDataText=查看数据
btnViewSP
MebtnViewSPLocation=NewSystemDrawingPoint()
MebtnViewSPName=btnViewSP
MebtnViewSPSize=NewSystemDrawingSize()
MebtnViewSPTabIndex=
MebtnViewSPText=查看存储过程
btnViewView
MebtnViewViewLocation=NewSystemDrawingPoint()
MebtnViewViewName=btnViewView
MebtnViewViewTabIndex=
MebtnViewViewText=查看视图
DataGrid
MeDataGridDataMember=
MeDataGridHeaderForeColor=SystemDrawingSystemColorsControlText
MeDataGridLocation=NewSystemDrawingPoint()
MeDataGridName=DataGrid
MeDataGridSize=NewSystemDrawingSize()
MeDataGridTabIndex=
Form
MeAutoScaleBaseSize=NewSystemDrawingSize()
MeClientSize=NewSystemDrawingSize()
MeControlsAddRange(NewSystemWindowsFormsControl(){MeDataGridMebtnViewView_
MebtnViewSPMebtnViewDataMebtnDropTableMebtnCreateOthersMebtnAlterTable_
MeCreateViewBtnMeCreateSPBtnMeCreateTableBtnMeCreateDBBtn})
MeName=Form
MeText=动态创建SQLServer数据库表存储过程等架构信息
CType(MeDataGridSystemComponentModelISupportInitialize)EndInit()
MeResumeLayout(False)
EndSub
#EndRegion
创建数据库
PrivateSubCreateDBBtn_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesCreateDBBtnClick
conn=NewSqlConnection(ConnectionString)
打开连接
IfconnState<>ConnectionStateOpenThen
connOpen()
EndIf
MyDataBase为数据库名称
DimsqlAsString=CREATEDATABASEMyDataBaseONPRIMARY(Name=MyDataBase_datafilename=+_
D:\MyDataBasemdfsize=+maxsize=filegrowth=%)logon+(name=MyDataBase_log+_
filename=D:\MyDataBaseldfsize=+maxsize=filegrowth=)
cmd=NewSqlCommand(sqlconn)
Try
cmdExecuteNonQuery()
CatchaeAsSqlException
MessageBoxShow(aeMessageToString())
EndTry
EndSub
创建表
PrivateSubCreateTableBtn_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesCreateTableBtnClick
conn=NewSqlConnection(ConnectionString)
打开连接
IfconnState=ConnectionStateOpenThen
connClose()
EndIf
ConnectionString=DataSource=;InitialCatalog=MyDataBase;UserId=sa;Password=;
connConnectionString=ConnectionString
connOpen()
sql=CREATETABLEmyTable+(myIdINTEGERCONSTRAINTPKeyMyIdPRIMARYKEY+_
myNameCHAR()NOTNullmyAddressCHAR()myValuesFLOAT)
cmd=NewSqlCommand(sqlconn)
Try
cmdExecuteNonQuery()
添加纪录
sql=INSERTINTOmyTable(myIdmyNamemyAddressmyValues)+_
VALUES(_【孟宪会之精彩世界】之一)
cmd=NewSqlCommand(sqlconn)
cmdExecuteNonQuery()
sql=INSERTINTOmyTable(myIdmyNamemyAddressmyValues)+_
VALUES(【孟宪会之精彩世界】之二_lover/)
cmd=NewSqlCommand(sqlconn)
cmdExecuteNonQuery()
sql=INSERTINTOmyTable(myIdmyNamemyAddressmyValues)+_
VALUES(【孟宪会之精彩世界】之三)
cmd=NewSqlCommand(sqlconn)
cmdExecuteNonQuery()
sql=INSERTINTOmyTable(myIdmyNamemyAddressmyValues)+_
VALUES(【孟宪会之精彩世界】之四_lover/)
cmd=NewSqlCommand(sqlconn)
cmdExecuteNonQuery()
CatchaeAsSqlException
MessageBoxShow(aeMessageToString())
EndTry
EndSub
创建存储过程
PrivateSubCreateSPBtn_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesCreateSPBtnClick
sql=CREATEPROCEDUREmyProcAS+SELECTmyNamemyAddressFROMmyTableGO
ExecuteSQLStmt(sql)
EndSub
创建视图
PrivateSubCreateViewBtn_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesCreateViewBtnClick
sql=CREATEVIEWmyViewASSELECTmyNameFROMmyTable
ExecuteSQLStmt(sql)
EndSub
修改表
PrivateSubbtnAlterTable_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnAlterTableClick
sql=ALTERTABLEMyTableADDnewColdatetimeNOTNULLDEFAULT(getdate())
ExecuteSQLStmt(sql)
EndSub
创建规则和索引
PrivateSubbtnCreateOthers_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnCreateOthersClick
sql=CREATEUNIQUEINDEX+myIdxONmyTable(myName)
ExecuteSQLStmt(sql)
sql=CREATERULEmyRule+AS@myValues>=AND@myValues<
ExecuteSQLStmt(sql)
EndSub
删除表
PrivateSubbtnDropTable_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnDropTableClick
DimsqlAsString=DROPTABLEMyTable
ExecuteSQLStmt(sql)
EndSub
浏览表数据
PrivateSubbtnViewData_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnViewDataClick
conn=NewSqlConnection(ConnectionString)
IfconnState=ConnectionStateOpenThen
connClose()
EndIf
ConnectionString=DataSource=;InitialCatalog=MyDataBase;UserId=sa;Password=;
connConnectionString=ConnectionString
connOpen()
DimdaAsNewSqlDataAdapter(SELECT*FROMmyTableconn)
DimdsAsNewDataSet(myTable)
daFill(dsmyTable)
DataGridDataSource=dsTables(myTable)DefaultView
EndSub
浏览存储过程
PrivateSubbtnViewSP_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnViewSPClick
conn=NewSqlConnection(ConnectionString)
IfconnState=ConnectionStateOpenThen
connClose()
EndIf
ConnectionString=DataSource=;InitialCatalog=MyDataBase;UserId=sa;Password=;
connConnectionString=ConnectionString
connOpen()
DimdaAsNewSqlDataAdapter(myProcconn)
DimdsAsNewDataSet(SP)
daFill(dsSP)
DataGridDataSource=dsDefaultViewManager
EndSub
浏览视图
PrivateSubbtnViewView_Click(ByValsenderAsSystemObjectByValeAsSystemEventArgs)_
HandlesbtnViewViewClick
conn=NewSqlConnection(ConnectionString)
IfconnState=ConnectionStateOpenThen
connClose()
EndIf
ConnectionString=DataSource=;InitialCatalog=MyDataBase;UserId=sa;Password=;
connConnectionString=ConnectionString
connOpen()
DimdaAsNewSqlDataAdapter(SELECT*FROMmyViewconn)
DimdsAsNewDataSet()
daFill(ds)
DataGridDataSource=dsDefaultViewManager
EndSub
PrivateSubExecuteSQLStmt(ByValsqlAsString)
conn=NewSqlConnection(ConnectionString)
打开连接
IfconnState=ConnectionStateOpenThen
connClose()
EndIf
ConnectionString=DataSource=;InitialCatalog=MyDataBase;UserId=sa;Password=;
connConnectionString=ConnectionString
connOpen()
cmd=NewSqlCommand(sqlconn)
Try
cmdExecuteNonQuery()
CatchaeAsSqlException
MessageBoxShow(aeMessageToString())
EndTry
EndSub
EndClass
