How to execute an .SQL script file using c#

Question

Hi

I'm sure this question has been answered already, however I was unable to find an answer using the search tool.

Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, some of which are broken over multiple lines. I tried reading in the file and tried executing the file using ODP.NET ... however I don't think ExecuteNonQuery is really designed to do this.

So I tried using sqlplus via spawning a process ... however unless I spawned the process with UseShellExecute set to true sqlplus would hang and never exit. Here's the code that DOESN'T WORK.

Process p = new Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = "sqlplus";
p.StartInfo.Arguments = string.Format("xx/xx@{0} @{1}", in_database, s);
p.StartInfo.CreateNoWindow = true;

bool started = p.Start();
p.WaitForExit();

WaitForExit never returns .... Unless I set UseShellExecute to true. A side effect of UseShellExecute is that you can no capture the redirected output.

This must be such a common thing to do, I'm hoping someone has a solution to this.

Thanks Rich

Answer 1

Put the command to execute the sql script into a batch file then run the below code

string batchFileName = @"c:\batosql.bat";
string sqlFileName = @"c:\MySqlScripts.sql";
Process proc = new Process();
proc.StartInfo.FileName = batchFileName;
proc.StartInfo.Arguments = sqlFileName;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
proc.StartInfo.ErrorDialog = false;
proc.StartInfo.WorkingDirectory = Path.GetDirectoryName(batchFileName);
proc.Start();
proc.WaitForExit();
if ( proc.ExitCode!= 0 )

in the batch file write something like this (sample for sql server)

osql -E -i %1

Answer 2

Just a curious question to the question asked, that how would we ensure the whole Transaction consisting all the statements in sql file to perform or nothing at all using this way ?

asked like this in an answer as didn't want to create a whole new qustion for this, as was not sure if this thing does make sense what I asked !

Answer 3

When I was doing somehting similar as part of an install, I discovered the sql script needed to have an exit statement at the end for SQL*Plus to stop correctly. I would suggest trying, as the last line of the script "exit" followed by a carriage return.

Answer 4

Hi,

I managed to work out the answer by reading the manual :)

This extract from the MSDN

The code example avoids a deadlock condition by calling p.StandardOutput.ReadToEnd before p.WaitForExit. A deadlock condition can result if the parent process calls p.WaitForExit before p.StandardOutput.ReadToEnd and the child process writes enough text to fill the redirected stream. The parent process would wait indefinitely for the child process to exit. The child process would wait indefinitely for the parent to read from the full StandardOutput stream.

There is a similar issue when you read all text from both the standard output and standard error streams. For example, the following C# code performs a read operation on both streams.

Turns the code into this;

                    Process p = new Process();
                    p.StartInfo.UseShellExecute = false;
                    p.StartInfo.RedirectStandardOutput = true;
                    p.StartInfo.FileName = "sqlplus";
                    p.StartInfo.Arguments = string.Format("xxx/xxx@{0} @{1}", in_database, s);

                    bool started = p.Start();
                    // important ... read stream input before waiting for exit.
                    // this avoids deadlock.
                    string output = p.StandardOutput.ReadToEnd();

                    p.WaitForExit();

                    Console.WriteLine(output);

                    if (p.ExitCode != 0)
                    {
                        Console.WriteLine( string.Format("*** Failed : {0} - {1}",s,p.ExitCode));
                        break;
                    }

Which now exits correctly.

Answer 5

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

public partial class ExcuteScript : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

    FileInfo file = new FileInfo(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

    string script = file.OpenText().ReadToEnd();

    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));

    server.ConnectionContext.ExecuteNonQuery(script);
    file.OpenText().Close();

    }
}