c# - Cleanest way to use BeginTransaction using try catch

Question

Till now I am using two try catch blocks for my queries. The first one will throw an error if the connection is not be established. The second one checks if SqlCommand is executed successfully. Like the example below

try
{
  using(varconnection=newSqlConnection())
  using(varcmd=newSqlCommand())
  {
  
    connection.Open();
    var transaction=connection.BeginTransaction();
    cmd.Connection=connection;
    cmd.Transaction=transaction;

    try
    {
     cmd.CommandText="InsertintoCustomers(Name)values('Dimitri')";
     cmd.ExecuteNonQuery();

     cmd.CommandText="InsertintoCustomers(Name)values('George')";
     cmd.ExecuteNonQuery();

     transaction.Commit();
   }
   catch
   {
     try{transaction.Rollback();}catch{}
   }
  }
}
catch
{

}

I found a second Example that looks more clear for me.

SqlTransactiontransaction=null;
using(varconnection=newSqlConnection())
using(varcmd=newSqlCommand())
{
   try
   {
   connection.Open();
   transaction=connection.BeginTransaction();
   cmd.Connection=connection;
   cmd.Transaction=transaction;

   cmd.CommandText="InsertintoCustomers(Name)values('Dimitri')";
   cmd.ExecuteNonQuery();

   cmd.CommandText="InsertintoCustomers(Name)values('George')";
   cmd.ExecuteNonQuery();

   transaction.Commit();
   transaction.Dispose();
   transaction=null;
}
catch
{
   if(transaction!=null)
   {
      try{transaction.Rollback();}catch{}
   }
 }
}

Are both of them having the same result? Which of two is more preferable?

question from:https://stackoverflow.com/questions/65923313/cleanest-way-to-use-begintransaction-using-try-catch

Answer 1

Neither of those two methods is good. They are too verbose.

The best method is to just put the Transaction in a using as well, also we should use a parameter for the query:

using(var connection = new SqlConnection(connString))
using(var cmd = new SqlCommand("Insert into Customers (Name) values (@Name));"))
{
    var param = cmd.Parameters.Add("@Name", SqlDbType.VarChar, insert_column_length_here);
    connection.Open();
    using(var transaction = connection.BeginTransaction())
    {
        cmd.Transaction = transaction;
        param.Value = "Dimitri";
        cmd.ExecuteNonQuery();

        param.Value = "George";
        cmd.ExecuteNonQuery();

        transaction.Commit();
    }
}

We can see that disposing the transaction object will automatically rollback if not already committed, by looking at the source code. So using will clean everything up.

If you need to catch to display a message to the user, do it outside the code i.e. put a try/catch around the whole thing. Don't do the cleanup code yourself