Executing batches of SQL statements using ADO.NET
I was writing an auditing tool recently that generated scripts to create triggers. Of course i wanted to guarantee that these scripts did not fail so i did the right thing and first checked if the trigger existed, and dropped it if it did.
Now i wrote these scripts originally in SQL Server Management Studio of course, and used the GO statement to separate them into batches. If you don't do this you will get a horrible error message informing you that CREATE statements must be the first line in a batch.
For more information about batches see: http://www.teratrax.com/tsg/help/queries.html
Now when i went to execute this scripts on the fly, lo and behold i came upon a familiar error message: '
CREATE TRIGGER' must be the first statement in a query batch.
It turns out for some perverse reason ADO.NET interprets the SQL you give it as a single batch, in fact it must go as far as to strip or ignore all
GO statements within it. Logically a
Command would appear to be singular, but regardless you would hope that it would execute the SQL as written.
So having this in mind there is a very simple solution. The below code splits the SQL into separate batches for singular execution:
string sql = "... your sql ..."; Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline); string batches = regex.Split(sql);
Then of course to execute them you just need to loop through each, and execute it as a separate command.