C# – Does using (var connection = new SqlConnection(“ConnectionString”)) still close/dispose the connection on error

cnetsql serversqlconnectiontsql

I have the following code 

try
{
    using (var connection = new SqlConnection(Utils.ConnectionString))
    {
        connection.Open();

        using (var cmd = new SqlCommand("StoredProcedure", connection))
        {                            
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            var sqlParam = new SqlParameter("id_document", idDocument);
            cmd.Parameters.Add(sqlParam);

            int result = cmd.ExecuteNonQuery();
            if (result != -1)
                return "something";

            //do something here

            return "something else";
        }
    }

    //do something
}
catch (SqlException ex)
{
    return "something AKA didn't work";
}

The question is: Does var connection still get closed if an unexpected error happens between the using brackets ({ })?

The problem is that most of my calls to stored procedures are made this way, and recently I have been getting this error:

System.InvalidOperationException: Timeout expired. The timeout
period elapsed prior to obtaining a connection from the pool. This
may have occurred because all pooled connections were in use and max
pool size was reached.

The other way I access the DB is through nHibernate.

Best Answer

using Statement (C# Reference)

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):

Related Topic