10

I keep finding conflicting results for this question. Let's look at this C# code of running an SQL query:

using (SqlConnection cn = new SqlConnection(strConnectString))
{
    cn.Open();

    using (SqlCommand cmd = new SqlCommand(strSQL, cn))
    {
        cmd.ExecuteNonQuery();
    }

    //Do I need to call?
    cn.Close();
}

Do I need to call that last cn.Close()? The reason I'm asking is that in a heavy traffic web app I run out of connections in a pool.

c00000fd
  • 20,994
  • 29
  • 177
  • 400

3 Answers3

12

The using keyword as used here:

using (SqlConnection cn = new SqlConnection(strConnectString))
{
    // Stuff
}

is short for:

SqlConnection cn = null;
try
{
    cn = new SqlConnection(strConnectString);
    // Stuff
}
finally
{
    if (cn != null) cn.Dispose();
}

cn.Dispose() will be called immediately after cn goes out of scope of the using, which in turn immediately closes the connection (because SqlConnection.Dispose() does just that).

UPDATE

This should not be confused with garbage collection. GC is non-deterministic in .NET, which is exactly why the IDisposable inteface and Dispose Pattern were introduced. IDisposable allows expensive resources to be released in a timely, deterministic manner.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
1

You dont need to close the connection when you use the Using statement.

Scott hanselman explains it here Why The Using Statement Is Better Than A Sharp Stick In The Eye And A SqlConnection Refactoring Example.

In a heavy traffic web app I run out of connections in a pool.

Make sure you are using the same connection string this way SQL will use Connection Pooling.

is the connection closed immediately, or is it closed when the garbage collector gets to it

Edit:

The Dispose pattern is used to provide deterministic destruction of resources. Since the .net runtime garbage collector is non-deterministic (which means you can never be sure when the runtime will collect old objects and call their finalizer). Therefore, when you implement the Dispose pattern properly you provide deterministic release of the resources and in cases where the consumer is careless and does not dispose the object, the finalizer will clean up the object.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
  • Thanks. The question is -- is the connection closed immediately, or is it closed when the garbage collector gets to it? Because that makes a difference in my case. – c00000fd Mar 21 '13 at 02:34
  • "Make sure you are using the same connection string" -- what do you mean by that? Define the `SqlConnection cn` as a static variable? – c00000fd Mar 21 '13 at 02:37
  • 1
    `It non-deterministic finalisation` = **wrong**. The connection is *disposed* immediately after going out of scope of the `using` statement. That has nothing to do with garbage collection. In fact, non-deterministic GC is exactly why the IDisposable interface and Dispose Pattern were introduced to .NET. – Eric J. Mar 21 '13 at 02:39
  • Thanks you both. But that is what I keep finding on the web -- conflicting info. In my view I need to dispose of all connections ASAP. – c00000fd Mar 21 '13 at 02:41
  • @c00000fd see [here](http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx) When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. – Jeremy Thompson Mar 21 '13 at 02:41
  • @EricJ. To clear up the confusion. The Dispose pattern is used to provide deterministic destruction of resources. Since the .net runtime garbage collector is non-deterministic (which means you can never be sure when the runtime will collect old objects and call their finalizer). Therefore, when you implement the Dispose pattern properly you provide deterministic release of the resources and in cases where the consumer is careless and does not dispose the object, the finalizer will clean up the object. – Jeremy Thompson Mar 21 '13 at 02:44
  • @JeremyThompson: Agreed, but that's not what comes across in your answer. That comment would actually make a nice update to the answer. – Eric J. Mar 21 '13 at 02:46
  • @EricJ. yes I agree. I have removed it. Thank you for bringing it to my attention, obviously we want [so] to be a resource of (correct) knowledge for years to come. – Jeremy Thompson Mar 21 '13 at 02:48
1

Not necessary to close it if you use using as it calls Displose() internally.

Here's a bit details about using keyword in ADO.NET, might be worth reading.

Leveraging the "using" keyword in C#

A quick search @SO will lead you to this post, where you can find your answers there too.

Community
  • 1
  • 1
woodykiddy
  • 6,074
  • 16
  • 59
  • 100