Cannot access SqlTransaction object to rollback in catch block

Question

I've got a problem, and all articles or examples I found seem to not care about it.

I want to do some database actions in a transaction. What I want to do is very similar to most examples:

using (SqlConnection Conn = new SqlConnection(_ConnectionString)) {     try     {         Conn.Open();         SqlTransaction Trans = Conn.BeginTransaction();          using (SqlCommand Com = new SqlCommand(ComText, Conn))         {             /* DB work */         }     }     catch (Exception Ex)     {         Trans.Rollback();         return -1;     } } 

But the problem is that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block, but I think that's a bit risky, since both can throw multiple exceptions.

Am I wrong, or do most people just ignore this risk? What's the best solution to be able to rollback, if an exception happens?

Answer 1

using (var Conn = new SqlConnection(_ConnectionString)) {     SqlTransaction trans = null;     try     {         Conn.Open();         trans = Conn.BeginTransaction();          using (SqlCommand Com = new SqlCommand(ComText, Conn, trans))         {             /* DB work */         }         trans.Commit();     }     catch (Exception Ex)     {         if (trans != null) trans.Rollback();         return -1;     } } 

or you could go even cleaner and easier and use this:

using (var Conn = new SqlConnection(_ConnectionString)) {     try     {         Conn.Open();         using (var ts = new System.Transactions.TransactionScope())         {             using (SqlCommand Com = new SqlCommand(ComText, Conn))             {                 /* DB work */             }             ts.Complete();         }     }     catch (Exception Ex)     {              return -1;     } } 

Answer 2

I don't like typing types and setting variables to null, so:

try {     using (var conn = new SqlConnection(/* connection string or whatever */))     {         conn.Open();          using (var trans = conn.BeginTransaction())         {             try             {                 using (var cmd = conn.CreateCommand())                 {                     cmd.Transaction = trans;                     /* setup command type, text */                     /* execute command */                 }                  trans.Commit();             }             catch (Exception ex)             {                 trans.Rollback();                 /* log exception and the fact that rollback succeeded */             }         }     } } catch (Exception ex) {     /* log or whatever */ } 

And if you wanted to switch to MySql or another provider, you'd only have to modify 1 line.