Execute Oracle SQL Scripts with C#

Since I've worked with Oracle in C# before I kind of understand you even with the lack of concrete samples.

SET DEFINE OFF never worked for me in C#, so I simply use query = query.Replace("'","''");. You could also use command parameters like the one below to avoid exceptions.

        string query = string.Format(
                                    @"UPDATE CardStatus
                                      SET DateExpired = @dateExpired,
                                          ModifiedBy = @modifiedBy,
                                          ModifiedOn = @modifiedOn
                                      WHERE
                                        IDNo = @idNo");

        List<OracleParameter> parameters = new List<OracleParameter>();

        OracleParameter pIdNo = new OracleParameter("idNo", OracleDbType.Varchar2);

        pIdNo.Value = idNo;

        OracleParameter pDateExpired = new OracleParameter("dateExpired", OracleDbType.Date);

        pDateExpired.Value = dateExpired;

        OracleParameter pModifiedBy = new OracleParameter("modifiedBy", OracleDbType.Varchar2);

        pModifiedBy.Value = "SIS";

        OracleParameter pModifiedOn = new OracleParameter("modifiedOn", OracleDbType.Date);

        pModifiedOn.Value = DateTime.Now;

        parameters.Add(pIdNo);
        parameters.Add(pDateExpired);
        parameters.Add(pModifiedBy);
        parameters.Add(pModifiedOn);

        bool result = _DAL.ExecuteNonQuery(query, parameters.ToArray());

_DAL is simply my helper class for data access, it manages the query executions placing it inside a single transaction so that I get to decide whether to commit or rollback the changes of single to multiple queries.

    public bool ExecuteNonQuery(string query, object[] parameters, [CallerMemberName] string callerMemberName = "")
    {
        bool success = false;

        try
        {
            using (OracleCommand command = new OracleCommand())
            {
                command.Connection = _connection;
                command.Transaction = _transaction;
                command.CommandText = query;
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(parameters);

                command.ExecuteNonQuery();
            }

            this.AuditSQL(query, string.Empty);
            success = true;
        }
        catch (OracleException ex)
        {
            this.AuditSQL(query, ex.Message, callerMemberName);

            if (ex.Number == 54) // SELECT .. FOR UPDATE NOWAIT failed.
            {
                throw new RowLockException();
            }
        }

        return success;
    }

I am not sure if you are interested in looking at my _DAL class but I provided you a snippet to give you an idea of what you can do.

EDIT: Since you've mentioned that the scripts already exists then it is easier, you just have to make sure that there is no ' character within the script to make oracle ask for a variable value and all batch queries must have BEGIN and END;.

Example:

BEGIN

-- INSERT
-- UPDATE
-- INSERT
... etc.

END;

Now, what exactly is your problem? You can read the query from the file and replace all SET DEFINE OFF (as well as other statements you deem unnecessary) with string.Empty and replace all ' with '' if needed. Worst case, you would be using regex to identify and remove unwanted statements.