Rows Inserted / Updated from OracleScript

Rows Inserted / Updated from OracleScript

Postby quip85673456 » Wed 13 Mar 2013 16:24

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".
quip85673456
 
Posts: 13
Joined: Mon 18 Apr 2011 16:37

Re: Rows Inserted / Updated from OracleScript

Postby Pinturiccio » Fri 15 Mar 2013 12:32

quip85673456 wrote: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
Devart Team
 
Posts: 1936
Joined: Wed 02 Nov 2011 09:44

Re: Rows Inserted / Updated from OracleScript

Postby Pinturiccio » Fri 05 Apr 2013 08:28

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
Pinturiccio
Devart Team
 
Posts: 1936
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle