ADO.NET and working with different db

Question

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/Update 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.

how do you handle when Db1 transaction succeed and Db2 transaction failure? — Eranga
– Eranga, Commented Aug 5, 2011 at 10:48

Eranga · Accepted Answer · 2011-08-05 06:54:54Z

1

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

answered Aug 5, 2011 at 6:54

Eranga

32.5k5 gold badges100 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eranga Over a year ago

@ukraine You can use System.EnterpriseServices here's an example. But TransactionScope approach is simple.

Collectives™ on Stack Overflow

ADO.NET and working with different db

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related