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

User · Answer

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

User · Answer

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 lt string gt  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

User · Answer

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 lt string gt  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

User · Answer

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     quot Integrated Security SSPI Persist Security Info False Initial Catalog ccwebgrity Data Source SURAJIT SQLEXPRESS quot            string script   File ReadAllText   quot E  Project Docs MX462-PD MX756 ModMappings1 sql quot             SqlConnection conn   new SqlConnection sqlConnectionString            Server server   new Server new ServerConnection conn             server ConnectionContext ExecuteNonQuery script

User · Answer

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

User · Answer

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

User · Answer

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 lt string gt  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

User · Answer

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

User · Answer

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

User · Answer

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 lt string gt  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  gt  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

[c#] How to execute an .SQL script file using c#

Examples related to c#

Examples related to sql

Examples related to oracle

Examples related to scripting