Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback - Using Statement - IDisposable

Here is the basic template for an ADO.NET 2.0 Transaction: using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = connection.CreateCommand(); SqlTransaction transaction = null; try { // BeginTransaction() Requires Open Connection connection.Open(); transaction = connection.BeginTransaction(); // Assign Transaction to Command command.Transaction = transaction; // Execute 1st Command command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute 2nd Command command.CommandText = "Update..."; command.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { connection.Close(); } } A c# using statement wraps up the connection, because SqlConnection implements IDisposable. The using statement makes sure that Dispose() gets called on the connection object so it can free up any unmanaged resources. Before you can begin a transaction, you must first open the connection. You begin your transaction and then assign any newly created command objects to that transaction and perform queries as necessary. Commit the transaction. If an error occurs, Rollback the transaction in a catch statement to void out any changes and then rethrow the error so that the application can deal with it accordingly. The connection is properly closed in the finally statement, which gets called no matter what, and any unmanaged resources are disposed when the using statement calls Dispose() on the connection. Pretty simple solution to a fairly advanced topic. The above template could actually implement a second c# using statement around command, because SqlCommand also implements IDisposable. I don't know that it is really necessary, however. More theoretical than probably anything. I just like to see using statements around anything that implements IDisposable: using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = connection.CreateCommand()) { SqlTransaction transaction = null; try { // BeginTransaction() Requires Open Connection connection.Open(); transaction = connection.BeginTransaction(); // Assign Transaction to Command command.Transaction = transaction; // Execute 1st Command command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute 2nd Command command.CommandText = "Update..."; command.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { connection.Close(); } } }