Starting a transaction and manipulating data

You use the BeginTransaction method of the AdoTransaction object’s Connection property to start a transaction. The Connection property inherits its methods from the IDbConnection interface. Similarly, you use the Commit and Rollback methods of the AdoTransaction object’s Transaction property. The Transaction property inherits its methods from the IDbTransaction interface.

In the following example, data is retrieved into the DataStore before the transaction is started within a try-catch statement. Within the same try clause, changes in the data are updated to the database and committed and the status flags are reset. If the update fails, a DbErrorException is caught and the transaction is rolled back.


[Visual Basic]
// In form's load method
ds.Retrieve()
...
// In an Update button
Try
   adoTrans.Transaction = _
      adoTrans.Connection.BeginTransaction()
   ds.UpdateData(true, false)
   adoTrans.Transaction.Commit()
   ds.ResetUpdateStatus()

Catch ex As DbErrorException
   MessageBox.Show(ex.SqlErrorText + _
   "\n\nNo changes have been made to the database.", _
   "Database error", MessageBoxButtons.OK, _
   MessageBoxIcon.Stop);
   adoTrans.Transaction.Rollback();

Catch ex As Exception
   MessageBox.Show(ex.ToString() + _
   "\n\nNo changes have been made to the database.", _
   "Unexpected Exception", MessageBoxButtons.OK, _
   MessageBoxIcon.Stop);
End Try

[C#]
// In form’s load method
ds.Retrieve();
...
// In an Update button
try
{
   adoTrans.Transaction =
      adoTrans.Connection.BeginTransaction();
   ds.UpdateData(true, false);
   adoTrans.Transaction.Commit();
   ds.ResetUpdateStatus();
}
catch (DbErrorException ex) 
{
   MessageBox.Show(ex.SqlErrorText + 
   "\n\nNo changes have been made to the database.",
   "Database error", MessageBoxButtons.OK,
   MessageBoxIcon.Stop);
   adoTrans.Transaction.Rollback();
}
catch (Exception ex)
{ 
   MessageBox.Show(ex.ToString() + 
   "\n\nNo changes have been made to the database.",
   "Unexpected Exception", MessageBoxButtons.OK,
   MessageBoxIcon.Stop);
}