16.1 Manual Transactions

Manual transactions use explicit statements to control the boundaries of a transaction. Transactions are started, and subsequently either committed or rolled back. The SQL .NET data provider allows savepoints to be defined that allow a transaction to be partially rolled back. The OLE DB .NET data provider allows new, or nested, transactions to be started within the boundaries of the parent transaction. If transactions are nested, the parent can't commit until all nested transactions have committed.

A Transaction is started by calling the BeginTransaction( ) method of a Connection object. You can set a Command object to run in a transaction by setting its Transaction property to a Transaction object connected to the same Connection as the Command object. An overloaded constructor for the Command object allows this to be done in a single statement.

Once running in a Transaction, commands can be executed on the Command object within a try/catch block. If an exception is raised, the Rollback( ) method can be called on the Transaction to roll back all changes; otherwise, the Commit( ) method persists the changes.

The following example demonstrates these concepts. Order and order detail records are inserted within a transaction, thereby ensuring that either both or neither record is added:

String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();

//create command and enlist in transaction
SqlCommand cmdOrder = new SqlCommand("InsertOrder", conn, tran);
cmdOrder.CommandType = CommandType.StoredProcedure;
SqlCommand cmdOrderDetail = new SqlCommand("InsertOrderDetail",
    conn, tran);
cmdOrderDetail.CommandType = CommandType.StoredProcedure;

SqlParameterCollection orderParams = cmdOrder.Parameters;
orderParams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
orderParams["@OrderID"].Direction = ParameterDirection.InputOutput;
orderParams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
// ... code to define remaining parameters

SqlParameterCollection orderDetailParams = cmdOrderDetail.Parameters;
orderDetailParams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
orderDetailParams.Add("@ProductID", SqlDbType.Int, 5, "ProductID");
// ... code to define remaining parameters

String result = "";
try
{
    // insert order
    cmdOrder.Parameters["@OrderID"].Value = -1;
    cmdOrder.Parameters["@CustomerID"].Value = "ALFKI";
    // ... set the other parameters
    cmdOrder.ExecuteNonQuery();

    // insert order detail with OrderID from the inserted order
    cmdOrderDetail.Parameters["@CustomerID"].Value =
        (Int32)cmdOrder.Parameters["@OrderID"].Value;
    cmdOrderDetail.Parameters["@ProductID"].Value = 20;
    //... set the other parameters
    cmdOrderDetail.ExecuteNonQuery();

    //if okay to here, commit the transaction
    tran.Commit();
    result = "Transaction commit.";
}
catch (SqlException ex)
{
    tran.Rollback();
    result = "ERROR: " + ex.Message + "; Transaction rollback.";
}
catch (FormatException ex)
{
    tran.Rollback();
    result = "ERROR: " + ex.Message + "; Transaction rollback.";
}
finally
{
    conn.Close();
}