C# 执行大型 SQL 脚本（使用 GO 命令）

Execute a large SQL script (with GO commands)

提问by Blorgbeard is out

I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program.

我需要从 C# 程序中执行大量 SQL 语句（创建一堆表、视图和存储过程）。

These statements need to be separated by GOstatements, but SqlCommand.ExecuteNonQuery()does not like GOstatements. My solution, which I suppose I'll post for reference, was to split the SQL string on GOlines, and execute each batch separately.

这些语句需要用GO语句分隔，但SqlCommand.ExecuteNonQuery()不喜欢GO语句。我的解决方案，我想我会发布以供参考，是将 SQL 字符串按GO行拆分，并分别执行每个批处理。

Is there an easier/better way?

有没有更简单/更好的方法？

采纳答案by Jon Galloway

Use SQL Server Management Objects (SMO) which understands GO separators. See my blog post here: http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way

使用理解 GO 分隔符的 SQL Server 管理对象 (SMO)。在此处查看我的博客文章：http: //weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way

Sample code:

示例代码：

public static void Main()    
{        
  string scriptDirectory = "c:\temp\sqltest\";
  string sqlConnectionString = "Integrated Security=SSPI;" +
  "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
  DirectoryInfo di = new DirectoryInfo(scriptDirectory);
  FileInfo[] rgFiles = di.GetFiles("*.sql");
  foreach (FileInfo fi in rgFiles)
  {
        FileInfo fileInfo = new FileInfo(fi.FullName);
        string script = fileInfo.OpenText().ReadToEnd();
        using (SqlConnection connection = new SqlConnection(sqlConnectionString))
        {
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
   }
}

If that won't work for you, see Phil Haack's library which handles that: http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx

如果这对您不起作用，请参阅处理该问题的 Phil Haack 库：http: //haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators -and.aspx

回答by ila

I also faced the same problem, and I could not find any other way but splitting the single SQL operation in separate files, then executing all of them in sequence.

我也遇到了同样的问题，除了将单个 SQL 操作拆分到单独的文件中，然后按顺序执行所有这些操作之外，我找不到任何其他方法。

Obviously the problem is not with lists of DML commands, they can be executed without GO in between; different story with DDL (create, alter, drop...)

显然问题不在于 DML 命令列表，它们可以在没有 GO 的情况下执行；DDL 的不同故事（创建、更改、删​​除...）

回答by Blorgbeard is out

This is what I knocked together to solve my immediate problem.

这就是我为了解决眼前的问题而拼凑起来的东西。

private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {
    string sqlBatch = string.Empty;
    SqlCommand cmd = new SqlCommand(string.Empty, conn);
    conn.Open();
    sql += "\nGO";   // make sure last batch is executed.
    try {
        foreach (string line in sql.Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries)) {
            if (line.ToUpperInvariant().Trim() == "GO") {
                cmd.CommandText = sqlBatch;
                cmd.ExecuteNonQuery();
                sqlBatch = string.Empty;
            } else {
                sqlBatch += line + "\n";
            }
        }            
    } finally {
        conn.Close();
    }
}

It requires GO commands to be on their own line, and will not detect block-comments, so this sort of thing will get split, and cause an error:

它要求 GO 命令在他们自己的行上，并且不会检测块注释，所以这种事情会被拆分，并导致错误：

ExecuteBatchNonQuery(@"
    /*
    GO
    */", conn);

回答by tbreffni

You can use SQL Management Objectsto perform this. These are the same objects that Management Studio uses to execute queries. I believe Server.ConnectionContext.ExecuteNonQuery()will perform what you need.

您可以使用SQL 管理对象来执行此操作。这些对象与 Management Studio 用于执行查询的对象相同。我相信Server.ConnectionContext.ExecuteNonQuery()会执行你需要的。

回答by John Hubert

The "GO" batch separator keyword is actually used by SQL Management Studio itself, so that it knows where to terminate the batches it is sending to the server, and it is not passed to SQL server. You can even change the keyword in Management Studio, should you so desire.

“GO”批处理分隔符关键字实际上由 SQL Management Studio 本身使用，因此它知道在哪里终止它发送到服务器的批处理，并且它不会传递到 SQL 服务器。如果您愿意，您甚至可以在 Management Studio 中更改关键字。

回答by jason saldo

If you don't want to go the SMO route you can search and replace "GO" for ";" and the query as you would. Note that soly the the last result set will be returned.

如果您不想走 SMO 路线，您可以搜索并将“GO”替换为“;” 和查询一样。请注意，只会返回最后一个结果集。

回答by Natalya

If you don't want to install SMO objects you can use gplex tool (see this answer)

如果您不想安装 SMO 对象，则可以使用 gplex 工具（请参阅此答案）

回答by grv

Too difficult :)

太难了 ：）

Create array of strings str[] replacing GO with ",@" :

创建字符串数组 str[] 用 ",@" 替换 GO ：

            string[] str ={
                @"
USE master;
",@"


CREATE DATABASE " +con_str_initdir+ @";
",@"
-- Verify the database files and sizes
--SELECT name, size, size*1.0/128 AS [Size in MBs] 
--SELECT name 
--FROM sys.master_files
--WHERE name = N'" + con_str_initdir + @"';
--GO

USE " + con_str_initdir + @";
",@"

SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customers](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"



SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GOODS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GOODS](
    [GoodsID] [int] IDENTITY(1,1) NOT NULL,
    [GoodsName] [nvarchar](50) NOT NULL,
    [GoodsPrice] [float] NOT NULL,
 CONSTRAINT [PK_GOODS] PRIMARY KEY CLUSTERED 
(
    [GoodsID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [Date] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderDetails](
    [OrderID] [int] NOT NULL,
    [GoodsID] [int] NOT NULL,
    [Qty] [int] NOT NULL,
    [Price] [float] NOT NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [GoodsID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"

SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCustomers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[InsertCustomers]
 @CustomerName nvarchar(50),
 @Identity int OUT
AS
INSERT INTO Customers (CustomerName) VALUES(@CustomerName)
SET @Identity = SCOPE_IDENTITY()

' 
END
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
ON UPDATE CASCADE
",@"
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetails_GOODS]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_GOODS] FOREIGN KEY([GoodsID])
REFERENCES [dbo].[GOODS] ([GoodsID])
ON UPDATE CASCADE
",@"
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_GOODS]
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetails_Orders]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON UPDATE CASCADE
ON DELETE CASCADE
",@"
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]


                "};


            for(int i =0; i<str.Length;i++)     
            {
                myCommand.CommandText=str[i];
                try
                {
                myCommand.ExecuteNonQuery();
                }
                catch (SystemException ee)
                {
                    MessageBox.Show("Error   "+ee.ToString());
                }

            }

That's all, enjoy.

就这些，尽情享受吧。

回答by Andy Dove

I accomplished this today by loading my SQL from a text file into one string. I then used the string Split function to separate the string into individual commands which were then sent to the server individually. Simples :)

我今天通过将我的 SQL 从文本文件加载到一个字符串中来实现这一点。然后我使用字符串拆分函数将字符串分成单独的命令，然后单独发送到服务器。简单:)

Just realised that you need to split on \nGO just in case the letters GO appear in any of your table names etc. Guess I was lucky there!

刚刚意识到您需要在 \nGO 上拆分，以防万一字母 GO 出现在您的任何表名等中。猜猜我在那里很幸运！

回答by Ryan Penfold

Based on Blorgbeard's solution.

基于Blorgbeard的解决方案。

foreach (var sqlBatch in commandText.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
{
   sqlCommand.CommandText = sqlBatch;
   sqlCommand.ExecuteNonQuery();
}