Enlisting in a Distributed Transaction

Article
10/17/2014

The Connection object will automatically enlist in an existing distributed transaction if it determines that a transaction is active. Automatic transaction enlistment occurs when the connection is opened or retrieved from the connection pool. You can disable auto-enlistment in existing transactions by specifying Enlist=false as a connection string parameter for a SqlConnection, or OLE DB Services=-7 as a connection string parameter for an OleDbConnection.

If auto-enlistment is disabled, you can enlist in an existing distributed transaction using the EnlistDistributedTransaction method of the Connection object. Enlisting in an existing distributed transaction ensures that, if the transaction is committed or rolled back, modifications made by the code at the data source will be committed or rolled back as well.

EnlistDistributedTransaction is particularly applicable when pooling business objects. If a business object is pooled with an open connection, automatic transaction enlistment only occurs when that connection is opened or pulled from the connection pool. If multiple transactions are performed using the pooled business object, the open connection for that object will not automatically enlist in newly initiated transactions. In this case, you can disable automatic transaction enlistment for the Connection and enlist the Connection in transactions using EnlistDistributedTransaction.

EnlistDistributedTransaction takes a single argument of type ITransaction that is a reference to the existing transaction. After calling Connection.EnlistDistributedTransaction, all modifications made at the data source using the Connection are included in the transaction.

Note The Connection must be open before calling EnlistDistributedTransaction.

CAUTION EnlistDistributedTransaction returns an Exception if the Connection has already begun a transaction using BeginTransaction. However, if the transaction is a local transaction started at the data source (for example, executing the BEGIN TRANSACTION statement explicitly using a SqlCommand), EnlistDistributedTransaction will roll back the local transaction and enlist in the existing distributed transaction as requested. You will not receive notice that the local transaction was rolled back, and are responsible to manage any local transactions not started using BeginTransaction.

The following example shows an ASP.NET page that requires a transaction. ADO.NET code, within the page, enlists in the transaction.

<%@Page Transaction="Required" %> 
<%@Import Namespace="System.Data"%>
<%@Import Namespace="System.Data.SqlClient"%>
<%@Import Namespace="System.EnterpriseServices"%>

<html>
<Script Runat="SERVER" Language="VB">

Sub Page_Load()
  Dim ns As NorthwindSample = New NorthwindSample()

  Dim customerId As String = "CUST1"
  Dim companyName As String = "New Company Name"

  Try
    ns.AddCustomer(customerId, companyName, CType(ContextUtil.Transaction, ITransaction))
    ContextUtil.SetComplete()
    Response.Write("Customer " & Server.HtmlEncode(customerId) & " added.")
  Catch e As Exception
    ContextUtil.SetAbort()
    Response.Write("Customer " & Server.HtmlEncode(customerId) & " not added. An exception of type " & _
                   e.GetType().ToString() & " was encountered.")
  End Try
End Sub


Public Class NorthwindSample

  Public Sub AddCustomer(customerId As String, companyName As String, trans As ITransaction)
  
    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
                                                      "Initial Catalog=Northwind;Enlist=false;")

    Dim cmd As SqlCommand = New SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " & _
                                           "Values(@CustomerId, @CompanyName)", nwindConn)
    cmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = customerId
    cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40).Value = companyName

    nwindConn.Open()

    If Not trans Is Nothing Then
      nwindConn.EnlistDistributedTransaction(trans)
    End If

    Try
      cmd.ExecuteNonQuery()
    Catch e As SqlException
      Throw e
    finally
      nwindConn.Close()
    End Try
  End Sub

End Class
</Script>
</html>
[C#]
<%@Page Transaction="Required" %> 
<%@Import Namespace="System.Data"%>
<%@Import Namespace="System.Data.SqlClient"%>
<%@Import Namespace="System.EnterpriseServices"%>

<html>
<Script Runat="SERVER" Language="C#">

void Page_Load()
{
  NorthwindSample ns = new NorthwindSample();

  string customerId = "CUST1";
  string companyName = "New Company Name";

  try
  {
    ns.AddCustomer(customerId, companyName, (ITransaction)ContextUtil.Transaction);
    ContextUtil.SetComplete();
    Response.Write("Customer " + Server.HtmlEncode(customerId) + " added.");
  }
  catch (Exception e)
  {
    ContextUtil.SetAbort();
    Response.Write("Customer " + Server.HtmlEncode(customerId) + " not added. An exception of type " + e.GetType() + " was encountered.");
  }
}


public class NorthwindSample
{
  public void AddCustomer(string customerId, string companyName, ITransaction trans)
  {
    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;" +
                                                "Initial Catalog=Northwind;Enlist=false;");

    SqlCommand cmd = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
                                    "Values(@CustomerId, @CompanyName)", nwindConn);
    cmd.Parameters.Add("@CustomerId", SqlDbType.VarChar, 5).Value = customerId;
    cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40).Value = companyName;

    nwindConn.Open();

    if (trans != null)
      nwindConn.EnlistDistributedTransaction(trans);

    try
    {
      cmd.ExecuteNonQuery();
    }
    catch (SqlException e)
    {
      throw(e);
    }
    finally
    {
      nwindConn.Close();
    }
  }
}
</Script>
</html>

Enlisting in a Distributed Transaction

See Also

Additional resources