首页 > 资讯列表 > 编程/数据库 >> 数据库操作教程

创建动态MSSQL数据库表

数据库操作教程 2022-09-23 16:41:55 转载来源: 网络整理/侵权必删

以下是引用片段:复制代码代码如下:  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窗体设计器生成的代码"  '窗体重写处置以清理组件列表。  ProtectedO

以下是引用片段:
复制代码 代码如下:

  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

标签: 创建 动态 MSSQL 数据库


声明:本文内容来源自网络,文字、图片等素材版权属于原作者,平台转载素材出于传递更多信息,文章内容仅供参考与学习,切勿作为商业目的使用。如果侵害了您的合法权益,请您及时与我们联系,我们会在第一时间进行处理!我们尊重版权,也致力于保护版权,站搜网感谢您的分享!

站长搜索

http://www.adminso.com

Copyright @ 2007~2024 All Rights Reserved.

Powered By 站长搜索

打开手机扫描上面的二维码打开手机版


使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

站长搜索目录系统技术支持