创建动态MSSQL数据库表
程序员文章站
2023-10-20 23:31:24
以下是引用片段： 复制代码 代码如下: importssystem.data importssystem.data.sqlclient publicclassfo...
以下是引用片段：
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
复制代码 代码如下:
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