How to execute an .SQL script file using c#

Question

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.

Ultun

Hello Mr. Rich, your question was about Oracle and you accepted a solution that was for sql server ? You changed your DB to sql server ? – Boris

Answer 1

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";

        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

        SqlConnection conn = new SqlConnection(sqlConnectionString);

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

        server.ConnectionContext.ExecuteNonQuery(script);
    }
}

Beaujolais

Great! This solution worked for me for being able to drop and recreate a database, and add tables (via the referenced SQL script file). – Flutist

This method doesn't allow using the "GO" command in your script which is allowed when you run a script from SQL Management Studio or the osql command. msdn.microsoft.com/en-us/library/ms188037.aspx – Chivalric

Rn222: I think you've confused ExecuteNonQuery methods, SqlCommand.ExecuteNonQuery won't allow using "GO" commands, however Server.ConnectionContext.ExecuteNonQuery definitely does (I'm using it right now). – Vespid

Peter is right. Make sure to script if exist when doing drops when you generate scripts, else the script will fail when it tries to drop a non-existant SP or table. – Remotion

Note that you need to add references to the project, to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk and Microsoft.SqlServer.Smo for this answer to work. – Chophouse

Is there an advantage to be using FileInfo over StreamReader here? – Brisson

This solution does not work if I use .NET Framework 2.0. I have tried it. Please correct me if I am wrong. – Numbles

To me it didn't work when using .net 4.0/4.5, when referencing 110\SDK\Assemblies The solution I found was changing the app.Config to <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/> </startup> – Pegmatite

1. I also need to reference Microsoft.SqlServer.Management.Sdk.Sfc.dll. 2. The above code is somehow not considering unicode text. – Madelaine

Looks like the original question is about ORACLE. Sure this solution will work for MS SQL Server, but does anything similar to SMO exist for ORACLE. – Lasonyalasorella

Is there a way to get the result from the query? How do I know if something and what went wrong? – Sower

Does this get the contents of the file at build time - or run time? – Relent

Note: These libraries exist in NuGet package Microsoft.SqlServer.Scripting – Columba

@MikeChristensen That NuGet package is long outdated (last updated in 2012). It's better to add the references directly with SQL Server installed. Add>Reference>Assemblies>Extensions, select Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.sfc, and Microsoft.SqlServer.Smo – Normanormal

It should be noted that via NUGET Package Manager Microsoft.SqlServer.SqlManagementObjects needs to be installed first. – Externalization

Answer 2

I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.

string script = File.ReadAllText(@"E:\someSqlScript.sql");

// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\b*GO\b*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);

Connection.Open();
foreach (string commandString in commandStrings)
{
    if (!string.IsNullOrWhiteSpace(commandString.Trim()))
    {
        using(var command = new SqlCommand(commandString, Connection))
        {
            command.ExecuteNonQuery();
        }
    }
}     
Connection.Close();

Rapture

Exactly. This solution won't even close the file after it's done using it. That could be critical. – Gawen

Use "RegexOptions.Multiline | RegexOptions.IgnoreCase" to match "Go" or "go" cases too. – Zambia

I think the RegexOptions.CultureInvariant flag should be used as well. – Bivouac

This is not 100% working: 'GO' may accept numerical parameter. – Muniments

If there's a comment after GO, it will attempt to execute it as a command. – Premium

Why using the RegEx for go? – Rentfree

instead of using \s, using \b would be more correct, \b means matching a word boundary. Regex.Split(script, @"\bGO\b", RegexOptions.Multiline); – Kunkle

Answer 3

This Works on Framework 4.0 or Higher. Supports "GO". Also show the error message, line, and sql command.

using System.Data.SqlClient;

    private bool runSqlScriptFile(string pathStoreProceduresFile, string connectionString)
    {
        try
        {
            string script = File.ReadAllText(pathStoreProceduresFile);

            // split script on GO command
            System.Collections.Generic.IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
                                     RegexOptions.Multiline | RegexOptions.IgnoreCase);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                foreach (string commandString in commandStrings)
                {
                    if (commandString.Trim() != "")
                    {
                        using (var command = new SqlCommand(commandString, connection))
                        {
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (SqlException ex)
                            {
                                string spError = commandString.Length > 100 ? commandString.Substring(0, 100) + " ...\n..." : commandString;
                                MessageBox.Show(string.Format("Please check the SqlServer script.\nFile: {0} \nLine: {1} \nError: {2} \nSQL Command: \n{3}", pathStoreProceduresFile, ex.LineNumber, ex.Message, spError), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                                return false;
                            }
                        }
                    }
                }
            }
            return true;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return false;
        }
    }

Macromolecule

Nice code, one very minor thing is that doesn't need connection.Close() the connection will be closed by the using you've wrapped it in. – Phyl

Answer 4

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

Tabbatha

Answer 5

This works for me:

public void updatedatabase()
{

    SqlConnection conn = new SqlConnection("Data Source=" + txtserver.Text.Trim() + ";Initial Catalog=" + txtdatabase.Text.Trim() + ";User ID=" + txtuserid.Text.Trim() + ";Password=" + txtpwd.Text.Trim() + "");
    try
    {

        conn.Open();

        string script = File.ReadAllText(Server.MapPath("~/Script/DatingDemo.sql"));

        // split script on GO command
        IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
        foreach (string commandString in commandStrings)
        {
            if (commandString.Trim() != "")
            {
                new SqlCommand(commandString, conn).ExecuteNonQuery();
            }
        }
        lblmsg.Text = "Database updated successfully.";

    }
    catch (SqlException er)
    {
        lblmsg.Text = er.Message;
        lblmsg.ForeColor = Color.Red;
    }
    finally
    {
        conn.Close();
    }
}

Cacophonous

Answer 6

Added additional improvements to surajits answer:

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

namespace MyNamespace
{
    public partial class RunSqlScript : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            var connectionString = @"your-connection-string";
            var pathToScriptFile = Server.MapPath("~/sql-scripts/") + "sql-script.sql";
            var sqlScript = File.ReadAllText(pathToScriptFile);

            using (var connection = new SqlConnection(connectionString))
            {
                var server = new Server(new ServerConnection(connection));
                server.ConnectionContext.ExecuteNonQuery(sqlScript);
            }
        }
    }
}

Also, I had to add the following references to my project:

• C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
• C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

I have no idea if those are the right dll:s to use since there are several folders in C:\Program Files\Microsoft SQL Server but in my application these two work.

Herbartian

This worked for me in .Net 4.7. I didn't need the other dlls mentioned by surajit. However, I had to use version 13.0.0.0 for both Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo, as 13.100.0.0 threw exceptions when instantiating the ServerConnection. – Kossuth

Answer 7

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.

Ultun

A tip regarding sqlplus: if you want to know if script execution was successful you can add WHENEVER SQLERROR EXIT SQL.SQLCODE at the beginning of the script. This way the sqlplus process returns the sql error number as return code. – Assailant

any complete full source code sample ? what is in_database, s ?? – Aime

this doesn't work for me. p.StandardOutput.ReadToEnd(); never exits – Cloudless

Answer 8

There are two points to considerate.

1) This source code worked for me:

private static string Execute(string credentials, string scriptDir, string scriptFilename)
{ 
  Process process = new Process();
  process.StartInfo.UseShellExecute = false;
  process.StartInfo.WorkingDirectory = scriptDir;
  process.StartInfo.RedirectStandardOutput = true;
  process.StartInfo.FileName = "sqlplus";
  process.StartInfo.Arguments = string.Format("{0} @{1}", credentials, scriptFilename);
  process.StartInfo.CreateNoWindow = true;

  process.Start();
  string output = process.StandardOutput.ReadToEnd();
  process.WaitForExit();

  return output;
}

I set the working directory to the script directory, so that sub scripts within the script also work.

Call it e.g. as Execute("usr/pwd@service", "c:\myscripts", "script.sql")

2) You have to finalize your SQL script with the statement EXIT;

Prase

Answer 9

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);
            }
        }              
    }
}

Cardsharp

Answer 10

My approach to executing SQL scripts is different from previously mentioned answers due to:

1. Usage of the using statement, which ensures the correct use of disposable objects.
2. Takes into account the SQL Transactions class. This allows the script contents to be executed but can be rolled back on any error allowing better error handling.
3. Makes use of the ArgumentNullException class that helps prevent exceptions regarding nullable arguments like connectionString or scriptPath.
4. Allows correct execution of scripts containing GO keyword, preventing the following Exception: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'GO'..

The previous answers are sufficient, but I think this approach is a viable option SQL Transactions preventing incomplete scripts being executed on to an SQL database leading to erroneos data modifications.

public async Task ApplySqlCommandAsync(string connectionString, string scriptPath)
{
    ArgumentNullException.ThrowIfNull(connectionString, nameof(connectionString));
    ArgumentNullException.ThrowIfNull(scriptPath, nameof(scriptPath));

    var scriptContents = await ReadAllTextAsync(scriptPath);

    _logger.LogInformation("Opening connection");

    await using SqlConnection sqlConnection = new(connectionString);
    await sqlConnection.OpenAsync();

    SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
    try
    {
        _logger.LogInformation(
            "Executing script: {ScriptPath}",
            scriptPath
        );

        // split script on GO command
        IEnumerable<string> commandStrings = Regex.Split(
                scriptContents,
                @"^\s*GO\s*$",
                RegexOptions.Multiline | RegexOptions.IgnoreCase
            );

        if (commandStrings != null && commandStrings.Any())
        {
            foreach (var commandString in commandStrings)
            {
                if (commandString.Trim() == string.Empty)
                    continue;

                await using var command = new SqlCommand(commandString, sqlConnection);
                command.Transaction = sqlTransaction;
                await command.ExecuteNonQueryAsync();
            }
        }
        else
        {
            _logger.LogInformation(
                    "Could not obtain any {Entity} from script {ScriptPath}",
                    nameof(SqlCommand),
                    scriptPath
                );
        }

        await sqlTransaction.CommitAsync();

        _logger.LogInformation(
            "Successfully executed script: {ScriptPath}",
            scriptPath
        );
    }
    catch (SqlException sqlException)
    {
        _logger.LogError(
            sqlException,
            "Failed at {Now}. Reason: {Reason}",
            DateTimeOffset.UtcNow,
            sqlException.Message
        );

        await sqlTransaction.RollbackAsync();

        throw;
    }
}

I had the need to use Transactions, also added the fix for the 'GO' command from this post

Ayannaaycock

Answer 11

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
        {

        }

    }

}

Masterwork

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. so I merged them all and make the result. But it is still generating an exception "ExecuteNonQuery: CommandText property has not been Initialized." Though it Successfully runs the script file(In my case, successfully create the database and insert data on the first startup). – Masterwork

Answer 12

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)

Milzie