46306.htm
来自「一本很基础的SQL讲解」· HTM 代码 · 共 34 行 · 第 1/2 页
HTM
34 行
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>Imports System.Data <BR>Imports System.Data.SqlClient </P><P>Public Class Form1 <BR> Inherits System.Windows.Forms.Form <BR> Private ConnectionString As String = "Data Source=.;Initial Catalog=;User Id=sa;Password=;" <BR> Private reader As SqlDataReader = Nothing <BR> Private conn As SqlConnection = Nothing <BR> Private cmd As SqlCommand = Nothing <BR> Private AlterTableBtn As System.Windows.Forms.Button <BR> Private sql As String = Nothing <BR> Private CreateOthersBtn As System.Windows.Forms.Button </P><P>#Region " Windows 窗体设计器生成的代码 " <BR> '窗体重写处置以清理组件列表。 <BR> Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) <BR> If disposing Then <BR> If Not (components Is Nothing) Then <BR> components.Dispose() <BR> End If <BR> End If <BR> MyBase.Dispose(disposing) <BR> End Sub <BR> Public Sub New() <BR> MyBase.New() <BR> InitializeComponent() <BR> End Sub <BR> Private components As System.ComponentModel.IContainer <BR> Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid <BR> Friend WithEvents CreateDBBtn As System.Windows.Forms.Button <BR> Friend WithEvents CreateTableBtn As System.Windows.Forms.Button <BR> Friend WithEvents CreateSPBtn As System.Windows.Forms.Button <BR> Friend WithEvents CreateViewBtn As System.Windows.Forms.Button <BR> Friend WithEvents btnAlterTable As System.Windows.Forms.Button <BR> Friend WithEvents btnCreateOthers As System.Windows.Forms.Button <BR> Friend WithEvents btnDropTable As System.Windows.Forms.Button <BR> Friend WithEvents btnViewData As System.Windows.Forms.Button <BR> Friend WithEvents btnViewSP As System.Windows.Forms.Button <BR> Friend WithEvents btnViewView As System.Windows.Forms.Button <BR> <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() <BR> Me.CreateDBBtn = New System.Windows.Forms.Button() <BR> Me.CreateTableBtn = New System.Windows.Forms.Button() <BR> Me.CreateSPBtn = New System.Windows.Forms.Button() <BR> Me.CreateViewBtn = New System.Windows.Forms.Button() <BR> Me.btnAlterTable = New System.Windows.Forms.Button() <BR> Me.btnCreateOthers = New System.Windows.Forms.Button() <BR> Me.btnDropTable = New System.Windows.Forms.Button() <BR> Me.btnViewData = New System.Windows.Forms.Button() <BR> Me.btnViewSP = New System.Windows.Forms.Button() <BR> Me.btnViewView = New System.Windows.Forms.Button() <BR> Me.DataGrid1 = New System.Windows.Forms.DataGrid() <BR> CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() <BR> Me.SuspendLayout() <BR> ' <BR> 'CreateDBBtn <BR> ' <BR> Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9) <BR> Me.CreateDBBtn.Name = "CreateDBBtn" <BR> Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23) <BR> Me.CreateDBBtn.TabIndex = 0 <BR> Me.CreateDBBtn.Text = "创建数据库" <BR> ' <BR> 'CreateTableBtn <BR> ' <BR> Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9) <BR> Me.CreateTableBtn.Name = "CreateTableBtn" <BR> Me.CreateTableBtn.TabIndex = 1 <BR> Me.CreateTableBtn.Text = "创建表" <BR> ' <BR> 'CreateSPBtn <BR> ' <BR> Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9) <BR> Me.CreateSPBtn.Name = "CreateSPBtn" <BR> Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23) <BR> Me.CreateSPBtn.TabIndex = 2 <BR> Me.CreateSPBtn.Text = "创建存储过程" <BR> ' <BR> 'CreateViewBtn <BR> ' <BR> Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9) <BR> Me.CreateViewBtn.Name = "CreateViewBtn" <BR> Me.CreateViewBtn.TabIndex = 3 <BR> Me.CreateViewBtn.Text = "创建视图" <BR> ' <BR> 'btnAlterTable <BR> ' <BR> Me.btnAlterTable.Location = New System.Drawing.Point(441, 9) <BR> Me.btnAlterTable.Name = "btnAlterTable" <BR> Me.btnAlterTable.TabIndex = 4 <BR> Me.btnAlterTable.Text = "修改表" <BR> ' <BR> 'btnCreateOthers <BR> ' <BR> Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43) <BR> Me.btnCreateOthers.Name = "btnCreateOthers" <BR> Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23) <BR> Me.btnCreateOthers.TabIndex = 5 <BR> Me.btnCreateOthers.Text = "创建规则和索引" <BR> ' <BR> 'btnDropTable <BR> ' <BR> Me.btnDropTable.Location = New System.Drawing.Point(138, 43) <BR> Me.btnDropTable.Name = "btnDropTable" <BR> Me.btnDropTable.TabIndex = 6 <BR> Me.btnDropTable.Text = "删除表" <BR> ' <BR> 'btnViewData <BR> ' <BR> Me.btnViewData.Location = New System.Drawing.Point(351, 43) <BR> Me.btnViewData.Name = "btnViewData" <BR> Me.btnViewData.TabIndex = 7 <BR> Me.btnViewData.Text = "查看数据" <BR> ' <BR> 'btnViewSP <BR> ' <BR> Me.btnViewSP.Location = New System.Drawing.Point(230, 43) <BR> Me.btnViewSP.Name = "btnViewSP" <BR> Me.btnViewSP.Size = New System.Drawing.Size(104, 23) <BR> Me.btnViewSP.TabIndex = 8 <BR> Me.btnViewSP.Text = "查看存储过程" <BR> ' <BR> 'btnViewView <BR> ' <BR> Me.btnViewView.Location = New System.Drawing.Point(443, 43) <BR> Me.btnViewView.Name = "btnViewView" <BR> Me.btnViewView.TabIndex = 9 <BR> Me.btnViewView.Text = "查看视图" <BR> ' <BR> 'DataGrid1 <BR> ' <BR> Me.DataGrid1.DataMember = "" <BR> Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText <BR> Me.DataGrid1.Location = New System.Drawing.Point(20, 76) <BR> Me.DataGrid1.Name = "DataGrid1" <BR> Me.DataGrid1.Size = New System.Drawing.Size(500, 183) <BR> Me.DataGrid1.TabIndex = 10 <BR> ' <BR> 'Form1 <BR> ' <BR> Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) <BR> Me.ClientSize = New System.Drawing.Size(538, 281) <BR> Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _ <BR> Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, </P><P> Me.btnAlterTable, _ <BR> Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn}) <BR> Me.Name = "Form1" <BR> Me.Text = "动态创建SQL Server数据库、表、存储过程等架构信息" <BR> CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() <BR> Me.ResumeLayout(False) </P><P> End Sub </P><P>#End Region </P><P> ' 创建数据库 <BR> Private Sub CreateDBBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ <BR> Handles CreateDBBtn.Click <BR> conn = New SqlConnection(ConnectionString) <BR> ' 打开连接 <BR> If conn.State <> ConnectionState.Open Then <BR> conn.Open() <BR> End If <BR> 'MyDataBase为数据库名称 <BR> Dim sql As String = "CREATE DATABASE MyDataBase ON PRIMARY (Name=MyDataBase_data, </P><P> filename = " + _ <BR> "'D:\MyDataBase.mdf', size=3," + "maxsize=5, filegrowth=10%) log on" + </P><P> "(name=MyDataBase_log, " + _ <BR> "filename='D:\MyDataBase.ldf',size=3," + "maxsize=20,filegrowth=1)" <BR> cmd = New SqlCommand(sql, conn) <BR> Try <BR> cmd.ExecuteNonQuery() <BR> Catch ae As SqlException <BR> MessageBox.Show(ae.Message.ToString()) <BR> End Try <BR> End Sub <BR> '创建表 <BR> Private Sub CreateTableBtn_Click(ByVal sender As System.Object, </P><P> ByVal e As System.EventArgs) _ <BR> Handles CreateTableBtn.Click <BR> conn = New SqlConnection(ConnectionString) <BR> ' 打开连接 <BR> If conn.State = ConnectionState.Open Then <BR> conn.Close() <BR> End If <BR> ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" <BR> conn.ConnectionString = ConnectionString <BR> conn.Open() <BR> sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + _ <BR> "myName CHAR(50) NOT Null, myAddress CHAR(255), myValues FLOAT)" <BR> cmd = New SqlCommand(sql, conn) <BR> Try <BR> cmd.ExecuteNonQuery() <BR> ' 添加纪录 <BR> sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ <BR> "VALUES (1001, _'【孟宪会之精彩世界】之一', 'http://xml.sz.luohuedu.net/', 100 ) " <BR> cmd = New SqlCommand(sql, conn) <BR> cmd.ExecuteNonQuery() <BR> sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ <BR> "VALUES (1002, '【孟宪会之精彩世界】之二', 'http://www.erp800.com/net_lover/', 99) " <BR> cmd = New SqlCommand(sql, conn) <BR> cmd.ExecuteNonQuery() <BR> sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ <BR> "VALUES (1003, '【孟宪会之精彩世界】之三', 'http://xml.sz.luohuedu.net/', 99) " <BR> cmd = New SqlCommand(sql, conn) <BR> cmd.ExecuteNonQuery() <BR> sql = "INSERT INTO myTable(myId, myName, myAddress, myValues) " + _ <BR> "VALUES (1004, '【孟宪会之精彩世界】之四', 'http://www.erp800.com/net_lover/', 100) " <BR> cmd = New SqlCommand(sql, conn) <BR> cmd.ExecuteNonQuery() <BR> Catch ae As SqlException <BR> MessageBox.Show(ae.Message.ToString()) <BR> End Try </P><P> End Sub <BR> '创建存储过程 <BR> Private Sub CreateSPBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ <BR> Handles CreateSPBtn.Click <BR> sql = "CREATE PROCEDURE myProc AS" + " SELECT myName, myAddress FROM myTable GO" <BR> ExecuteSQLStmt(sql) <BR> End Sub <BR> '创建视图 <BR> Private Sub CreateViewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ <BR> Handles CreateViewBtn.Click <BR> sql = "CREATE VIEW myView AS SELECT myName FROM myTable" <BR> ExecuteSQLStmt(sql) </P><P> End Sub <BR> '修改表 <BR> Private Sub btnAlterTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ <BR> Handles btnAlterTable.Click <BR> sql = "ALTER TABLE MyTable ADD newCol datetime NOT NULL DEFAULT (getdate())" <BR> ExecuteSQLStmt(sql) <BR> End Sub <BR> '创建规则和索引 <BR> Private Sub btnCreateOthers_Click(ByVal sender As System.Object, </P><P>ByVal e As System.EventArgs) _ <BR> Handles btnCreateOthers.Click <BR> sql = "CREATE UNIQUE INDEX " + "myIdx ON myTable(myName)" <BR> ExecuteSQLStmt(sql) </P><P> sql = "CREATE RULE myRule " + "AS @myValues >= 90 AND @myValues < 9999" <BR> ExecuteSQLStmt(sql) <BR> End Sub </P><P> '删除表 <BR> Private Sub btnDropTable_Click(ByVal sender As System.Object, </P><P> ByVal e As System.EventArgs) _ <BR> Handles btnDropTable.Click <BR> Dim sql As String = "DROP TABLE MyTable" <BR> ExecuteSQLStmt(sql) <BR> End Sub <BR> '浏览表数据 <BR> Private Sub btnViewData_Click(ByVal sender As System.Object, </P><P> ByVal e As System.EventArgs) _ <BR> Handles btnViewData.Click <BR> conn = New SqlConnection(ConnectionString) <BR> If conn.State = ConnectionState.Open Then <BR> conn.Close() <BR> End If <BR> ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" <BR> conn.ConnectionString = ConnectionString <BR> conn.Open() <BR> Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn) <BR> Dim ds As New DataSet("myTable") <BR> da.Fill(ds, "myTable") <BR> DataGrid1.DataSource = ds.Tables("myTable").DefaultView <BR> End Sub <BR> '浏览存储过程 <BR> Private Sub btnViewSP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ <BR> Handles btnViewSP.Click <BR> conn = New SqlConnection(ConnectionString) <BR> If conn.State = ConnectionState.Open Then <BR> conn.Close() <BR> End If <BR> ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" <BR> conn.ConnectionString = ConnectionString <BR> conn.Open() <BR> Dim da As New SqlDataAdapter("myProc", conn) <BR> Dim ds As New DataSet("SP") <BR> da.Fill(ds, "SP") <BR> DataGrid1.DataSource = ds.DefaultViewManager <BR> End Sub <BR> '浏览视图 <BR> Private Sub btnViewView_Click(ByVal sender As System.Object, </P><P> ByVal e As System.EventArgs) _ <BR> Handles btnViewView.Click <BR> conn = New SqlConnection(ConnectionString) <BR> If conn.State = ConnectionState.Open Then <BR> conn.Close() <BR> End If <BR> ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" <BR> conn.ConnectionString = ConnectionString <BR> conn.Open() <BR> Dim da As New SqlDataAdapter("SELECT * FROM myView", conn) <BR> Dim ds As New DataSet() <BR> da.Fill(ds) <BR> DataGrid1.DataSource = ds.DefaultViewManager <BR> End Sub </P><P> Private Sub ExecuteSQLStmt(ByVal sql As String) <BR> conn = New SqlConnection(ConnectionString) <BR> ' 打开连接 <BR> If conn.State = ConnectionState.Open Then <BR> conn.Close() <BR> End If <BR> ConnectionString = "Data Source=.;Initial Catalog=MyDataBase;User Id=sa;Password=;" <BR> conn.ConnectionString = ConnectionString <BR> conn.Open() <BR> cmd = New SqlCommand(sql, conn) <BR> Try <BR> cmd.ExecuteNonQuery() <BR> Catch ae As SqlException <BR> MessageBox.Show(ae.Message.ToString()) <BR> End Try <BR> End Sub <BR>End Class</P></PRE></TD></TR></TBODY></TABLE></P>
<P><FONT size=4>【相关文章】</FONT></P>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200704/46197.htm" target=_blank><U><FONT color=blue>升级SQL Server数据库系统</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200704/46096.htm" target=_blank><U><FONT color=blue>如何实现SQL Server 2005快速web分页</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200704/45901.htm" target=_blank><U><FONT color=blue>SQL Server MFC DAO类和MFC ODBC类</FONT></U></A></LI></UL>
<DIV align=right>【责任编辑：<A class=ln href="mailto:sunsj@51cto.com">火凤凰</A> TEL：（010）68476606-8007】</DIV></td> </tr> <tr> <td class="d_font4"> </td> </tr> </table>⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?