sqlConnection/Command using statement + try/catch

Question

This question already has answers here:

try/catch + using, right syntax (9 answers)

Closed 6 years ago.

What is a correct approach try/catch inside using or using inside try/catch?

using (SqlConnection connection = CreateSqlConnection(connString))
{
               using (SqlCommand command = CreateSqlCommand()
               {
                   try{//open connection + execute command + do something else}
                   catch{//do something}
               }
}

vs.

try
{
    using (SqlConnection connection = CreateSqlConnection(connString))
    {
               using (SqlCommand command = CreateSqlCommand()
               {
                   //open connection + execute command + do something else
               }
    }
}
catch
{
 //do something
}

Answer 1

From my point of view:

try
{
    using (SqlConnection connection = CreateSqlConnection(connString))
    {
               using (SqlCommand command = CreateSqlCommand()
               {
                   //open connection + execute command + do something else
               }
    }
}
catch
{
 //do something
}

Above is the correct way.

Because , with this approach if there is exception with connection to database, that will get caught inside catch block.. But with first approach, it will not.

Answer 2

Personally, I think the best way is - then the connection is closed regardless and you get to handle the exception as you wish

using (SqlConnection connection = CreateSqlConnection(connString))
{
    using (SqlCommand command = CreateSqlCommand()) 
    {
          try { //open connection, execute }
          catch { // log and handle exception }
          finally { // check connection state and close if required }
    }
}

Answer 3

Both are correct in the sense that both will close the disposable resources in case of an error.

Where you place the try-catch-statement should depend on what you want to do with that information, i.e. if you want to react to an error concerning the SqlCommand itself or a more general SQL-error, that could also involve the connection.