Handling "GO" Separators in SQL Scripts - the easy way
If you've ever had to execute one or more SQL scripts from ADO.NET, you've likely run into the GO batch terminator issue. Any SQL script that does anything worthwhile has more than one batch, separated by a GO terminator. The problem is that "GO" isn't valid T-SQL, it's just a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window. If you try to execute T-SQL scripts with GO commands in them via ADO.NET SqlCommand.ExecuteNonQuery, you'll get an error that says something like:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch
Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and "gets" the GO statement as a batch separator. And the crowd goes wild!!!
I'm telling you, if you're doing anything with SQL Server from .NET code, you really have to look at SMO.
Here's a simple sample app that iterates SQL scripts in a directory and executes them with that fancy ConnectionContext.ExecuteNonQuery - the one that rocks a house party at the drop of a hat, while retaining the ability to beat a biter down with an aluminum bat:
using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
//Microsoft.SqlServer.Smo.dll
using Microsoft.SqlServer.Management.Smo;
//Microsoft.SqlServer.ConnectionInfo.dll
using Microsoft.SqlServer.Management.Common;
public class RunAllSqlSriptsInDirectory
{
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);
}
}
}