Managing Database Transactions with the TransactionScope
- In the past...
- Employing the using Statement
- Using the TransactionScope
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Software can be revolutionary or evolutionary. Revolutionary software is something that changes the way everyone works dramatically.
Turbo Pascal’s IDE for DOS was revolutionary. Visual Basic for Windows was revolutionary. Evolutionary software is the stuff that happens between revolutions. The TransactionScope class in System.Transactions is evolutionary.
The TransactionScope class is an easier way to enlist objects that support transactions in a transaction at the block scope level. COM+ objects are often designed to support transactions. SQL Server supports transactions.
Since database programming is something most of us do a lot of, I will focus on DB transactions.
In the past...
Don’t laugh when I say that some programmers routinely use transactions. Many programmers still don’t. But transactions should be used any time one is making more than a single change to a database and those changes have to be treated as atomic.
This notion has to with the database acronym ACID (for Atomicity, Consistency, Isolation, and Durability).
Specifically, transactions support Atomicity and Consistency. Atomicity means that modifications support an all or nothing rule—either all changes take affect or none do. Consistency means that only valid data will be written, and anything that violates database rules is not permitted to remain in the database. If data gets in the database, the C in ACID means that the database went from one consistent state to another consistent state.
To date, we can support transactions with any of the transaction classes in ADO.NET, such as SqlTransaction. Since ADO.NET 2.0 we can simplify transactions and eliminate the need to pass transaction objects around by using the TransactionScope class.
Listing 1 shows a legacy example demonstrating how to use the SqlTransaction class.
Listing 1 Using SqlTransaction—or transactions the old way
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)
{
UseTransaction();
}
public static void UseTransaction()
{
// using ensures Dispose is called. Dispose in SqlConnection
// closes connection
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
int orderId = InsertOrder(connection, transaction);
Console.WriteLine(orderId);
InsertOrderDetails(orderId, connection, transaction);
transaction.Commit();
Console.WriteLine("Transaction committed");
Console.ReadLine();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
transaction.Rollback();
}
}
}
}
// 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);
}
}
}
Elements such as INSERT statements, as demonstrated in the InsertOrder and InsertOrderDetails methods, are just helper methods. We are mostly interested in the code in UseTransaction.