Execute a large SQL script (with GO commands)

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

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

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