All geek questions in one place

Multiple statements in one SqlCommand

I have a set of sql scripts that I send to a SQL server using the SqlCommand object in C #. These scripts create stored procedures, and while I just create procedures, everything works finde. If my scripts contain the usual block "if exists ... drop XYZ; create procedure XYZ ...", I get an error message, which should be the first statement in the package. The semicolon segment or "GO" acts as a delimiter.

Any hint on how to execute such a script with a single SqlCommand? I expected that I could set the "Package" property or something like that, but I did not find anything.

+4
source share
3 answers

Conflicting statements must either be separated by a packet separator (by default, GO - in your opinion, does not work), or, if possible, from the point of view of your program logic executed in a different order. However, in most cases, restructuring the order of applications will not be possible, so you will have to resort to separation in different parties , so I suggest launching them in different parties.

+3
source

If you want to present a large sql script with packages in it, you should use SMO. This gives you much more control over how scripts do their job. And doesn't rely on custom sql script shredding logic (do I need to solve problems with this?)

http://msdn.microsoft.com/en-us/library/ms212724.aspx

+1
source

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

But you can use the stored procedure for this task, which will simplify the situation.

0
source

Source: https://habr.com/ru/post/915524/

More articles:


All Articles