Handling transactions for multiple stored procs calls

Question

I've a .Net 3.5 winforms application in which am running multiple steps. Each step does some calculation and calls one or more stored procs.Some of these stored procs do multiple updates/inserts in the tables in the oracle database.

App UI has "process" and "cancel process" buttons for each step.If the use hits cancel process button, the application is supposed to rollback the database state to its previous state...ie. make the transaction ATOMIC.

So, my question here is, is this possible..?and if yes, to achieve this atomicity, what all things I need to take care of in the app and db side?

Do I need to use .Net's transaction API here?Also, is it required to use BEGIN/COMMIT TRANSACTION blocks in those stored procs??

Please share your thoughts.

Thanks.

Answer 1

First, yes, and second, your C# app (specifically the task layer) should manage the transactions, the sprocs should NOT be transactional unless you can guarantee the ability to do nested transactions that roll back when the parent rolls back (and I can't speak on that point WRT oracle)

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection_methods%28v=VS.71%29.aspx

With your OracleConnection object, before you begin all your work, call BeginTransaction(). Then do all your OracleCommand operations with that connection.

Then if you call Transaction.RollBack or Transaction.Commit all sproc work you do should roll back or commit.

Example right from the link:

public void RunOracleTransaction(string myConnString)
{
   OracleConnection myConnection = new OracleConnection(myConnString);
   myConnection.Open();

   OracleCommand myCommand = myConnection.CreateCommand();
   OracleTransaction myTrans;

   // Start a local transaction
   myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
   // Assign transaction object for a pending local transaction
   myCommand.Transaction = myTrans;

   try
   {
 myCommand.CommandText = "INSERT INTO Dept (DeptNo, Dname, Loc) values (50,     'TECHNOLOGY', 'DENVER')";
     myCommand.ExecuteNonQuery();
 myCommand.CommandText = "INSERT INTO Dept (DeptNo, Dname, Loc) values (60,     'ENGINEERING', 'KANSAS CITY')";
     myCommand.ExecuteNonQuery();
     myTrans.Commit();
     Console.WriteLine("Both records are written to database.");
   }
   catch(Exception e)
   {
     myTrans.Rollback();
     Console.WriteLine(e.ToString());
     Console.WriteLine("Neither record was written to database.");
   }
   finally
   {
     myConnection.Close();
   }
}