CODEWITHSUNDEEP
c#oracle-databasetransactionstransactionscope
ZerOne
ZerOne

Reputation: 1326

Commit some oracle changes in transactionscope immediately?

I have the following code:

using (TransactionScope tran = new TransactionScope())
{
   try
   {
       OracleConnection _transactionDB = new OracleConnection("ConnectionString");
       _transactionDB.Open();

       OracleCommand _command = new OracleCommand();
       _command.Connection = _transactionDB;
       _command.CommandType = CommandType.Text;
       _command.CommandText = "INSERT INTO table (id, text) VALUES (3, 'test')";
       int rowsAffected = _command.ExecuteNonQuery();

       OracleCommand _command2 = new OracleCommand();
       _command2.Connection = _transactionDB;
       _command2.CommandType = CommandType.Text;
       _command2.CommandText = "INSERT INTO log (id, text) VALUES (3, 'Success')";
       int rowsAffected2 = _command2.ExecuteNonQuery();

       //...some other actions(DB changes)
   }
}

Is there a solution to commit the second insert immediately, doesn't matter if the transactionscope fails or not? This insert should always be visible in the database, to easier see what was going on in this transaction.

In oracle there is a 'AUTONOMOUS_TRANSACTION Pragma', which is like the function I need, in C#.

Thanks, Michael

Upvotes: 1

Views: 930

Answers (1)

PT_STAR
PT_STAR

Reputation: 505

Maybe this will solve you problem:

Write a Oracle PL/SQL Procedure to write the log. This procedure must have the pragma "AUTONOMOUS_TRANSACTION". Then call this procedure instead of inserting directly.

Upvotes: 1

Related Questions