执行大型SQL脚本(使用GO命令)

我需要在C#程序中执行大量的SQL语句(创建一堆表,视图和存储过程).

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

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

1> Jon Galloway..：

---

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

示例代码:

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);
        }
   }
}

如果这对您不起作用,请参阅Phil Haack的图书馆处理:http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators -and.aspx

---

当计算机上未安装某些SQL Dll时，此解决方案导致您的代码失败。.NET使用Windows内置的某些dll。缺少某些SQL功能包（包括管理对象）可能会阻止未发现类似“ Microsoft.SqlServer.SqlClrProvider.dll”的错误。修复它（这不容易），下一个错误将是“ Microsoft.SqlServer.BathParser.dll”等。查找其他解决方案以确保您的应用程序具有灵活性。

---

如何将其与交易集成？使用SqlConnection创建具有挂起事务的ServerConnection时,代码会抛出InvalidOperationException.

2> Blorgbeard..：

---

这就是我为了解决我当前的问题而共同努力的结果.

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();
    }
}

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

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

3> tbreffni..：

---

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

4> John Hubert..：

---

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

5> Filip Cordas..：

---

我看这个在最后决定用几次EF实现 一点修改SqlConnection

public static void ExecuteSqlScript(this SqlConnection sqlConnection, string sqlBatch)
        {
            // Handle backslash utility statement (see http://technet.microsoft.com/en-us/library/dd207007.aspx)
            sqlBatch = Regex.Replace(sqlBatch, @"\\(\r\n|\r|\n)", string.Empty);

            // Handle batch splitting utility statement (see http://technet.microsoft.com/en-us/library/ms188037.aspx)
            var batches = Regex.Split(
                sqlBatch,
                string.Format(CultureInfo.InvariantCulture, @"^\s*({0}[ \t]+[0-9]+|{0})(?:\s+|$)", BatchTerminator),
                RegexOptions.IgnoreCase | RegexOptions.Multiline);

            for (int i = 0; i < batches.Length; ++i)
            {
                // Skip batches that merely contain the batch terminator
                if (batches[i].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase) ||
                    (i == batches.Length - 1 && string.IsNullOrWhiteSpace(batches[i])))
                {
                    continue;
                }

                // Include batch terminator if the next element is a batch terminator
                if (batches.Length > i + 1 &&
                    batches[i + 1].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase))
                {
                    int repeatCount = 1;

                    // Handle count parameter on the batch splitting utility statement
                    if (!string.Equals(batches[i + 1], BatchTerminator, StringComparison.OrdinalIgnoreCase))
                    {
                        repeatCount = int.Parse(Regex.Match(batches[i + 1], @"([0-9]+)").Value, CultureInfo.InvariantCulture);
                    }

                    for (int j = 0; j < repeatCount; ++j)
                    {
                       var command = sqlConnection.CreateCommand();
                       command.CommandText = batches[i];
                       command.ExecuteNonQuery();
                    }
                }
                else
                {
                    var command = sqlConnection.CreateCommand();
                    command.CommandText = batches[i];
                    command.ExecuteNonQuery();
                }
            }
        }

---

@DipakRiswadkar是的，在此问题上被锁定了几次，提供的答案都无法满足我的需求，因此在EF实施方面看起来不错，所以我发布了答案。