Does using (var connection = new SqlConnection("ConnectionString")) still close/dispose the connection on error?

I have the following code

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

        using (var cmd = new SqlComma开发者_运维问答nd("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.

---

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):

---

Yes, if it gets into the body of the using statement, it will be disposed at the end... whether you reached the end of the block normally, exited via a return statement, or an exception was thrown. Basically the using statement is equivalent to a try/finally block.

Is that the only place you acquire a connection? Has your stored procedure deadlocked somewhere, perhaps, leaving lots of connections genuinely "busy" as far as the client code is concerned?

---

In terms of your connection pool running out of available connections, if you are in a distributed environment and using many applications to access SQL Server but they all use the same connection string, then they will all be using the same pool on the server. To get around this you can change the connection string for each application by setting the connection WorkstationID to the Environment.MachineName. This will make the server see each connection as different and provide a pool to each machine instead of sharing the pool.

In the below example we even pass in a token to allow an application on the same machine to have multiple pools.

Example:

    private string GetConnectionStringWithWorkStationId(string connectionString, string connectionPoolToken)
    {
        if (string.IsNullOrEmpty(machineName)) machineName = Environment.MachineName;

        SqlConnectionStringBuilder cnbdlr;
        try
        {
            cnbdlr = new SqlConnectionStringBuilder(connectionString);
        }
        catch
        {
            throw new ArgumentException("connection string was an invalid format");
        }

        cnbdlr.WorkstationID = machineName + connectionPoolToken;

        return cnbdlr.ConnectionString;
    }

---

Replace your above code.. by this.. and check again..

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";

        }

        connection.Close();
        connection.Dispose();
    }

    //do something

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

---

Here's a reference:

http://msdn.microsoft.com/en-us/library/yh598w02(v=vs.80).aspx

What I know is that if you use an object within the using {} clause, that object inherits the IDisposable interface (i.e. SqlConnection inherits DbConnection, and DbConnection inherits IDisposable), which means if you get an exception, any object will be closed and disposed properly.