Run SQL Script from Console application

  • Thread starter Thread starter shapper
  • Start date Start date
shapper said:
Hello,

Is it possible to run a SQL script from a C# Console application?
Click to expand...
Sure, there isn't any reason why the choice of user interface would
affect what libraries you can use.
 
Yeah, you make an sproc that contains the script and execute the sproc
in the console application using ADO.NET SQL Command object.
Click to expand...

I am not sure if it is as simple as that ... Or maybe I miss
understood your suggestion.
My T-SQL script includes various GO's and creates a database, its log,
filegroups, tables, relationships, etc.

Since the database does not exist I use a connection to master and I
use the following C# code:


using System;
using System.IO;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public static class Lab {

public static void Run() {

string sqlConnectionString = "Data Source=WorkGroup\
\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI";
FileInfo file = new FileInfo("C:\\Users\\Miguel\\Projects\\WCA\
\Solution\\WCA.Lab\\Site.sql");
String script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}

This is working fine. Any suggestion to improve it is welcome.

Thanks,
Miguel
 
I assume you mean you have a file containing the SQL. If this is the case,
read the content of the file as a string, replace to GO statements with a
";". Execute the string.

However, not all sql statements can be executed in a batch, so you may want
to split the string to create an array. Loop through the array then executing
each statement.

something like...

string text = System.IO.File.ReadAllText("myscript.sql");
text = text.Replace("GO", ";");
string[] statements = text.Split(Char.Parse(";"));

for(int i = 0; i < statements.Length ;i++)
{
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(statements, conn)
conn.Open();

cmd.ExecuteNonQuery();
}

NOTE: "GO" and ";" in comments will present a problem.
 
I assume you mean you have a file containing the SQL. If this is the case,
read the content of the file as a string, replace to GO statements with a
";". Execute the string.

However, not all sql statements can be executed in a batch, so you may want
to split the string to create an array. Loop through the array then executing
each statement.

something like...

string text = System.IO.File.ReadAllText("myscript.sql");
text = text.Replace("GO", ";");
string[] statements = text.Split(Char.Parse(";"));

for(int i = 0; i < statements.Length ;i++)
{
   System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(statements, conn)
   conn.Open();

   cmd.ExecuteNonQuery();

}

NOTE: "GO" and ";" in comments will present a problem.
Click to expand...


I think that if you use the approach I posted before you don't need to
split the string or worry about the GO's.
At least it works fine for me and my script creates a Database,
Filegroups, Logs, Tables, Constraints, etc.

Thanks,
Miguel
 
Back
Top