Hello,

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

Thanks,
Miguel

shapper wrote:
> Hello,
> 
> Is it possible to run a SQL script from a C# Console application?
> 

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

shapper wrote:
> 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.

On Nov 17, 10:11=A0pm, "Mr. Arnold" <Arn...@Arnold.com> wrote:
> 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 =3D "Data Source=3DWorkGroup\
\SQLEXPRESS;Initial Catalog=3Dmaster;Integrated Security=3DSSPI";
      FileInfo file =3D new FileInfo("C:\\Users\\Miguel\\Projects\\WCA\
\Solution\\WCA.Lab\\Site.sql");
      String script =3D file.OpenText().ReadToEnd();
      SqlConnection conn =3D new SqlConnection(sqlConnectionString);
      Server server =3D 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[i], conn)
   conn.Open();

   cmd.ExecuteNonQuery();
}

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

"shapper" wrote:

> Hello,
> 
> Is it possible to run a SQL script from a C# Console application?
> 
> Thanks,
> Miguel
> .
> 

On Nov 18, 9:19=A0am, JTC <J...@discussions.microsoft.com> wrote:
> 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 wa=
nt
> to split the string to create an array. Loop through the array then execu=
ting
> each statement.
>
> something like...
>
> string text =3D System.IO.File.ReadAllText("myscript.sql");
> text =3D text.Replace("GO", ";");
> string[] statements =3D text.Split(Char.Parse(";"));
>
> for(int i =3D 0; i < statements.Length ;i++)
> {
> =A0 =A0System.Data.SqlClient.SqlCommand cmd =3D new
> System.Data.SqlClient.SqlCommand(statements[i], conn)
> =A0 =A0conn.Open();
>
> =A0 =A0cmd.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