TransactionScope and Enterprise Library 3.0 Data Access Application Block - System.Transactions
by David Hayden ( Microsoft MVP C# ), Filed: Enterprise Library 3.0
One of the cool new features added in the Enterprise Library 3.0 Data Access Application Block is support for System.Transactions and the TransactionScope Class.
Now when you write code as shown below, the Data Access Application Block detects that a transaction is happening and uses the same connection for all commands as opposed to open and closing several commands. This means that we avoid a Distributed Transaction since only one connection is used within the scope of the transaction:
Database database = DatabaseFactory.CreateDatabase();
string insertCustomerSql = "INSERT INTO Customers
(Name, EmailAddress) VALUES (@Name,@EmailAddress)
SELECT @CustomerId = SCOPE_IDENTITY()";
string insertOrderSql = "INSERT INTO Orders (CustomerId,
Number, OrderDate) VALUES (@CustomerId, @Number,
@OrderDate)";
using (TransactionScope scope =
new TransactionScope(TransactionScopeOption.RequiresNew))
{
DbCommand insertCustomerCommand =
database.GetSqlStringCommand(insertCustomerSql);
database.AddInParameter(insertCustomerCommand,
"Name", DbType.String, "Bill Doe");
database.AddInParameter(insertCustomerCommand,
"EmailAddress", DbType.String, "bill@doe.com");
database.AddOutParameter(insertCustomerCommand,
"CustomerId", DbType.Int32, 4);
database.ExecuteNonQuery(insertCustomerCommand);
int customerId = (int)database.GetParameterValue
(insertCustomerCommand, "CustomerId");
DbCommand insertOrderCommand =
database.GetSqlStringCommand(insertOrderSql);
database.AddInParameter(insertOrderCommand,
"CustomerId", DbType.Int32, customerId);
database.AddInParameter(insertOrderCommand,
"Number", DbType.String, "12345");
database.AddInParameter(insertOrderCommand, "OrderDate",
DbType.DateTime, System.DateTime.Now);
database.ExecuteNonQuery(insertOrderCommand);
DbCommand insertOrderCommandAgain =
database.GetSqlStringCommand(insertOrderSql);
database.AddInParameter(insertOrderCommandAgain,
"CustomerId", DbType.Int32, customerId);
database.AddInParameter(insertOrderCommandAgain,
"Number", DbType.String, "23456");
database.AddInParameter(insertOrderCommandAgain,
"OrderDate", DbType.DateTime, System.DateTime.Now);
database.ExecuteNonQuery(insertOrderCommandAgain);
scope.Complete();
}
In Enterprise Library 2.0 DAAB there is no detection of a current transaction so the DAAB opens and closes several connections causing a Distributed Transaction as shown in the following image. Enterprise Library 3.0 has eliminated that problem:
Conclusion
Enterprise Library 3.0 has a number of great features including the cool new Validation Application Block.
by David Hayden ( Microsoft MVP C# )
Filed: Enterprise Library 3.0