Execute SQL file on Oracle database using C#

Question

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?

Note that if the purpose of creating the tool was to hide the password for the db then a) your exe can just be decompiled to get the password and b) it's not really necessary to even do that because a malicious user can write whatever they want into the sql file- use with caution — Caius Jard, Nov 17 '21 at 07:21

Caius Jard · Accepted Answer · 2021-11-17T07:35:43.667

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

Thanks for the help, that's exactly what I was looking for! – Nov 17 '21 at 07:42 — , Nov 17 '21 at 07:42

Execute SQL file on Oracle database using C#

1 Answers1