execute queries that aren't stored on DB

Question

At work, we sometimes need to execute a SQL script (we open this .SQL file -stored as C:\SQLScript\man.sql-, set proper parameters and then execute it). Right now I've create a C# application, this application is running on my computer and perform some operations against a DB server (the same server where the .sql script works) one of these operations include the execution of that script.

The problem is: how can I execute (from my C# application) the SQL script if it is stored as simple file rather than a real stored procedure?

What you are describing is a text file -- not a stored procedure on your hard drive. You simply need to read the text from the file into your application and execute it as you would any inline SQL -- in most cases with a `SqlCommand` and `SqlConnection` -- or variance of depending on your database provider. — George Johnston, Aug 06 '13 at 14:34
can u post a sample content of the file? Does it contain the `CREATE PROCEDURE` part, or is it just the SQL statements? — asafrob, Aug 06 '13 at 14:35
Is this a production DB? sounds pretty nasty, what permissions does the application user have on the DB? — Christian Phillips, Aug 06 '13 at 14:49

score 1 · Answer 1 · edited Aug 06 '13 at 14:44

1

If it is a valid sql file and it executes as it is then all you have to do is

  string query = File.ReadAllText("yoursqlfilePath");
  using (SqlConnection connection = new SqlConnection(
           connectionString))
{
    SqlCommand command = new SqlCommand(query , connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
}

edited Aug 06 '13 at 14:44

Joe Enos

39,478
11
80
136

answered Aug 06 '13 at 14:39

Ehsan

31,833
6
56
65

@JoeEnos you edited too quickly, i was about to do this. thanks anyways – Ehsan Aug 06 '13 at 14:45

score 0 · Answer 2 · answered Aug 06 '13 at 14:32

0

I believe you could open the file read it in and then execute an SQL command created from the file string and then execute the command.

I am not sure if there is a better way but that's how I would do it.

answered Aug 06 '13 at 14:32

Evan

600
2
7
34

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

I would generally try and convert that to a stored procedure passing parameters which are needed. But you can look at the approach taken here. It basically reads the text from the script into a string and then executes it using ExecuteNonQuery. You can amend the text before calling ExecuteNonQuery.

edited May 23 '17 at 11:50

Community

1
1

answered Aug 06 '13 at 14:35

siddharth

660
2
6
18

execute queries that aren't stored on DB

3 Answers3