Rows Inserted / Updated from OracleScript

[quip85673456]

I'm using an OracleScript object to insert and update data from sql script files. It is working well.

Is there a way to get a row count of the number of rows inserted / updated / deleted from the OracleScript?

I'm handling the Progress and Error events to log when there are errors, and success, but it would be better to be able to log that 400 rows were inserted instead of just "success".

[Pinturiccio]

Is there a way to get a row count of the number of rows inserted / updated / deleted from the OracleScript?

You can use the SqlStatementExecute event of the OracleScript class. In the handler of this event, you can define the type of the statement being executed and disable its execution by the OracleScript class object. Then you can create OracleCommand inside the event handler and get the result for execution of an insert/ update / delete statement and save it to a static variable. Below you can find a complete example.

Code: Select all

static int count = 0;
static void Main(string[] args)
{
            
    OracleMonitor mon = new OracleMonitor() { IsActive = true };
    OracleConnection conn = new OracleConnection("your connection string");
    conn.AutoCommit = true;
    conn.Open();
    string script = "INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');" +
        "INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');" +
        "INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');" +
        "INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');" +
        "Update Dept set loc='Los Angeles' where deptno>10;" +
        "DELETE FROM SCOTT.DEPT2 WHERE DEPTNO >20;";
    OracleScript myScript = new OracleScript(script, conn);
    myScript.Error += new Devart.Common.ScriptErrorEventHandler(OnError);
    myScript.SqlStatementExecute += new SqlStatementExecuteEventHandler(myScript_SqlStatementExecute);
    myScript.Execute();
    Console.WriteLine("Count = " + count);
    conn.Close();
}

static void myScript_SqlStatementExecute(object sender, SqlStatementExecuteEventArgs e)
{
    if (e.StatementType == Devart.Common.SqlStatementType.Insert || e.StatementType == Devart.Common.SqlStatementType.Update||e.StatementType==Devart.Common.SqlStatementType.Delete)
    {
        var script = (OracleScript)sender;
        e.StatementStatus = Devart.Common.SqlStatementStatus.SkipStatement;
        OracleCommand comm = new OracleCommand();
        comm.Connection = script.Connection;
        comm.CommandText = e.Text;
        count += comm.ExecuteNonQuery();
    }
}

static void OnError(object sender, Devart.Common.ScriptErrorEventArgs e)
{
    e.Ignore = true;
    Console.WriteLine(e.Text);
    Console.WriteLine("  Failed.");
}

Duting testing this example we discovered an issue with e.StatementStatus defined incorrectly for SqlStatementType.Delete. We will investigate it and notify you about the results as soon as possible.

[Pinturiccio]

We have fixed the bug with defining delete statement in SqlStatementExecute event handler.
The new build of dotConnect for Oracle 7.7.217 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=26313