How to execute an .SQL script file using c#

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

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.

Using EntityFramework, you can go with a solution like this. I use this code to initialize e2e tests. De prevent sql injection attacks, make sure not to generate this script based on user input or use command parameters for this (see overload of ExecuteSqlCommand that accepts parameters).

public static void ExecuteSqlScript(string sqlScript)
{
    using (MyEntities dataModel = new MyEntities())
    {
        // split script on GO commands
        IEnumerable<string> commands = 
            Regex.Split(
                sqlScript, 
                @"^\s*GO\s*$",
                RegexOptions.Multiline | RegexOptions.IgnoreCase);

        foreach (string command in commands)
        {
            if (command.Trim() != string.Empty)
            {
                dataModel.Database.ExecuteSqlCommand(command);
            }
        }              
    }
}

I couldn't find any exact and valid way to do this. So after a whole day, I came with this mixed code achieved from different sources and trying to get the job done.

But it is still generating an exception ExecuteNonQuery: CommandText property has not been Initialized even though it successfully runs the script file - in my case, it successfully creates the database and inserts data on the first startup.

public partial class Form1 : MetroForm
{
    SqlConnection cn;
    SqlCommand cm;
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        if (!CheckDatabaseExist())
        {
            GenerateDatabase();
        }
    }

    private bool CheckDatabaseExist()
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=SalmanTradersDB;Integrated Security=true");
        try
        {
            con.Open();
            return true;
        }
        catch
        {
            return false;
        }
    }

    private void GenerateDatabase()
    {

        try
        {
            cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("drop databse {0}", "SalmanTradersDB"));
            cm = new SqlCommand(sb.ToString() , cn);
            cn.Open();
            cm.ExecuteNonQuery();
            cn.Close();
        }
        catch
        {

        }
        try
        {
            //Application.StartupPath is the location where the application is Installed
            //Here File Path Can Be Provided Via OpenFileDialog
            if (File.Exists(Application.StartupPath + "\\script.sql"))
            {
                string script = null;
                script = File.ReadAllText(Application.StartupPath + "\\script.sql");
                string[] ScriptSplitter = script.Split(new string[] { "GO" }, StringSplitOptions.None);
                using (cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"))
                {
                    cn.Open();
                    foreach (string str in ScriptSplitter)
                    {
                        using (cm = cn.CreateCommand())
                        {
                            cm.CommandText = str;
                            cm.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
        catch
        {

        }

    }

}

fresh new solution for dotnet using Microsoft.SqlServer.DacFx

all results returned by batch fully accessible

written for fsharp interactive

usage:

dotnet fsi --exec file.fsx connection-string sql-batches-file

// https://github.com/dotnet/SqlClient/issues/1397
// https://github.com/dotnet/fsharp/issues/12703
#r "nuget: Microsoft.Data.SqlClient, 3.0"

#r "nuget: Microsoft.SqlServer.DacFx"

open System
open System.IO
open System.Text
open System.Collections.Generic
open Microsoft.Data.SqlClient
open Microsoft.SqlServer.TransactSql.ScriptDom

let tokens (f:TSqlFragment) = seq {
  for i = f.FirstTokenIndex to f.LastTokenIndex do yield f.ScriptTokenStream[i]
}

let fragToString (f:TSqlFragment) =
  let append (b:StringBuilder) (t:TSqlParserToken) = b.Append(t.Text)
  (Seq.fold append (StringBuilder()) (tokens f)).ToString()

let parse file =
  let parser = TSql150Parser(false)
  use textReader = File.OpenText(file) :> TextReader
  let mutable errors : IList<_> = Unchecked.defaultof<IList<_>>
  let res = parser.Parse(textReader, &errors)
  match errors.Count with
  | 0 -> res
  | _ -> failwith $"parse error in file {file}"

let cs = SqlConnectionStringBuilder(fsi.CommandLineArgs[1])
let dbConn = new SqlConnection(cs.ConnectionString)
dbConn.Open()

let visitor = {
  new TSqlFragmentVisitor() with
  override this.Visit(statement:TSqlBatch) =
    let sql = fragToString statement
    printfn $"running batch:\n{sql}"
    let cmd = new SqlCommand(sql, dbConn)
    cmd.ExecuteNonQuery() |> ignore
}
let parsed = parse fsi.CommandLineArgs[2]
parsed.Accept(visitor)