C# GO 语句在 .NET 中炸毁了 sql 执行

GO statements blowing up sql execution in .NET

提问by HerrimanCoder

I have a very simple C# command shell app that executes a sql script generated by SQL Server for scripting schema and data. It's blowing up on the "GO" statements. Error message:

我有一个非常简单的 C# 命令外壳应用程序，它执行由 SQL Server 生成的用于脚本架构和数据的 sql 脚本。它在“GO”语句上爆炸。错误信息：

Incorrect syntax near 'GO'.

“GO”附近的语法不正确。

Here is the full sql script:

这是完整的sql脚本：

/****** Object:  Table [gym].[MembershipStatus]    Script Date: 9/3/2013 9:24:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [gym].[MembershipStatus](
    [MembershipStatusID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](75) NOT NULL,
    [Description] [varchar](400) NOT NULL,
    [AllowCheckin] [bit] NOT NULL,
    [IncludeInCollections] [bit] NOT NULL,
    [ScheduleFutureInvoices] [bit] NOT NULL,
 CONSTRAINT [MembershipStatus_PK] PRIMARY KEY CLUSTERED 
(
    [MembershipStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [gym].[MembershipStatus] ON 

INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (1, N'Active', N'Active', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (2, N'Cancelled', N'Cancelled', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (3, N'Collection', N'Collection', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (4, N'Deleted', N'Deleted', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (5, N'Expired', N'Expired', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (6, N'Freeze', N'Freeze', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (7, N'Inactive', N'Inactive', 0, 1, 1)
SET IDENTITY_INSERT [gym].[MembershipStatus] OFF
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Name]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Description]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [AllowCheckin]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [IncludeInCollections]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [ScheduleFutureInvoices]
GO

The relevant section of my code looks like this:

我的代码的相关部分如下所示：

SqlCommand command = new SqlCommand(script, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

Any ideas?

有任何想法吗？

回答by Sparky

GO is not a valid QA command, it is a batch separator... It is processed by Enterprise Manager to separate SQL scripts. As such, it will work in Enterprise Manager, but not in database calls from C# or other external programs....

GO 不是有效的 QA 命令，它是批处理分隔符...它由企业管理器处理以分隔 SQL 脚本。因此，它可以在企业管理器中工作，但不能在来自 C# 或其他外部程序的数据库调用中工作......

回答by Pablo Romeo

As mentioned in another answer, GOis not supported.

如另一个答案中所述，GO不支持。

You can use String.Split()on your script using your GOstatements as delimiters, and run each segment as a command, separately.

您可以String.Split()在脚本中使用GO语句作为分隔符，并将每个段作为命令单独运行。

回答by Scott Chamberlain

GOis not part of SQL, it is something SQL Server Management Studio does for you to split the script up.

GO不是 SQL 的一部分，它是 SQL Server Management Studio 为您拆分脚本所做的事情。

What you need to do is read the query in to a string then split on GOon a line by itself (you may want to use Regex for this)

您需要做的是将查询读入一个字符串，然后单独在GO一行上拆分（您可能需要为此使用正则表达式）

//Its better to dispose the SqlCommand, I also switched constructors so I could re-use the SqlCommand.
using(SqlCommand command = new SqlCommand())
{
    command.Connection = connection;

    var scripts = Regex.Split(script, @"^\w+GO$", RegexOptions.Multiline);
    foreach(var splitScript in scripts)
    {
        command.CommandText = splitScript;
        command.ExecuteNonQuery();
    }
}

Look at Matt Johnson's answerfor a less naive implementation of the GOsplitting.

看看马特约翰逊的答案，以了解GO分裂的不太天真。

回答by iamkrillin

If you want to be able to use GOyou will need to reference to the following dlls

如果你想能够使用GO你将需要参考以下dll

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.SqlEnum.dll

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.SqlEnum.dll

Then execute like so

然后像这样执行

 using (SqlConnection conn = new SqlConnection(connection))
 {
     Server db = new Server(new ServerConnection(conn));
     string script = File.ReadAllText(scriptPath);
     db.ConnectionContext.ExecuteNonQuery(script);      
 }

回答by Matt Johnson-Pint

As others mentioned, split your string by GOstatements. But be careful, you may have the text "GO"in other parts of your script. You might also have whitespace before or after the GO statement, and you might have comments on the line after the GO statement also. Any of that would be valid in SSMS, so you may want to test for it.

正如其他人提到的，按GO语句拆分您的字符串。但请注意，您"GO"的脚本的其他部分可能包含该文本。在 GO 语句之前或之后也可能有空格，并且在 GO 语句之后的行中也可能有注释。其中任何一个在 SSMS 中都是有效的，因此您可能需要对其进行测试。

Here is the method I use:

这是我使用的方法：

private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
    // Split by "GO" statements
    var statements = Regex.Split(
            sqlScript,
            @"^[\t ]*GO[\t ]*\d*[\t ]*(?:--.*)?$",
            RegexOptions.Multiline |
            RegexOptions.IgnorePatternWhitespace |
            RegexOptions.IgnoreCase);

    // Remove empties, trim, and return
    return statements
        .Where(x => !string.IsNullOrWhiteSpace(x))
        .Select(x => x.Trim(' ', '\r', '\n'));
}

回答by Eric King

As an alternative to massaging the scripts to make them runnable through C#, you could just run them as-is by using the sqlcmdutility. Lot of details at:

作为按摩脚本以使其可通过 C# 运行的替代方法，您可以使用该sqlcmd实用程序按原样运行它们。很多细节在：

http://technet.microsoft.com/en-us/library/ms180944.aspx

http://technet.microsoft.com/en-us/library/ms180944.aspx

By using sqlcmd, you can script out the execution of any number of your SQL Server generated scripts, without stripping out the Gostatements.

通过使用 sqlcmd，您可以编写任意数量的 SQL Server 生成脚本的执行脚本，而无需删除Go语句。

回答by user2011910

string[] commands = sql.Split( 
    new string[]{"GO\r\n", "GO ", "GO\t"}, StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
    command = new SqlCommand(c, masterConnection);
    command.ExecuteNonQuery();
}
}
catch (Exception e)
{
    MessageBox.Show(e.Message);
}
finally
{
    masterConnection.Close();
}
}

Found here. http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx

在这里找到。http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx

回答by Sergey T

The top answer has a mistake. I just tested a working solution: You should allow space,';' or new line before GO

楼上的回答有误。我刚刚测试了一个可行的解决方案：你应该留出空间，';' 或在 GO 前换行

            var scripts = Regex.Split(statementText, @"(\s+|;|\n|\r)GO", RegexOptions.Multiline);
            foreach(var splitScript in scripts.Where(splitScript => !splitScript.IsNullOrWhiteSpace())) {
                cmd.CommandText = splitScript;
                cmd.ExecuteNonQuery();
            }

回答by Andrew Raynier

1 additional point to "iamkrillin"'s answer, for using the old DLLs to make it work.

“iamkrillin”的答案还有 1 点，用于使用旧的 DLL 使其工作。

after adding the references to these DLLs

添加对这些 DLL 的引用后

Microsoft.SqlServer.ConnectionInfo.dll , Microsoft.SqlServer.Management.Sdk.Sfc.dll Microsoft.SqlServer.Smo.dll , Microsoft.SqlServer.SqlEnum.dll

Microsoft.SqlServer.ConnectionInfo.dll、Microsoft.SqlServer.Management.Sdk.Sfc.dll、Microsoft.SqlServer.Smo.dll、Microsoft.SqlServer.SqlEnum.dll

from a place like this: "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll" to the project, I needed to add the following "using" directives to the top of my code file:

从这样的地方：“C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll”到项目，我需要将以下“使用”指令添加到我的代码文件的顶部：

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

....
string query = @" //sql multi-command text here"

using (SqlConnection thisconn = new SqlConnection(connectionString)) {
    Server db = new Server(new ServerConnection(thisconn));
    db.ConnectionContext.ExecuteNonQuery(query);
}

回答by Didar_Uranov

There is very nice SqlServerBatchParserclass inside FluentMigrator library

FluentMigrator 库中有非常好的SqlServerBatchParser类

Supports GOGO 3statements.

支持GOGO 3声明。

You need to install FluentMigrator.Runner.SqlServernuget package

您需要安装FluentMigrator.Runner.SqlServernuget 包

Example generously copied(slightly modified) from FluentMigrator source code:

从 FluentMigrator 源代码中大量复制（稍作修改）的示例：

using FluentMigrator.Runner.BatchParser;
using FluentMigrator.Runner.BatchParser.SpecialTokenSearchers;
using FluentMigrator.Runner.BatchParser.Sources;

void Main()
{
    var connString = "Server=.;Database=mydb;Trusted_Connection=True;";
    var sql = @"select 1;
GO
SELECT 2;
GO 5";
    ExecuteBatchNonQuery(connString, sql);
}

public static void ExecuteBatchNonQuery(string ConnectionString, string sql)
{
    var sqlBatch = string.Empty;
    var conn = new SqlConnection(ConnectionString);
    conn.Open();

    try
    {
        var parser = new SqlServerBatchParser();
        parser.SqlText += (sender, args) => { sqlBatch = args.SqlText.Trim(); };
        parser.SpecialToken += (sender, args) =>
        {
            if (string.IsNullOrEmpty(sqlBatch))
                return;

            if (args.Opaque is GoSearcher.GoSearcherParameters goParams)
            {
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = sqlBatch;

                    for (var i = 0; i != goParams.Count; ++i)
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }

            sqlBatch = null;
        };

        using (var source = new TextReaderSource(new StringReader(sql), true))
        {
            parser.Process(source, stripComments: true);
        }

        if (!string.IsNullOrEmpty(sqlBatch))
        {
            using (var command = conn.CreateCommand())
            {
                command.CommandText = sqlBatch;
                command.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    {
        using (var message = new StringWriter())
        {
            message.WriteLine("An error occured executing the following sql:");
            message.WriteLine(string.IsNullOrEmpty(sqlBatch) ? sql : sqlBatch);
            message.WriteLine("The error was {0}", ex.Message);

            throw new Exception(message.ToString(), ex);
        }
    }
    finally
    {
        conn?.Dispose();
    }
}

Credits: https://github.com/fluentmigrator/fluentmigrator/blob/v3.2.1/src/FluentMigrator.Runner.SqlServer/Processors/SqlServer/SqlServerProcessor.cs

学分：https: //github.com/fluentmigrator/fluentmigrator/blob/v3.2.1/src/FluentMigrator.Runner.SqlServer/Processors/SqlServer/SqlServerProcessor.cs