Prev: Using Dispatcher.BeginInvoke with current foreach member
Next: Enterprise design: Accessing global 'config' data
From: shapper on 17 Nov 2009 17:03
Hello,

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

Thanks,
Miguel
From: Mr. Arnold on 17 Nov 2009 17:11
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.
From: Harlan Messinger on 17 Nov 2009 17:21
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.
From: shapper on 17 Nov 2009 20:09
On Nov 17, 10:11 pm, "Mr. Arnold" <Arn...(a)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 = "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
From: JTC on 18 Nov 2009 04:19
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
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Using Dispatcher.BeginInvoke with current foreach member
Next: Enterprise design: Accessing global 'config' data