distributed transactions using SQLConnection

  • Thread starter Thread starter francois
  • Start date Start date
F

francois

I have a set of sql statements (insert, updates) that I want to encapsulate
in a single transaction to be able to rollback the all thing in any case an
exception occurs. If everything works fine then I will commit the all thing
all at once.

The only problem is that the different sql statements are using 2 different
DBs, hence 2 different SQL connections.

My question is how can i have different DB connections using the same
transaction? Maybe someone can forward me to a good resource if it is a
little bit too long to explain in here.

Thanks in advance,
Best regards,

Francois
 
Hi,

You could use "linked servers" capability of Sql server (see Sql server help
on this topic).
Or, you could manually control both transaction (create two tranasactions
and make sure that they both commit or rollback).
 
Hello,

Referencing System.EnterpriseServices.dll in your project and coding a class
inheriting from ServicedComponent would do the trick. Note that you have to
have COM+ (Component Services) installed to get this working and assign a
strong name to your assembly. Below is a sample code to make a distributed
transaction using two databases, both updates will get rolled back if an
error occurs. See "Component Services" topics in MSDN for more info.

[Transaction(TransactionOption.Required)]
public class TransactionalUpdater : ServicedComponent
{
[AutoComplete]
public void DoIt()
{
SqlConnection conn1 = new SqlConnection("connection string
to first database");
SqlConnection conn2 = new SqlConnection("connection string
to second database");
SqlCommand cmd1 = new SqlCommand("UPDATE ATable SET AField =
1", conn1);
SqlCommand cmd2 = new SqlCommand("UPDATE AnotherTable SET
AnotherField = 1", conn2);

conn1.Open();
conn2.Open();

try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
}
finally
{
cmd1.Dispose();
cmd2.Dispose();
conn1.Dispose();
conn2.Dispose();
}
}
}
 
Back
Top