How to run SQL scripts?

  • Thread starter Thread starter Rainer Halanek
  • Start date Start date
R

Rainer Halanek

Hi,
I have a *.sql file which includes a database script. How
or where can I run it?
Thanks, Rainer.
 
You should be able to read the file in as a string (say with a
SteamReader class from System.IO), then pass the string to an ADO.NET
command object. With SQL Server this would look something like:

SqlConnection sqlConnection = new SqlConnection("YOUR_CONN_STR");
sqlConnection.Open();

StreamReader sr = new StreamReader("YOUR_SQL_FILE");
SqlCommand sqlCommand = new SqlCommand(sr.ReadToEnd(), sqlConnection);
sqlCommand.ExecuteNonQuery();
 
I doubt if this will work. SQL scripts often contain "GO" separators that
tell the script processor to execute each section in turn. You'll need
OSQL/ISQL or Query Analyzer to execute the script or include logic to send
each section one-at-a-time.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
The original poster didn't mention batches, but it should be easy
enough to do:

SqlConnection sqlConnection = new SqlConnection(connectionstring);
sqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;

Regex regex = new Regex(BATCH_SEPERATOR); // i.e. "GO"
StreamReader sr = new StreamReader(filename);

string[] batches = regex.Split(sr.ReadToEnd());

for(int i = 0; i < batches.Length; i++)
{
batches = batches.Trim();
if(batches.Length > 0)
{
sqlCommand.CommandText = batches;
sqlCommand.ExecuteNonQuery();
}
}
sqlConnection.Close();
 
Back
Top