CODEWITHSUNDEEP
c#asp.netdatabase-connection
c00000fd
c00000fd

Reputation: 22327

Do I need to close SQL Server connection with the using keyword?

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.

Upvotes: 10

Views: 9654

Answers (3)

Jeremy Thompson
Jeremy Thompson

Reputation: 65742

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.

Upvotes: 1

Eric J.
Eric J.

Reputation: 150238

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.

Upvotes: 13

woodykiddy
woodykiddy

Reputation: 6455

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.

Upvotes: 1

Related Questions