How to roll back a transaction using dapper

Question

I have it:

using (var con= new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString)) { try { // many transactions } catch (Exception e) { con.BeginTransaction().Rollback(); } } 

Will it be my job. I know that another method is to make a transaction, then open it, then rollback.

Answer 1

You can use the TransactionScope variable in the use block at the same level as the SqlConnection use block

 using (TransactionScope scope = new TransactionScope()) using (var con= new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString)) { try { // many transactions scope.Complete(); } catch (Exception e) { // Not needed any rollback, if you don't call Complete // a rollback is automatic exiting from the using block // con.BeginTransaction().Rollback(); } } 

Answer 2

In my understanding, if you plan to work with one connection and need a transaction to ensure the completion or rollback of DML, go to a specific connection transaction that will have less weight than external System.Transaction transactions, which are more universal and help coordinate with MSDTC, if necessary, since you can open multiple connections of various types.

 using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString)) { using(var txn = con.BeginTransaction()) { try { txn.Commit(); } catch (Exception e) { txn.Rollback(); } } }