How To Use Transaction Inside Using{} Block

At present while doing any database related code i use the following structure: try { using(FBConnection con = new FBConnection('connectionstringhere')) { con.Open();

Solution 1:

You don't need a Transaction if you are just reading records (ExecuteReader) however this could be an approach using the TransactionScope class

try
{
   using(TransactionScope scope = new TransactionScope())
   using(FBConnection con = new FBConnection('connectionstringhere'))
   {
     con.Open();
     ...
     scope.Complete();
   }
}
catch(FBException ex)
{
   // No rollback needed in case of exceptions. // Exiting from the using statement without Scope.Complete// will cause the rollback
  MessageBox.Show(ex.Message);
}

The standard approach could be written as

FBTransactiontransaction=null;
FBConnectioncon=null;
try
{
   con = newFBConnection('connectionstringhere');
   con.Open();
   transaction = con.BeginTransaction();
   ...
   transaction.Commit();
}
catch(FBException ex)
{
    MessageBox.Show(ex.Message);
    if(transaction!=null) transaction.Rollback();
}
finally
{
    if(transaction != null) transaction.Dispose();
    if(con != null) con.Dispose();
}

Not sure about the behavior or the FBConnection object in case of exceptions so better going on a traditional finally block in which you dispose the transaction and the connection in the correct order