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?
Best Answer
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:
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