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

Question

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.

Pavior

Answer 1

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

Hautrhin

Answer 2

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?

Culver

If the pooled connection is open, is it closed when disposed of? – Littlejohn

@Tim: Yes. Note that this doesn't necessarily close the network connection itself - it just releases it back to the connection pool. – Culver

Yes, there is the possibility of a deadlock. But yes, that is pretty much how all of my calling stored procedures work. If I don't use a using I use try, catch { throw } and finnaly. So that leaves only a deadlock to investigate ? – Pavior

@Jon: when the using block is exited, is the connection immediately disposed of and the connection immediately released, or is the connection flagged as garbage and eventual release? Should the connection be closed explicitly? – Littlejohn

@Tim: Dispose will be called on the connection - that's the whole point of the using statement. – Culver

@Meltdown: Well, it may do. Worth a try, at least - but if it does fix it (you'll get exceptions of course) that should steer you to a better fix in terms of getting rid of the deadlock. – Culver

I believe the problem that you are seeing is that the connection is only closed/returned to the pool when garbage collection runs thus under load you will run out of connections. Thus the exception saying that the timeout was reached before a connection could be obtained from the pool. You have to manually close your connections - nuisance! – Histoid

@JamesWestgate I would also suggest that objects in a using statement are only closed once the GC runs. In case of a limited connection pool one might run out of connections before this happens. – Sarcastic

@ThomAS: No, "closing/disposing" is entirely separate from GC. It's not clear whether your "suggestion" is your belief about the current state of affairs, or what you'd like to happen. – Culver

@JonSkeet What i meant to say: Connections should be closed asap, even when they are contained within a using statement. Disposing and freeing the resources might happen later then one expects and in the meantime all connections could be depleted. I must admit the inner workings of dispose and close on a SQLConnection are unknown to me. – Sarcastic

@ThomAS: That just suggests you use a using statement for the connection itself, which the OP is doing. Again, I think you're getting confused between "dispose" and GC. The using statement will call Dispose, which does close the connection as appropriate. There's absolutely no need to call Close as well. – Culver

@JonSkeet Thanks for enlightening me! I recently had an issue from which i concluded above statements. I stand corrected and will read up on this. – Sarcastic

Answer 3

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

Kristinkristina

Answer 4

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

Briones

Answer 5

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.

Kolnos