sp; Me.Text = "动态创建SQL Server数据库、表、存储过程等架构信息"
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
'''' 创建数据库
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 S上一页 [1] [2] [3] 下一页 |
