动态创建MSSQL数据库表存储过程
发布时间：2022-10-06 15:45:04 所属栏目：MsSql教程 来源：
导读： ' 创建数据库
Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateDBBtn.Click
conn = New SqlConnection(Connection
Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles CreateDBBtn.Click
conn = New SqlConnection(Connection
|
' 创建数据库 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) " insert表另一张表数据_Mssq创建数据表_a表数据复制到b表 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) Mssq创建数据表_a表数据复制到b表_insert表另一张表数据 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 （编辑：海洋资讯信息网_我爱站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
相关内容
- sql-server – 在SQL Server中设计条件数据库关系
- sql-server-2012 – 如何卸载SQL Server Management Studio
- sql-server – 管理工作室关闭时SQL Server性能不佳
- 详解html-webpack-plugin使用
- sql-server – DL380p-Gen8 D2700适用于SQL 2012标准版的适
- sql 存储过程分页代码 匹配亿万庞大数据量
- sql-server – 如何使用SELECT INTO复制表但忽略IDENTITY属
- sql-server – 每次执行触发器编译吗？
- sql-server – 如何防止Sql Server作业同时运行
- sql-server – 反转TSQL中单词的顺序
站长推荐