`
water84222
  • 浏览: 375153 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

动态创建SQL Server数据库、表、存储过程

SQL ServerSQLWindows.net浏览器 
阅读更多
 

下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法。所要增加的控件如下：

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
  Inherits System.Windows.Forms.Form
  Private ConnectionString As String = "Data Source=.;Initial Catalog=;User Id=sa;Password=;"
  Private reader As SqlDataReader = Nothing
  Private conn As SqlConnection = Nothing
  Private cmd As SqlCommand = Nothing
  Private AlterTableBtn As System.Windows.Forms.Button
  Private sql As String = Nothing
  Private CreateOthersBtn As System.Windows.Forms.Button

#Region " Windows 窗体设计器生成的代码 "
  '窗体重写处置以清理组件列表。
  Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
      If Not (components Is Nothing) Then
        components.Dispose()
      End If
    End If
    MyBase.Dispose(disposing)
  End Sub
  Public Sub New()
    MyBase.New()
    InitializeComponent()
  End Sub
  Private components As System.ComponentModel.IContainer
  Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
  Friend WithEvents CreateDBBtn As System.Windows.Forms.Button
  Friend WithEvents CreateTableBtn As System.Windows.Forms.Button
  Friend WithEvents CreateSPBtn As System.Windows.Forms.Button
  Friend WithEvents CreateViewBtn As System.Windows.Forms.Button
  Friend WithEvents btnAlterTable As System.Windows.Forms.Button
  Friend WithEvents btnCreateOthers As System.Windows.Forms.Button
  Friend WithEvents btnDropTable As System.Windows.Forms.Button
  Friend WithEvents btnViewData As System.Windows.Forms.Button
  Friend WithEvents btnViewSP As System.Windows.Forms.Button
  Friend WithEvents btnViewView As System.Windows.Forms.Button
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Me.CreateDBBtn = New System.Windows.Forms.Button()
    Me.CreateTableBtn = New System.Windows.Forms.Button()
    Me.CreateSPBtn = New System.Windows.Forms.Button()
    Me.CreateViewBtn = New System.Windows.Forms.Button()
    Me.btnAlterTable = New System.Windows.Forms.Button()
    Me.btnCreateOthers = New System.Windows.Forms.Button()
    Me.btnDropTable = New System.Windows.Forms.Button()
    Me.btnViewData = New System.Windows.Forms.Button()
    Me.btnViewSP = New System.Windows.Forms.Button()
    Me.btnViewView = New System.Windows.Forms.Button()
    Me.DataGrid1 = New System.Windows.Forms.DataGrid()
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.SuspendLayout()
    '
    'CreateDBBtn
    '
    Me.CreateDBBtn.Location = New System.Drawing.Point(19, 9)
    Me.CreateDBBtn.Name = "CreateDBBtn"
    Me.CreateDBBtn.Size = New System.Drawing.Size(104, 23)
    Me.CreateDBBtn.TabIndex = 0
    Me.CreateDBBtn.Text = "创建数据库"
    '
    'CreateTableBtn
    '
    Me.CreateTableBtn.Location = New System.Drawing.Point(139, 9)
    Me.CreateTableBtn.Name = "CreateTableBtn"
    Me.CreateTableBtn.TabIndex = 1
    Me.CreateTableBtn.Text = "创建表"
    '
    'CreateSPBtn
    '
    Me.CreateSPBtn.Location = New System.Drawing.Point(230, 9)
    Me.CreateSPBtn.Name = "CreateSPBtn"
    Me.CreateSPBtn.Size = New System.Drawing.Size(104, 23)
    Me.CreateSPBtn.TabIndex = 2
    Me.CreateSPBtn.Text = "创建存储过程"
    '
    'CreateViewBtn
    '
    Me.CreateViewBtn.Location = New System.Drawing.Point(350, 9)
    Me.CreateViewBtn.Name = "CreateViewBtn"
    Me.CreateViewBtn.TabIndex = 3
    Me.CreateViewBtn.Text = "创建视图"
    '
    'btnAlterTable
    '
    Me.btnAlterTable.Location = New System.Drawing.Point(441, 9)
    Me.btnAlterTable.Name = "btnAlterTable"
    Me.btnAlterTable.TabIndex = 4
    Me.btnAlterTable.Text = "修改表"
    '
    'btnCreateOthers
    '
    Me.btnCreateOthers.Location = New System.Drawing.Point(17, 43)
    Me.btnCreateOthers.Name = "btnCreateOthers"
    Me.btnCreateOthers.Size = New System.Drawing.Size(104, 23)
    Me.btnCreateOthers.TabIndex = 5
    Me.btnCreateOthers.Text = "创建规则和索引"
    '
    'btnDropTable
    '
    Me.btnDropTable.Location = New System.Drawing.Point(138, 43)
    Me.btnDropTable.Name = "btnDropTable"
    Me.btnDropTable.TabIndex = 6
    Me.btnDropTable.Text = "删除表"
    '
    'btnViewData
    '
    Me.btnViewData.Location = New System.Drawing.Point(351, 43)
    Me.btnViewData.Name = "btnViewData"
    Me.btnViewData.TabIndex = 7
    Me.btnViewData.Text = "查看数据"
    '
    'btnViewSP
    '
    Me.btnViewSP.Location = New System.Drawing.Point(230, 43)
    Me.btnViewSP.Name = "btnViewSP"
    Me.btnViewSP.Size = New System.Drawing.Size(104, 23)
    Me.btnViewSP.TabIndex = 8
    Me.btnViewSP.Text = "查看存储过程"
    '
    'btnViewView
    '
    Me.btnViewView.Location = New System.Drawing.Point(443, 43)
    Me.btnViewView.Name = "btnViewView"
    Me.btnViewView.TabIndex = 9
    Me.btnViewView.Text = "查看视图"
    '
    'DataGrid1
    '
    Me.DataGrid1.DataMember = ""
    Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.DataGrid1.Location = New System.Drawing.Point(20, 76)
    Me.DataGrid1.Name = "DataGrid1"
    Me.DataGrid1.Size = New System.Drawing.Size(500, 183)
    Me.DataGrid1.TabIndex = 10
    '
    'Form1
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(538, 281)
    Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.btnViewView, _
              Me.btnViewSP, Me.btnViewData, Me.btnDropTable, Me.btnCreateOthers, Me.btnAlterTable, _
              Me.CreateViewBtn, Me.CreateSPBtn, Me.CreateTableBtn, Me.CreateDBBtn})
    Me.Name = "Form1"
    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 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
End Class

转自孟子E章专栏http://blog.csdn.net/net_lover/archive/2003/08/18/6942.aspx


分享到：
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    基于ADO技术动态创建SQL Server数据库.pdf

    在VC++中利用ADO技术实现动态创建SQL Server数据库，不仅能够满足临时数据的存储需要，还能优化数据库的使用效率。本文为数据库开发人员提供了一种动态数据库创建的实现方法，对于学习和应用ADO技术进行数据库操作...

    C# 开发SQLSERVER数据库自动建表

    标题“C# 开发SQLSERVER数据库自动建表”表明我们将讨论一个使用C#开发的程序，该程序可以自动化创建SQL Server数据库中的表结构。这个功能对于数据导入、系统初始化或简化数据库管理流程非常有用。 描述中提到，该...

    基于ADO技术动态创建SQLServer数据库

    ### 基于ADO技术动态创建SQLServer数据库 #### 引言 在现代软件开发中，尤其是涉及到数据处理的应用程序，经常会遇到需要处理临时性数据的情况。这些数据可能源自某个计算过程的中间结果，也可能涉及与其他系统...

    SQL SERVER数据库开发之存储过程应用.rar

    总而言之，"SQL SERVER数据库开发之存储过程应用"教程会涵盖以上这些核心概念，并可能深入到高级主题，如动态SQL、游标、异常处理、临时表和变量的使用等。通过学习和实践，开发者可以提升在SQL Server环境下的...

    kepserver读取SQL Server数据库

    总结来说，KEPServer通过ODBC连接到SQL Server数据库，创建通道和设备，从而实现PLC与数据库之间的数据交互。理解并熟练掌握这一过程，对于在工业自动化环境中实施高效的数据管理和控制策略是至关重要的。

    在Sql Server 数据库中利用存储过程实现动态交叉表

    本文将探讨如何在SQL Server数据库中利用存储过程实现动态交叉表。 首先，我们需要理解交叉表的基本概念。在数据分析和报表生成中，交叉表是一种以行列交叉形式展示数据统计结果的表格，通常用于展示分类数据的汇总...

    Visual C#中动态操作SQL Server数据库

    《Visual C#中动态操作SQL Server数据库》这篇文章探讨了如何使用Visual C#编程语言与SQL Server数据库进行交互，实现动态创建、修改数据库及其对象。在数据库应用开发中，SQL语句，尤其是Select、Insert、Update和...

    vc 动态 创建 sqlserver2000 数据库

    在VC++环境中动态创建SQL Server 2000数据库涉及多个关键步骤，这些步骤包括连接到数据库服务器、创建数据库以及构建表结构。以下是对这些知识点的详细解释： 1. **连接到数据库服务器**： 在VC++中，我们通常使用...

    Visual_C#中动态操作SQL_Server数据库

    在Visual C#中动态操作SQL Server数据库是一项关键技能，它涉及到使用C#编程语言与SQL Server数据库进行交互，包括创建、修改和管理数据库及其对象。本文将深入探讨这个主题，帮助开发者更有效地进行数据库应用程序...

    基于ADO技术动态创建SQL+Server数据库

    ### 基于ADO技术动态创建SQL Server数据库 在现代软件开发中，数据库管理与操作是必不可少的一部分。本文将深入探讨如何使用ADO（ActiveX Data Objects）技术来动态创建SQL Server数据库，包括创建数据库的基本原理...

    Visual C#动态操作SQL Server数据库源码

    6. **动态创建存储过程** 存储过程是一组预编译的SQL语句，可以在需要时执行。创建存储过程的代码如下： ```csharp string createProcedure = "CREATE PROCEDURE GetCustomers @id INT AS SELECT * FROM ...

    C# SQL Server数据库操作DLL

    这个压缩包"**C# SQL Server数据库操作DLL**"提供了一种便捷的方式，通过一个动态链接库（DLL）来处理这些操作，免去了手动编写大量基础数据库访问代码的麻烦。DLL（DBClass.dll）封装了对SQL Server数据库的基本...

    ASP NET链接SQLServer数据库程序 含有数据库文件

    接下来，你可以使用SqlCommand对象执行SQL查询或存储过程，例如： ```csharp SqlCommand command = new SqlCommand("SELECT * FROM YourTableName", connection); SqlDataReader reader = command.ExecuteReader();...

    完全手册C#2005+SQL Server数据库与网络开发

    SQL Server数据库方面，本书会详细介绍如何安装和配置SQL Server，以及创建和管理数据库、表、视图、存储过程等数据库对象。读者将学习T-SQL语言，用于执行数据查询、更新、插入和删除操作，以及数据库设计的基本...

    在Sql Server数据库中利用存储过程实现动态交叉表.pdf

    "在Sql Server数据库中利用存储过程实现动态交叉表.pdf" 本文主要讲述了在Sql Server数据库中利用存储过程实现动态交叉表的技术。交叉表是一种特殊的表格形式，它可以将数据按照某种规则进行转换和重新组织，以便更...

    SQL Server数据库开发实例解析完整版

    在IT领域，SQL Server数据库是微软公司推出的一款关系型数据库管理系统，被广泛应用于企业级数据存储、管理和分析。本资源“SQL Server数据库开发实例解析完整版”提供了丰富的实践案例，帮助开发者深入理解并掌握...

    中国邮政编码 SQL Server 数据库

    数据库采用的是 SQL Server 数据存储系统，这是一种由微软开发的关系型数据库管理系统，以其强大的性能和稳定性著称。 SQL Server 2000 是这一数据库系统的早期版本，尽管已有若干年历史，但在处理大量数据和复杂...

    VisualC#中动态操作SQL+Server数据库[整理].pdf

    在创建 SQL Server 数据库时，我们可以使用 SQL 语句来动态创建数据库，并在数据库中创建表、存储过程、视图、索引等数据库对象。 在读取数据库对象时，我们可以使用 SQL 语句来读取表、存储过程和视图中的数据，并...

Magicbox
Global site tag (gtag.js) - Google Analytics