I second this. Parsing on the semicolon is not the way that
applications like SQL Management Studio work.
Also, you do not have to strip the comments, it works just fine,
assuming you have carriage returns and the like.
Basically, attach a StreamReader to the FileStream that is opened to the
script. Read EACH line into a string buffer. If the line contains GO and
ONLY GO, then send everything that you have in the buffer (as you add each
new line, remember to add the newline back to the buffer). Then, clear the
buffer, and continue reading.
We've done the exact same thing where I work to take the scripts that
you would use in SQL Management Studio and execute them without running it.
--
- Nicholas Paldino [.NET/C# MVP]
-
mv*@spam.guard.caspershouse.com
"Alberto Poblacion" <ea******************************@poblacion.orgwro te
in message news:%2****************@TK2MSFTNGP03.phx.gbl...
"Harry Keck" <Ha*******@discussions.microsoft.comwrote in message
news:0C**********************************@microsof t.com...
>No, executing a DBCommand does not allow sql batches. My files are
either
create scripts for procedures or tables. When I use a DBCommand on a
statement with more than one command, I get an exception. Osql and
sqlcmd
allow execution of batches containing mulitple statements.
There's a trick: Read your script file and split it on the GO
statements. Each piece between two "GO"s can be sent to the server by
means of an ExecuteNonQuery. Besides splitting on the GOs, I believe that
you also have to strip the comments, which you can do by reading the file
line by line and removing everything from "--" to the end of each line.