How to implement nested SQL transactions with ADO.NET?

Question

I need to implement nested transactions in .NET using ADO.NET.

The situation is as follows:

--> Start Process (Begin Transaction)       

   --> Do DB things       

   --> Begin Transaction for step 1
   --> Step 1
   --> Commit transaction for step 1

   --> Begin transaction for step 2
   --> Step 2
   --> Rollback transaction for step 2

   --> etc ...

   --> Do DB things       


--> End Process(Commit or Rollback ALL commited steps --> a.k.a the process)

Can that be done with transaction scopes? Could anyone post an example?

In addition I'd need the process to work for SQL Server 2005 AND Oracle 10g databases... will transaction scopes work with both database engines?

Edit: Note this situation might happen:

Step1 is commited, Step2 is rolled back Step3 is commited.

The process is commited

(Step1 and Step3 do store data into the database, step2 does not)

On the other hand...

Step1 is commited, Step2 is rolled back Step3 is commited.

The process is rolled back.

the NO DATA IS COMMITED to the database

Note: No DB schema or domain values available

Answer 1

This article talks about savepoints and nested transactions.

http://msdn.microsoft.com/en-us/library/ms971557.aspx

Answer 2

If you are want ALL steps to be committed or NONE then wouldn't one transaction be more appropriate? You can pass an existing transaction object to the constructor of an ADO.Net Command object and thus perform multiple updates within the scope of a single transaction.

Answer 3

You could do that in TSQL via save points (SAVE TRAN on SQL Server), but frankly I don't recommend it. You can't do it via TransactionScope, as any abort is terminal (the entire transaction is rolled back as soon as any transaction in the tree indicates failure).

Personally: check the data first, and only perform valid actions. If it fails, that is terminal - roll it back. Possibly separate the work into atomic units that can be truly committed (or rolled back) in isolation.

Answer 4

On Oracle:

BEGIN    
  SAVEPOINT STEP1;
  -- do some things
  IF your_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP1;
  END IF;

  SAVEPOINT STEP2;
  -- do some other things
  IF your_other_criteria_for_commit_is_needed THEN
    NULL; -- do nothing
  ELSE
    ROLLBACK TO SAVEPOINT STEP2;
  END IF;

  -- SOME NUMBER OF OTHER STEPS

  IF your_criteria_for_all_step_commit_is_needed THEN
    COMMIT; -- commit all changes to DB
  ELSE
    ROLLBACK; -- rollback all changes
  END IF;
END;
/