Run SQL Script from Console application

[shapper]

Hello,

Is it possible to run a SQL script from a C# Console application?

Thanks,
Miguel

 

[Harlan Messinger]

Hello,

Is it possible to run a SQL script from a C# Console application?

Sure, there isn't any reason why the choice of user interface would
affect what libraries you can use.

 

[shapper]

Yeah, you make an sproc that contains the script and execute the sproc
in the console application using ADO.NET SQL Command object.

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

 

[JTC]

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.

 

[shapper]

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 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