Where to put try / catch when using IDisposable

Question

I was simply advised to place the whole usingblock inside try, otherwise the area usingwill not allow an exception to be thrown. However, wouldn’t this hinder the usingproper management of their resources if an exception were thrown? If I have the code below, where should I put the blocks try?

using (connSQL = new SqlConnection(strConn)) 
{
    connSQL.Open();
    using (SqlCommand commSQL = new SqlCommand(strPreparedStatement, connSQL)) 
    {
        if (sqlParameters != null)
        {
            for (int i = sqlParameters.GetLowerBound(0); i <= sqlParameters.GetUpperBound(0); i++)
            {
                commSQL.Parameters.Add(sqlParameters[i]);
            }
        }
        drSQL = commSQL.ExecuteReader();
        dtReturn.Load(drSQL);

        commSQL.Parameters.Clear();
    }
}

In this application, it is much more important to ensure that database connections do not begin to accumulate due to exceptions.

Answer 1

using - , try/finally Dispose finally, try/catch, , - , ? ?

, , , ...

using, :

using (SqlConnection connSQL = new SqlConnection(strConn)) {

, , . , SqlDataReader using. , , , - IDisposable, .

, sqlParameters . foreach , , for (int i = 0; i < sqlParameters.Length; i++), , "" .

, :

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand(strPreparedStatement, connection))
    {
        if (sqlParameters != null)
        {
            // If sqlParameter is an array, you can just use
            // command.Parameters.AddRange(sqlParameters) instead
            foreach (SqlParameter parameter in sqlParameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        using (SqlDataReader reader = command.ExecuteReader())
        {
            DataTable table = new DataTable();
            // Perform any extra initialization here
            table.Load(reader);
            return table;
        }
    }
}

Answer 2

using, :

using (var connSQL = new SqlConnection(strConn)) 
using (var commSQL = connSQL.CreateCommand()) 
{
    connSQL.Open();
    commSQL.CommandText = strPreparedStatement;
    if (sqlParameters != null)
    {
        for (int i = sqlParameters.GetLowerBound(0); i <= sqlParameters.GetUpperBound(0); i++)
        {
            commSQL.Parameters.Add(sqlParameters[i]);
        }
    }
    using (var drSQL = commSQL.ExecuteReader())
    {
        dtReturn.Load(drSQL);
    }
}

, , .

try/finally, , using , Dispose . sql .

Answer 3

try catch, using , try finally , .

MSDN :

{
  Font font1 = new Font("Arial", 10.0f);
  try
  {
    byte charset = font1.GdiCharSet;
  }
  finally
  {
    if (font1 != null)
      ((IDisposable)font1).Dispose();
  }
}

:

using (Font font3 = new Font("Arial", 10.0f),
            font4 = new Font("Arial", 10.0f))
{
    // Use font3 and font4.
}