.Net Equivilant for sqlcmd or osql

[Harry Keck]

I am writing an application that automates running sql scripts against my
database. Currently, I am using Process.Start("sqlcmd") with a number of
parameters to accomplish this, but I would like to have more control than
running an outside process. I am hoping that there is a .Net class that I
can call directly to run sql scripts, but so far, I have not had any luck
finding one. Can anyone give me any advice to accomplish this task?

 

[Marc Gravell]

What is the database? Any of the DbCommand implementations (SqlCommand
etc) should be able to pass down your script as text. Depending on what
you are doing, there are also higher-level management objects, but these
tend to be vendor-specific.

Marc

 

[Harry Keck]

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.

 

[Claire]

For mysql, i had to do it by reading through the script one line at a time
(upto the ; at end of a sql line) calling executenonquery for each one.
Not sure about other database engines.

 

[Alberto Poblacion]

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.

 

[Nicholas Paldino [.NET/C# MVP]]

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.