The .NET Framework 2.0 includes the System.Transactions
namespace, which provides support for distributed transactions and defines the
TransactionScope class, which enables us to create and manage distributed
transactions. We can implement transactions for multiple database connections
using TransactionScope.
Listing 6
using(TransactionScope tranScope = new TransactionScope())
{
string cString =
WebConfigurationManager.ConnectionString[testDB].ConnectionString;
string cString1 =
WebConfigurationManager.ConnectionString[testDB1].ConnectionString;
using(SqlConnection con = new SqlConnection(cString))
{
SqlCommand cmd = codesDatabaseConnection.CreateCommand();
cmd.CommandText =
"Insert Into Emp (Emp_Code, Emp_Name) values (1,'Employee1')";
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
using(SqlConnection con1 = new SqlConnection(cString1))
{
SqlCommand cmd1 = con1.CreateCommand();
cmd1.CommandText = "Insert into Emp_Detailas(Emp_ID,Sal) values (1, 8000)";
con1.Open();
cmd1.ExecuteNonQuery();
con1.Close();
}
tranScope.Complete();
}
If all the update operations succeed in a transaction scope,
we should call the complete method on the TransactionScope object to indicate
that the transaction completed successfully and, thus, the transaction manager
commits the transaction.