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

Use GO command to efficiently execute large SQL scripts

When executing large SQL scripts in C# that contain multiple batches separated by GO statements, the built-in SqlCommand.ExecuteNonQuery() method may encounter errors while processing these statements.

A better way

Consider using SQL Server Management Objects (SMO), which supports GO delimiters. This approach is more efficient and provides a seamless execution experience.

Implementation details

using Microsoft.SqlServer.Management.Smo;

// 示例代码：
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);
    }
  }
}

Alternative methods

If SMO is not available, consider using the library written by Phil Haack for handling GO delimiters:

https://www.php.cn/link/3cdad14c5d7c1e1fa307772a876b42d7

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