动态创建MSSQL数据库表存储过程

' 创建数据库
  Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles CreateDBBtn.Click
    conn = New SqlConnection(ConnectionString)
    ' 打开连接
    If conn.State <> ConnectionState.Open Then
      conn.Open()
    End If
    'MyDataBase为数据库名称
    Dim sql As String = "CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, filename = " + _
        "'D:\MyDataBase.mdf', size=3," + "maxsize=5, filegrowth=10%) log on" + "(name=MyDataBase_log, " + _
        "filename='D:\MyDataBase.ldf',size=3," + "maxsize=20,filegrowth=1)"
    cmd = New SqlCommand(sql, conn)
    Try
      cmd.ExecuteNonQuery()
    Catch ae As SqlException
      MessageBox.Show(ae.Message.ToString())
    End Try
  End Sub

'创建表
  Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles CreateTableBtn.Click
    conn = New SqlConnection(ConnectionString)
    ' 打开连接
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If
    ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + _
      "myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)"
    cmd = New SqlCommand(sql, conn)
    Try
      cmd.ExecuteNonQuery()
      ' 添加纪录
      sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _
        "VALUES (1001, _'【孟宪会之精彩世界】之一', 'http://xml.sz.luohuedu.net/', 100 ) "
      cmd = New SqlCommand(sql, conn)
      cmd.ExecuteNonQuery()
      sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _
        "VALUES (1002, '【孟宪会之精彩世界】之二', 'http://www.erp800.com/net_lover/', 99) "
      cmd = New SqlCommand(sql, conn)
      cmd.ExecuteNonQuery()
      sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _
        "VALUES (1003, '【孟宪会之精彩世界】之三', 'http://xml.sz.luohuedu.net/', 99) "
      cmd = New SqlCommand(sql, conn)
      cmd.ExecuteNonQuery()
      sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _
        "VALUES (1004, '【孟宪会之精彩世界】之四', 'http://www.erp800.com/net_lover/', 100) "
      cmd = New SqlCommand(sql, conn)
      cmd.ExecuteNonQuery()
    Catch ae As SqlException
      MessageBox.Show(ae.Message.ToString())
    End Try

  End Sub
  '创建存储过程
  Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles CreateSPBtn.Click
    sql = "CREATE PROCEDURE myProc AS" + " SELECT myName, myAddress FROM myTable GO"
    ExecuteSQLStmt(sql)
  End Sub
  '创建视图
  Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles CreateViewBtn.Click
    sql = "CREATE VIEW myView AS SELECT myName FROM myTable"
    ExecuteSQLStmt(sql)

  End Sub
  '修改表
  Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnAlterTable.Click
    sql = "ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())"
    ExecuteSQLStmt(sql)
  End Sub
  '创建规则和索引
  Private Sub btnCreateOthers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnCreateOthers.Click
    sql = "CREATE UNIQUE INDEX " + "myIdx ON myTable(myName)"
    ExecuteSQLStmt(sql)

    sql = "CREATE RULE myRule " + "AS @myValues >= 90 AND @myValues < 9999"
    ExecuteSQLStmt(sql)
  End Sub

  '删除表
  Private Sub btnDropTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnDropTable.Click
    Dim sql As String = "DROP TABLE MyTable"
    ExecuteSQLStmt(sql)
  End Sub
  '浏览表数据
  Private Sub btnViewData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnViewData.Click
    conn = New SqlConnection(ConnectionString)
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If

ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)
    Dim ds As New DataSet("myTable")
    da.Fill(ds, "myTable")
    DataGrid1.DataSource = ds.Tables("myTable").DefaultView
  End Sub
  '浏览存储过程
  Private Sub btnViewSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnViewSP.Click
    conn = New SqlConnection(ConnectionString)
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If
    ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    Dim da As New SqlDataAdapter("myProc", conn)
    Dim ds As New DataSet("SP")
    da.Fill(ds, "SP")
    DataGrid1.DataSource = ds.DefaultViewManager
  End Sub
  '浏览视图
  Private Sub btnViewView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles btnViewView.Click
    conn = New SqlConnection(ConnectionString)
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If
    ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    Dim da As New SqlDataAdapter("SELECT * FROM myView", conn)
    Dim ds As New DataSet()
    da.Fill(ds)
    DataGrid1.DataSource = ds.DefaultViewManager
  End Sub

 

  Private Sub ExecuteSQLStmt(ByVal sql As String)
    conn = New SqlConnection(ConnectionString)
    ' 打开连接
    If conn.State = ConnectionState.Open Then
      conn.Close()
    End If
    ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;"
    conn.ConnectionString = ConnectionString
    conn.Open()
    cmd = New SqlCommand(sql, conn)
    Try
      cmd.ExecuteNonQuery()
    Catch ae As SqlException
      MessageBox.Show(ae.Message.ToString())
    End Try
  End Sub

 

转载于:https://www.cnblogs.com/MMLoveMeMM/articles/3265918.html