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.
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);
}
}
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();
}
}
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);
}
}
}
}
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
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;
}
}
}
}
connection.Close();
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
}
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
{
}
}
}
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.dllC:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dllI 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.
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.
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;
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, @"^\s*GO\s*$", 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();
Source: Stackoverflow.com