Running Transact-SQL script file from within Visual Studio.net

My VB.net project needs to run a .sql file containing transact-sql codes. Is there a way to do this using a connection within VB.net?

Thanks,

Hi Dots
you can read the file as a text file and pass the string of the file to a SQLCommand object|||You will have to check for BATCH separator in the file which requires parsing the file. It is probably easier to just spawn a process like SQLCMD/OSQL and use it to execute the script. You can capture output, error code from the command-line utility and use it in the program.|||

Hi,

I am trying to do what you suggested but can;t get it to work

This is my code...

System.Diagnostics.Process proc = new System.Diagnostics.Process();

proc.EnableRaisingEvents = false;
proc.StartInfo.FileName = @."sqlcmd -i ""C:\Program Files\PCIT\CDEvolution\CreateDB.sql"" -o ""C:\Program Files\PCIT\CDEvolution\CreateDBRes.txt""";

Console.WriteLine(proc.StartInfo.FileName.ToString());

try { proc.Start(); }
catch (Exception e)
{
Console.WriteLine(e.Message);
}

but when I run it I get an erro message saying the file can't be found.

I have tried:

Placing .exe after the "sqlcmd"

Using AppDomain.CurrentDomain.BaseDirectory before the string with and without using the full string for the fille names.

The files do exits in the location spicified but I get the same result every time

How do I get this to work?

Cheers.

PS. You may have noticed I am using C#