SqlCommand() ExecuteNonQuery() 截断命令文本

Question

我正在构建一个自定义的数据库部署工具，我需要读取包含SQL脚本的文本文件，并将它们执行到数据库中。

到目前为止，这是相当简单的事情，看起来还不错。

然而，我遇到了一个问题，文件的内容被成功地完整读取，但一旦传递给SqlCommand并使用SqlCommand.ExecuteNonQuery执行，只有部分脚本被执行。

我启动了Profiler并确认我的代码没有传递所有的脚本。

    private void ExecuteScript(string cmd, SqlConnection sqlConn, SqlTransaction trans)
    {

        SqlCommand sqlCmd = new SqlCommand(cmd, sqlConn, trans);
        sqlCmd.CommandType = CommandType.Text;
        sqlCmd.CommandTimeout = 9000000; // for testing
        sqlCmd.ExecuteNonQuery();

    }

    // I call it like this, readDMLScript contains 543 lines of T-SQL
    string readDMLScript = ReadFile(dmlFile);
    ExecuteScript(readDMLScript, sqlConn, trans);

- H. Abraham Chavez

---

脚本被截断的字符是什么？ - MikeWyatt

ReadFile 方法是如何工作的？你确定它不会跳过一些字符吗？为什么不直接使用 System.IO.File.ReadAllText(filename) 呢？ - marc_s

你从文件中读取了多少字节的文本？ - marc_s

感谢Marc的帮助，我之前使用了Stream reader。为了更清晰明了，我会转换成System.IO.File.ReadAllText(filename)。现在来说问题，我怀疑脚本出了问题，因为在使用SqlCommand执行时，会引发与脚本语法相关的异常，而在Sql Management Studio中运行良好。看起来今天早上我有些头部受伤，结果证明脚本并没有被截断。 - H. Abraham Chavez

但是异常仍然阻碍着我。您知道为什么像这样的东西：System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'.在执行脚本时在SqlCommand下可见，但在Sql Management Studio下却不可见吗？ - H. Abraham Chavez

Answer 1

是的，每个人在第一次开始将SQL脚本文件内容发送到数据库时都会遇到这个问题。

GO不是T-SQL命令。它是所有Microsoft交互式SQL工具（管理工具、isql、osql）识别的批处理结束标记。为了处理它，您将需要编写自己的解析器，以分隔文件中位于 GO 语句之间的每个文本块，并将它们作为单独的命令传递给数据库。

您如何实现解析器由您决定。它可以很简单（逐行读取，检测只由 GO 和空格组成的行），也可以很复杂（对所有语句进行记号化，并判断 GO 是否是真正的语句或字符串或多行注释中的一部分）。

就我个人而言，我选择了第一种选项。它可以在不引起麻烦的情况下处理您可能会遇到的99%所有SQL文件。如果您想要全面深入地编写记号化程序，我相信许多人已经做过了，只需搜索一下Google即可。

示例：

using(var reader = new SqlBatchReader(new StreamReader(dmlFile))) {
    string batch;
    while((batch = reader.ReadBatch()) != null) {
        var cmd = new SqlCommand(batch, conn, trans) { CommandType = CommandType.Text };
        cmd.ExecuteNonQuery();
    }
}

class SqlBatchReader : IDisposable {
    private TextReader _reader;
    public SqlBatchReader(TextReader reader) {
        _reader = reader;
    }
    /// <summary>
    /// Return the next command batch in the file, or null if end-of-file reached.
    /// </summary>
    public string ReadBatch() {
        // TODO: Implement your parsing logic here.
    }
}

- Christian Hayter

---

除非你的脚本文件以“SET IDENTITY_INSERT <table_name> ON”开头，否则没有好的解决方案将数据导出到文件中，以便于.NET进行导入。 - MStodd

@MStodd：SET IDENTITY_INSERT 旨在用于数据导入脚本，因此它是一个很好的解决方案。不过，个人而言，我会使用 SSIS 导入数据。 - Christian Hayter

我现在遇到的问题是，如果我从SSMS导出一个以'SET IDENTITY_INSERT <table_name> ON'开头的脚本，仅仅使用你的代码不就不够了吗？每次插入之前我都需要执行那个命令，不是吗？ - MStodd

@MStodd：SET IDENTITY_INSERT 会一直保持开启状态，除非你手动关闭它，因此你只需设置一次即可。你的脚本出了什么问题？ - Christian Hayter

我遇到了这个问题：https://dev59.com/WVzUa4cB1Zd3GeqPzhW5 - MStodd

@MStodd：我明白了。然而，只有在命令之间重置连接时才会出现问题。如果保持连接开放，就像使用我的代码或SSIS一样，那么它应该可以正常工作。 - Christian Hayter

Answer 2

我在寻找解决此问题的答案时，发现了下面的代码：

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

优点：它简短易懂，并完美地满足了我的需求。

缺点：它不如基于流的解决方案高效，并且是区分大小写（即“GO”而不是“go”）。

string[] commands = sql.Split(new string[]{"GO\r\n", "GO ", "GO\t"}, StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
    var command = new SqlCommand(c, masterConnection);
    command.ExecuteNonQuery();
}

- Bill Staib

Answer 3

SMO中的ExecuteNonQuery与批处理一起使用：

http://msdn.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.database.executenonquery.aspx

- Adam Butler

---

现在使用SMO库后，我建议不要使用它，并推荐使用纯.NET解决方案之一。 - Adam Butler

Answer 4

根据原帖下的评论回答：

GO是Management Studio / osql / isql的标记。它告诉SQL Server发送一批命令。在您的实用程序中，您应该使用GO作为分隔符拆分输入数据，并单独发送每个元素（不包括GO命令）。

- Timores

Answer 5

这是我们使用的 :)

public static class ExtensionMethodsSqlCommand
{
    #region Public

    private static bool IsGo(string psCommandLine)
    {
        if (psCommandLine == null)
            return false;
        psCommandLine = psCommandLine.Trim();
        if (string.Compare(psCommandLine, "GO", StringComparison.OrdinalIgnoreCase) == 0)
            return true;
        if (psCommandLine.StartsWith("GO", StringComparison.OrdinalIgnoreCase))
        {
            psCommandLine = (psCommandLine + "--").Substring(2).Trim();
            if (psCommandLine.StartsWith("--"))
                return true;
        }
        return false;
    }

    [System.Diagnostics.DebuggerHidden]
    public static void ExecuteNonQueryWithGos(this SqlCommand poSqlCommand)
    {
        string sCommandLong = poSqlCommand.CommandText;
        using (StringReader oStringReader = new StringReader(sCommandLong))
        {
            string sCommandLine;
            string sCommandShort = string.Empty;
            while ((sCommandLine = oStringReader.ReadLine()) != null)
                if (ExtensionMethodsSqlCommand.IsGo(sCommandLine))
                {
                    if (sCommandShort.IsNullOrWhiteSpace() == false)
                    {
                        if ((poSqlCommand.Connection.State & ConnectionState.Open) == 0)
                            poSqlCommand.Connection.Open();
                        using (SqlCommand oSqlCommand = new SqlCommand(sCommandShort, poSqlCommand.Connection))
                            oSqlCommand.ExecuteNonQuery();
                    }
                    sCommandShort = string.Empty;
                }
                else
                    sCommandShort += sCommandLine + "\r\n";
        }
    }

    #endregion Public
}

- user2606283

---

为什么不在您发布的代码示例中添加一些描述呢？阅读描述并理解该解决方案是否适用比花时间分析代码更容易。 - Artemix

Answer 6

我最终编写了一个StringReader的实现来完成这个任务。

它可以处理以下内容：

1. 跳过破折号注释中包含的GO
2. 跳过斜杠星号注释中包含的GO
3. 跳过单引号等文字中包含的GO
4. 跳过列名等中包含的GO

因此，它只会在批处理分隔符GO被使用时检测到关键字GO。这意味着它可以正确地拆分SQL文本。

如果您已将SQL终止符（分号）附加到单词GO上，它也可以处理。

您可以在这里找到它的代码：

使用方法如下：

using (var reader = new SqlCommandReader(scriptContents))
       {
            var commands = new List<string>();
            reader.ReadAllCommands(c => commands.Add(c));
            // commands now contains each seperated sql batch.
        }

- Darrell