CODEWITHSUNDEEP
c#transactions
papa dp
papa dp

Reputation: 65

Cleanest way to use BeginTransaction using try catch

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?

Upvotes: 0

Views: 2052

Answers (1)

Charlieface
Charlieface

Reputation: 72153

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

Upvotes: 3

Related Questions