Execute SQL file on Oracle database using C#-CodePudding

I have written SQL scripts in a script.sql file to create procedures. This script.sql file is located in the application path.

Now I've written a tool in C# that creates a database connection. I would like to run the script file with code through this connection. Is that possible?

private OracleConnection oraCon = new OracleConnection();

...

// ORACLE VERBINDUNG
try
{
    if (oraCon.State != ConnectionState.Open)
    {
        oraCon.ConnectionString = "Data Source="   tboServername.Text   "/"   
        tboDatenbank.Text   ";User Id="   tboLogin.Text   ";Password="   tboPasswort.Text;

        oraCon.Open();
    }
}
catch (Exception ex)
{
    Errorlog(ex.ToString());
}

Can I transfer the script.sql directly to the OraCon connection? Or maybe there is another way?

CodePudding user response：

You can, but maybe not directly - remember that delimiters between different statements (such as ; or /) are not allowed in oraclecommand so if your script has multiple statements in separated by something, then the script should be split on that thing and each statement within run separately. If your script creates stored procedures and hence hs semicolons in because they are part of the stored procedure you can wrap the entire script in ' and EXECUTE IMMEDIATE it

Essentially if you have some script file like

CREATE PROCEDURE BLAH .. semicolons blah..
/
CREATE PROCEDURE BLAH2 .. semicolons blah..

You're looking for something like this:

var statements = File.ReadAllText(path).Split("\n/");

foreach(var statement in statements)
  new OracleCommand($"EXECUTE IMMEDIATE '{statement}'", connection).ExecuteNonQuery();

You perhaps also have the option of just asking sqlplus command line tool to run your script