ADO.NET and working with different db

I need to implement reading data from one db and insert/update data in two other databases (Db2 and Db3). All databases are SQL Server.

Now I'm using the next scenario:

• Open connection and start transaction for my source db.

  1.1 Open connection and start transaction for Db2

  1.2 Insert/Update records.

  1.3 Commit/Rollback transaction

  2.1 Open connection and start transaction for Db3

  2.2 Insert/Up开发者_运维技巧date records.

  2.3 Commit/Rollback transaction

• Mark record as exported in source db.

• Commit transaction.

Everything is working correct. But if I have exception in 1.2 or 2.2 steps, the current transaction will be rolled back, but transaction from other step will stay committed.

How I can implement scenario when if steps (1.2 or 2.2) have exception, steps 1.3 and 2.3 will be rolled back, too?

I can't use one transaction for Db2 and Db3 because I have exception

The transaction is either not associated with the current connection or has been completed.

Any ideas?

Thanks,

UPDATE: I solved my problem: Now I'm using the next scenario:

• Open connection and start transaction for my source db.

  1.1 Open connection and start transaction for Db1

  1.2 Open connection and start transaction for Db2

  1.3 Insert/Update in DB1

  1.4 Insert/Update in DB2

  1.5 Commit/Rollback transaction for Db1

  1.6 Commit/Rollback transaction for Db2

• Mark record as exported in source db.

• Commit transaction.

---

You can use the TransactionScope class to interact with multiple databases inside a single distributed transaction.

using (TransactionScope scope = new TransactionScope())
{
  //put all logic here
  scope.Complete();
}

Here's another example