How Can I Execute Large SQL Scripts with GO Commands in C# Efficiently?

Efficiently execute large SQL scripts containing GO commands in C#

Question:

Using SqlCommand.ExecuteNonQuery() to execute large SQL scripts containing multiple batches separated by GO statements is challenging because it interprets GO as invalid syntax.

Solution 1: Multiple batch processing

One way is to split the SQL script into individual batches based on GO lines and execute each batch separately. While this method works, it can be tedious and error-prone for large scripts.

Solution 2: SQL Server Management Objects (SMO)

A more efficient solution is to leverage SQL Server Management Objects (SMO), which natively supports the GO command delimiter.

Sample code:

public static void Main()
{
    // SQL连接字符串
    string sqlConnectionString = "Integrated Security=SSPI;" +
                                  "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";

    // 从目录获取SQL脚本
    DirectoryInfo di = new DirectoryInfo("c:\temp\sqltest\");
    FileInfo[] rgFiles = di.GetFiles("*.sql");

    // 使用SMO执行每个脚本
    foreach (FileInfo fi in rgFiles)
    {
        string script = File.ReadAllText(fi.FullName);
        using (SqlConnection connection = new SqlConnection(sqlConnectionString))
        {
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
    }
}

Additional libraries:

Another option is to use Phil Haack's library, which encapsulates the functionality of executing SQL scripts using GO delimiters: https://www.php.cn/link/3cdad14c5d7c1e1fa307772a876b42d7

The above is the detailed content of How Can I Execute Large SQL Scripts with GO Commands in C# Efficiently?. For more information, please follow other related articles on the PHP Chinese website!