'''' 创建数据库 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