running SQL Scripts from VB.Net - is OSQL the best way?

[Guest]

I have written a VB.Net application which, on start up, creates a database by
running numerous .SQL files using the OSQL utility. This is the code i'm
using in VB.Net to run the scripts:

Dim proc As New Process
proc.StartInfo.UseShellExecute = True
proc.StartInfo.FileName = "osql"
proc.StartInfo.Arguments = " -E -i C:\SQLScript.SQL"
proc.StartInfo.WindowStyle = ProcessWindowStyle.Normal
proc.Start()

I checked the proc.ExitCode value, but unfortunately, this doesn't seem to
tell you whether the script ran successfully or not. It always returns a 0,
which indicates success.

Can anyone tell me if there is a way which i could run the script from
VB.Net and work out if there were errors or not?

Thanking you in advance!

 

[cecil]

Just use file IO to read the .sql file into a string and then set a
SqlCommand object's CommandText property to the string and
ExecuteNonQuery. Of course wrap this in a try...catch to get any
errors.

Cecil Howell
MCT, MCSD, MCAD.NET

 

[William \(Bill\) Vaughn]

Ah no. A script can have many (many) parts separated by the GO keyword.
These can't be executed without parsing in ADO.NET or any other data access
interface. For simple scripts the dump to ExecuteNonQuery works, but not
when you're running real scripts generated by any number of tools.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________