You are creating a Windows Forms application by using the .NET Framework 3.5. You write
the following code segment to update multiple databases on a SQL Server 2008 database.
(Line numbers are included for reference only.)
01 string connectionStringCustomer = @”Data Source=CUSTOMER;Integrated Security=
SSPI;”;
02 string connectionStringOrders = @”Data Source=ORDER ;Integrated Security= SSPI;”;
03 SqlCommand cmdCustomer = new SqlCommand();
04 SqlCommand cmdOrders = new SqlCommand();
05 SqlConnection cnnCustomer =
new SqlConnection(connectionStringCustomer);
06 SqlConnection cnnOrders = new SqlConnection(connectionStringOrders);
07
You need to ensure that all database updates are included in a single distributed transaction.
Which code fragment should you add on Line 07?
A.
cnnCustomer.Open();
cnnOrders.Open();
… cmdOrders.ExecuteNonQuery();
… cmdCustomer.ExecuteNonQuery();
cnnOrders.Close(); cnnCustomer.Close();
B.
TransactionScope scope = new TransactionScope();
cnnCustomer.Open();
cnnOrders.Open();
… cmdOrders.ExecuteNonQuery();
… cmdCustomer.ExecuteNonQuery();
cnnOrders.Close(); cnnCustomer.Close();
scope.Complete();
C.
TransactionScope customerScope = new TransactionScope() {
using (SqlConnection cnnCustomer = new SqlConnection (connectionStringCustomer)) { }
customerScope.Complete();
}
using (TransactionScope ordersScope = new TransactionScope()) {
using (SqlConnection cnnOrders = new SqlConnection(connectionStringOrders)) { }
ordersScope.Complete(); }
D.
try {
cmdOrders.Transaction = cnnOrders.BeginTransaction();
… cmdOrders.ExecuteNonQuery();
…
cmdCustomer.Transaction = cnnCustomer.BeginTransaction();
… cmdCustomer.ExecuteNonQuery();
cmdCustomer.Transaction.Commit();
cmdOrders.Transaction.Commit();
}catch { cmdCustomer.Transaction.Rollback();
cmdOrders.Transaction.Rollback();
}
Explanation:
To support distributed transactions, you must use the TransactionScope class rather than using the Transaction capabilities
built into the SqlConnection object. In addition, since this must occur as a single distributed transaction rather
than multiple distributed transactions, only a single instance of TransactionScope must be created.