Traditional Transactions

Before System.Transaction was released you could create transactions directly with ADO.NET, or you could do transactions with the help of components, attributes, and the COM+ runtime, which is covered in the namespace System.EnterpriseServices. So, you can see how the new transaction model compares to the traditional ways working with transactions, a short look will be presented on how ADO.NET transactions and transactions with Enterprise Services are done.

ADO.NET Transactions

Let’s start with traditional ADO.NET transactions. If you don’t create transactions manually, there’s a single transaction with every SQL statement. If multiple statements should participate with the same transaction, you have to create a transaction manually.

The following code segment shows how to work with ADO.NET transactions. The SqlConnection class defines the method BeginTransaction(), which returns an object of type SqlTransaction. This transaction object must then be associated with every command that participates with the transaction. To associate a command with a transaction, set the Transaction property of the SqlCommand class to the SqlTransaction instance. For the transaction to be successful, you must invoke the Commit() method of the SqlTransaction object. If there’s an error, you have to invoke the Rollback() method and every change is undone. You can check for an error with the help of a try/catch and do the rollback inside the catch.

  using System; using System.Data.SqlClient; using System.Diagnostics; namespace Wrox.ProCSharp.Transactions {    public class CourseData    {       public void AddCourse(Course c)       {          SqlConnection connection = new SqlConnection(                Properties.Settings.Default.CourseManagementConnectionString);          SqlCommand courseCommand = connection.CreateCommand();          courseCommand.CommandText =                "INSERT INTO Courses (Number, Title) VALUES (@Number, @Title)";          connection.Open();          SqlTransaction tx = connection.BeginTransaction();            try          {             courseCommand.Transaction = tx;                        courseCommand.Parameters.AddWithValue("@Number", c.Number);             courseCommand.Parameters.AddWithValue("@Title", c.Title);             courseCommand.ExecuteNonQuery();                tx.Commit();          }          catch (Exception ex)          {             Trace.WriteLine("Error: " + ex.Message);             tx.Rollback();          }          finally          {             connection.Close();          }       }    } } 

If you have multiple commands that should run in the same transaction, every command must be associated with the transaction. As the transaction is associated with a connection, every one of these commands must also be associated with the same connection instance. ADO.NET transactions do not support transactions across multiple connections; it is always a local transaction associated with one connection.

If you create an object persistence model using multiple objects, for example classes Course and CourseDate, which should be persisted inside one transaction, it gets very difficult using ADO.NET transactions. Here, it is necessary to pass the transaction to all the objects participating in the same transaction.

Important 

ADO.NET transactions are not distributed transactions. In ADO.NET transactions, it is difficult to have multiple objects working on the same transaction.

System.EnterpriseServices

With Enterprise Services you get a lot of services for free. One of them is automatic transactions. Using transactions with System.EnterpriseServices has the advantage that it’s not necessary to deal with transactions explicitly; transactions are automatically created by the runtime. You just have to add the attribute [Transaction] with the transactional requirements to the class. The [AutoComplete] attribute marks the method to automatically set the status bit for the transaction: if the method succeeds, the success bit is set, so the transaction can commit. If an exception happens, the transaction is aborted.

  using System; using System.Data.SqlClient; using System.EnterpriseServices; using System.Diagnostics; namespace Wrox.ProCSharp.Transactions {    [Transaction(TransactionOption.Required)]    public class CourseData : ServicedComponent    {       [AutoComplete]       public void AddCourse(Course c)       {          SqlConnection connection = new SqlConnection(                Properties.Settings.Default.CourseManagementConnectionString);          SqlCommand courseCommand = connection.CreateCommand();          courseCommand.CommandText =                "INSERT INTO Courses (Number, Title) VALUES (@Number, @Title)";          connection.Open();          try          {             courseCommand.Parameters.AddWithValue("@Number", c.Number);             courseCommand.Parameters.AddWithValue("@Title", c.Title);             courseCommand.ExecuteNonQuery();          }          finally          {             connection.Close();          }       }    } } 

A big advantage of creating transactions with System.EnterpriseServices is that multiple objects can easily run within the same transaction, and transactions are automatically enlisted. The disadvantages are that it requires the COM+ hosting model and the class using the features of this technology must be derived from the base class ServicedComponent.

Tip 

Enterprise Services and using COM+ transactional services is covered in Chapter 38, “Enterprise Services.”