动态创建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 KEYMssq创建数据表," + _

"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, _'【孟宪会之精彩世界】之一', '#39;, 100 ) "

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _

"VALUES (1002, '【孟宪会之精彩世界】之二', '#39;, 99) "

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _

"VALUES (1003, '【孟宪会之精彩世界】之三', '#39;, 99) "

cmd = New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _

"VALUES (1004, '【孟宪会之精彩世界】之四', '#39;, 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

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!