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.

score 6 · Accepted Answer · answered Sep 26 '11 at 08:15

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

score 4 · Answer 2 · answered Sep 26 '11 at 08:15

4

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?

answered Sep 26 '11 at 08:15

Jon Skeet

1,421,763
867
9,128
9,194

If the pooled connection is open, is it closed when disposed of? – Tim Sep 26 '11 at 08:20
@Tim: Yes. Note that this doesn't necessarily close the network connection itself - it just releases it back to the connection pool. – Jon Skeet Sep 26 '11 at 08:21
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 ? – Dragos Durlut Sep 26 '11 at 08:22
@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? – Tim Sep 26 '11 at 08:26
@Tim: `Dispose` will be called on the connection - that's the whole point of the using statement. – Jon Skeet Sep 26 '11 at 08:48
@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. – Jon Skeet Sep 26 '11 at 12:40
1

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! – James Westgate Nov 09 '11 at 16:48
@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. – Th 00 mÄ s Oct 25 '12 at 09:15
@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. – Jon Skeet Oct 25 '12 at 09:43
@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. – Th 00 mÄ s Oct 25 '12 at 10:17
@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. – Jon Skeet Oct 25 '12 at 10:34
@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. – Th 00 mÄ s Oct 25 '12 at 11:30

score 2 · Answer 3 · answered Oct 01 '11 at 05:02

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

score 0 · Answer 4 · answered Jan 03 '13 at 04:31

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.

score 0 · Answer 5 · answered Sep 26 '11 at 08:19

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

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

5 Answers5

using Statement (C# Reference)

Linked