Multiple statements in single SqlCommand

Question

I have a set of sql scripts which I send to SQL server using a SqlCommand object in C#. These scripts create stored procedures and as long as I just create the procedures, everything works finde. If my scripts contain the usual "if exists ... drop XYZ; create procedure XYZ ..." block, I get an error which tells me, that create must be the first statement in a batch. Neither semicolon nor "GO" work as separator.

Any hint how to execute such a script using a single SqlCommand? I have expected to be able to set a property to "Batch" or something like that, but I did not found anything.

Answer 1

insert GO between statements and it will work. it worked for me.

Answer 2

; should work fine, as seen in http://www.java2s.com/Code/CSharp/Database-ADO.net/ExecutemultipleSQLstatementsusingaSqlCommandobject.htm

But you could use a stored procedure for this task which would make things easier.

Answer 3

The conflicting statements must either be separated by a batch separator (default GO - which you say doesn't work), or, if possible from the point of view of your program logic executed in a different order. However, in most case restructuring of the order of statements will not be possible so that you have to resort to the separation in different batches so I would suggest running them in different batches.