Performing a Transaction Using ADO.NET

You can begin, commit and roll back a transaction using the Connection and Transaction objects. The following steps are used to perform a transaction.

To perform a transaction

1. Call the BeginTransaction method of the Connection object to mark the start of the transaction. The BeginTransaction method returns a reference to the Transaction. This reference is assigned to the Command objects that are enlisted in the transaction.
2. Assign the Transaction object to the Transaction property of the Command to be executed. If a Command is executed on a Connection with an active Transaction, and the Transaction object has not been assigned to the Transaction property of the Command, an exception is thrown.
3. Execute the required commands.
4. Call the Commit method of the Transaction object to complete the transaction, or call the Rollback method to cancel the transaction.

The following code example demonstrates transactional logic using ADO.NET with Microsoft® SQL Server™.

Dim myConnection As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")
myConnection.Open()

' Start a local transaction.
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()

' Enlist the command in the current transaction.
Dim myCommand As SqlCommand = myConnection.CreateCommand()
myCommand.Transaction = myTrans

Try
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
  myCommand.ExecuteNonQuery()
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
  myCommand.ExecuteNonQuery()
  myTrans.Commit()
  Console.WriteLine("Both records are written to database.")
Catch e As Exception
  Try
    myTrans.Rollback()
  Catch ex As SqlException
    If Not myTrans.Connection Is Nothing Then
      Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                        " was encountered while attempting to roll back the transaction.")
    End If
  End Try

  Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                    "was encountered while inserting the data.")
  Console.WriteLine("Neither record was written to database.")
Finally
  myConnection.Close()
End Try
[C#]SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;

try
{
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
  myCommand.ExecuteNonQuery();
  myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
  myCommand.ExecuteNonQuery();
  myTrans.Commit();
  Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
  try
  {
    myTrans.Rollback();
  }
  catch (SqlException ex)
  {
    if (myTrans.Connection != null)
    {
      Console.WriteLine("An exception of type " + ex.GetType() +
                        " was encountered while attempting to roll back the transaction.");
    }
  }

  Console.WriteLine("An exception of type " + e.GetType() +
                    "was encountered while inserting the data.");
  Console.WriteLine("Neither record was written to database.");
}
finally
{
  myConnection.Close();
}

See Also

Using .NET Framework Data Providers to Access Data | OleDbConnection Class | OleDbTransaction Class | SqlConnection Class | SqlTransaction Class | OdbcConnection Class | OdbcTransaction Class