Execute SQL scripts with GO statements

Programming C# SQL

"GO" is not a part of the SQL language, therefore one can't simply execute SQL scripts that contain "GO" statements.

One workaround  would be to split up the SQL script and executing each step between the "GO" keyword as a single SQL script:

var fileContent = File.ReadAllText("query.sql");
var sqlqueries = fileContent.Split(new[] {" GO "}, StringSplitOptions.RemoveEmptyEntries);

var con = new SqlConnection("connstring");
con.Open();

var transaction = con.BeginTransaction("execScript");
var cmd = new SqlCommand("query", con);
cmd.Transaction = transaction;

try
{
foreach (var query in sqlqueries)
{
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
}
finally
{
con.Close();
}

This may  work but has some issues.

You can't use the string "GO" - inserting/updating NVARCHARS that contain "GO" would not be possible, breaking the script. That's why we explicity split by using the string " GO " (Space before and after) in the example above.  But this also means that your SQL script must end with a white space - not that fancy.

This is where SQL Server Management Objects (short: SMO) comes into handy.  SMO can handle the "GO" statement (and also comments).

var script = File.ReadAllText("query.sql");
using (SqlConnection connection = new SqlConnection("connstring"))
{
Server server = new Server(new ServerConnection(connection));
try
{
server.BeginTransaction();
server.ConnectionContext.ExecuteNonQuery(script);
server.CommitTransaction();
}
catch
{
server.RollBackTransaction();
}
}

SMO even makes the usage of SQL Transactions quite easy as shown above.

Beginning with SQL Server 2017 SMO is distributed as the Microsoft.SqlServer.SqlManagementObjects NuGet package to allow devs to develop applications with SMO without downloading and installing additional binaries.