- In the past...
- Employing the using Statement
- Using the TransactionScope
- Summary
Using the TransactionScope
Using the TransactionScope is easy. Without further ado, follow these basic steps:
- Add a reference to System.Transactions.dll.
- Add a using System.Transactions; statement to the top of the module using the TransactionScope class.
- Make sure that the Microsoft Distributed Transaction Coordinator is running. Use net start msdtc at a command prompt.
- Create the TransactionScope object in a using statement.
- Complete all updates within the scope defined by the {} of the TransactionScope’s using statement.
- Before the using block exist call TransactionScope.Complete.
Listing 2 is a revision of Listing 1. As you will see it is simpler, requires fewer lines of code, and we do not need to pass a transaction object to the worker methods InsertOrder and InsertOrderDetails. (The argument is present in the sample to support the transaction-style used in Listing 1.)
Significantly revised code is shown in bold in Listing 2.
Listing 2 Use TransactionScope to simplify using transactions, making your data access code simpler to write and simpler to enlist in transactions
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Transactions; namespace TranScopeDemo { class Program { private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"; static void Main(string[] args) { UseTransactionScope(); } public static void UseTransactionScope() { // add reference to System.Transactions.dll // add using System.Transactions // net start msdtc using(TransactionScope scope = new TransactionScope()) { using(SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); int orderId = InsertOrder(connection, null); Console.WriteLine(orderId); InsertOrderDetails(orderId, connection, null); scope.Complete(); Console.WriteLine("Transaction committed"); Console.ReadLine(); } } } // returns order id private static int InsertOrder(SqlConnection connection, SqlTransaction transaction) { const string sql = "INSERT INTO Orders(CustomerID,EmployeeID,OrderDate," + "RequiredDate,ShippedDate,ShipVia,Freight,ShipName," + "ShipAddress,ShipCity,ShipRegion,ShipPostalCode," + "ShipCountry) VALUES (@CustomerID,@EmployeeID,@OrderDate," + "@RequiredDate,@ShippedDate,@ShipVia,@Freight,@ShipName," + "@ShipAddress,@ShipCity,@ShipRegion,@ShipPostalCode," + "@ShipCountry) SELECT @OrderID = @@IDENTITY"; SqlCommand command = new SqlCommand(sql, connection, transaction); SqlParameter orderid = new SqlParameter("@OrderID", SqlDbType.Int); orderid.Direction = ParameterDirection.Output; command.Parameters.Add(orderid); command.Parameters.AddWithValue("@CustomerID", "VINET"); command.Parameters.AddWithValue("@EmployeeID", 5); command.Parameters.AddWithValue("@OrderDate", DateTime.Now); command.Parameters.AddWithValue("@RequiredDate", DateTime.Now.AddDays(5)); command.Parameters.AddWithValue("@ShippedDate", DateTime.Now.AddDays(2)); command.Parameters.AddWithValue("@ShipVia", 3); command.Parameters.AddWithValue("@Freight", 32.3800); command.Parameters.AddWithValue("@ShipName", "Ed’s Edibles"); command.Parameters.AddWithValue("@ShipAddress", "Chevalier 59 rue de l’Abbaye"); command.Parameters.AddWithValue("@ShipCity", "Reims"); command.Parameters.AddWithValue("@ShipRegion", ""); command.Parameters.AddWithValue("@ShipPostalCode", "51100"); command.Parameters.AddWithValue("@ShipCountry", "France"); command.ExecuteNonQuery(); return (int)orderid.Value; } private static void InsertOrderDetails(int orderID, SqlConnection connection, SqlTransaction transaction) { const string sql = "INSERT INTO ’Order Details’ (" + "OrderID, ProductID, UnitPrice, Quantity, Discount)" + "VALUES( @OrderID, @ProductI, @UnitPrice, @Quantity, " + "@Discount )"; SqlCommand command = new SqlCommand(sql, connection, transaction); command.Parameters.AddWithValue("@OrderID", orderID); command.Parameters.AddWithValue("@ProductID", 11); command.Parameters.AddWithValue("@UnitPrice", 14.0); command.Parameters.AddWithValue("@Quantity", 12); command.Parameters.AddWithValue("@Discount", 0); } } }
You also have the option of initializing the TransactionScope class with one of the three TransactionScopeOption enumerated values: Required, RequiresNew, or Suppress.
Check the ms-help link ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref12/html/T_System_Transactions_TransactionScopeOption.htm for more information on the TransactionScopeOption.