How do I execute a large SQL script (with GO commands) from c#?

Question

I need to execute a large set of sql statements (creating a bunch of tables, views and stored procedures) from within a c# program.

These statements need to be separated by "GO" statements, but SqlCommand.ExecuteNonQuery() does not like "GO" statements. My solution, which I suppose I'll post for reference, was to split the sql string on "GO" lines, and execute each batch separately.

Is there an easier/better way?

Answer 1

I also faced the same problem, and I could not find any other way but splitting the single SQL operation in separate files, then executing all of them in sequence.

Obviously the problem is not with lists of DML commands, they can be executed without GO in between; different story with DDL (create, alter, drop...)

Answer 2

This is what I knocked together to solve my immediate problem.

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

It requires GO commands to be on their own line, and will not detect block-comments, so this sort of thing will get split, and cause an error:

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

Answer 3

Use SQL Server Management Objects (SMO) which understands GO separators. See my blog post here: http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way.aspx

Sample code:

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();
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        Server server = new Server(new ServerConnection(connection));
        server.ConnectionContext.ExecuteNonQuery(script);
   }
}

If that won't work for you, see Phil Haack's library which handles that: http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx

Answer 4

You can use SQL Management Objects to perform this. These are the same objects that Management Studio uses to execute queries. I believe Server.ConnectionContext.ExecuteNonQuery() will perform what you need.

Answer 5

The "GO" batch separator keyword is actually used by SQL Management Studio itself, so that it knows where to terminate the batches it is sending to the server, and it is not passed to SQL server. You can even change the keyword in Management Studio, should you so desire.

Answer 6

If you don't want to go the SMO route you can search and replace "GO" for ";" and the query as you would. Note that soly the the last result set will be returned.

Answer 7

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

I used it, but server has timed out. !!!!