Transaction best practice question

16 views
Skip to first unread message

Kyle Green

unread,
May 26, 2021, 1:23:48 PM5/26/21
to firebird-net-provider
Examples seem to show different ways and different orders of doing things re: transactions. I'm hoping to do things the proper way, any comments of this code snippet would be appreciated :)

//pseudocode:
string sConnString = "myconnectionstring";
FbTransaction objTrans = null;    
FbConnection objConn = new FbConnection(sConnString);

public bool DoSomeDatabaseStuff(objTrans, objConn, ref string sErrMsg)
  {    
  objConn.Open();    
  objTrans = objConn.BeginTransaction();
    
  FbCommand objCmd1 = new FbCommand("insert query one", objConn);    
  FbCommand objCmd2 = new FbCommand("insert query two", objConn);   
 
  //SOME MICROSOFT NOTES say I have to do this:
  //objCmd1.Transaction = obj.Trans;
  //objCmd2.Transaction = obj.Trans;

  try    
    {   
    //ANY OF THESE THREE could throw an exception, correct? 
    //-should I arrange separate try/catch for each?

    objCmd1.ExecuteNonQuery();    
    objCmd2.ExecuteNonQuery();
    objTrans.Commit();    
    }    
  catch (Exception)    
    {    
    //DO I NEED A TRY/CATCH here for rollback? connection might be closed?
    //-what could I do about it?

    objTrans.Rollback();    
    }    
  finally    
    {    
    objConn.Close();    
    }    
  }

Gerdus van Zyl

unread,
May 26, 2021, 2:00:03 PM5/26/21
to firebird-n...@googlegroups.com
Try to make use of using statements for everything (connection,command,transaction,etc) to simplify lifetime and disposal management.
General rule is for connections,transactions to have a short as possible lifetime.
separate try/catch for each ExecuteQuery is not necessary.

Explicitly setting the transaction is a good idea I think.

I always wrap the rollback in the generic exception case in a try/catch and ignore the catch, since there are a lot of causes of the generic exception case.
Also add an catch( FbException fbex) since those errors are worth logging/handling.

--
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/0dd62d53-0e7e-41b5-9140-935a6e3a71a5n%40googlegroups.com.

Kyle Green

unread,
May 26, 2021, 10:58:34 PM5/26/21
to firebird-net-provider
Thanks, I'll check out the FbException. What about assigning the transactions to the command objects, as per Microsoft notes- is that necessary?
Reply all
Reply to author
Forward
0 new messages