.net Oracle Data Provider transaction rollback not working

Question

So this is the problem.

I need to insert into tableA and get its new row id. After that, I must insert that id into tableB. I must commit after the insert into tableA so that when I attempt to insert into tableB I won't get a foreign key exception.

Now, my understanding was that if an exception was raised in the function that inserts into tableB, when the try-catch block catches the exception the original insertion into the table would be rollbacked. It isn't doing that.

I am making a mistake somewhere, but I don't know where. Is there a way to accomplish what I need here?

try
    {

        tableAinsert.ExecuteNonQuery();
        transaction.Commit();

        id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

        if (vsType == "I")
        {
            tableBinsert(vsType, eventId, id);
        }

    }
    catch (Exception err)
    {
         transaction.Rollback();
        throw (err);
    }

Answer 1

Hi, there, a coworker of mine pointed out the problem.

It seems that one must use the same connection and the same transaction to be able to rollback a multi-step insert like this. And that there can only be a single commit per transaction.

This means that each of my functions that had their own connection and transaction commands had to be modified to first accept OracleConnection and OracleTransaction arguments and to strip out the commit() code and other related lines.

So modifying the code I posted, it would look like this

    OracleConnection conn = new OracleConnection();
     // .... create your command, set the connection string, etc, etc
    var transaction = conn.BeginTransaction();
    cmd.Transaction = transaction;

try {

    tableAinsert.ExecuteNonQuery();


    id= Int32.Parse(tableAinsert.Parameters["id"].Value.ToString());

    if (vsType == "I")
    {
        tableBinsert(vsType, eventId, id, conn, transaction);
    }

    transaction.Commit();  //Moved this commit to the end of the block

}
catch (Exception err)
{
     transaction.Rollback();
    throw (err);
}