CODEWITHSUNDEEP
c#.netsql
Erez
Erez

Reputation: 6446

Close SqlConnection in the Finally when using "Using"

I want to close the SqlConnection in the Finally since the using not really close it and the connection pool gets full. but I don't realize what's the right way to fo that since the conn object isn't reachable any more in the finally section.

try 
{
    using (var conn = new SqlConnection(_dbconnstr)) 
    {
        //...
    }
}
catch (Exception ex)
{
    //...
}
finally 
{
    conn.Close //?!?!?!?!???
}

Upvotes: 8

Views: 21066

Answers (6)

Andrey
Andrey

Reputation: 60115

using (var conn = new SqlConnection(_dbconnstr)) 
{
    //code
}

is expaded to:

SqlConnection conn = new SqlConnection(_dbconnstr);
try
{
    //code
}
finally
{
    conn.Dispose();
}

So you should handle errors but you can forget about closing connection.

Upvotes: 15

Larry
Larry

Reputation: 18051

The way you are closing the connection with Using is Ok. Perhaps you might have forgotten to close some DataReaders instead ?

Upvotes: 1

LukeH
LukeH

Reputation: 269678

You don't need to close conn in the finally block. The using block will handle closing the connection for you. (In fact, you probably don't need the try...finally at all in this case, unless you have other resources that need dealing with in the finally.)

The using block will translate to something like this:

var conn = new SqlConnection(/*...*/);
try
{
    // ...
}
finally
{
    if (conn != null)
        ((IDisposable)conn).Dispose();
}

The Dispose method of the SqlConnection object will be called in the finally block, and the Dispose method goes on to call Close for you.

Upvotes: 8

Grant Thomas
Grant Thomas

Reputation: 45058

AFAIK, the following using statement:

using (var conn = new SqlConnection(_dbconnstr)) 
{

}

Is equivalent to:

SqlConnection conn;
try
{
    //`using` scope operations are executed here
    conn = new SqlConnection(_dbconnstr));

}
catch
{
    //exceptions are bubbled
    throw;
}
finally
{
    //Dispose is always called
    conn.Dispose();
}

Upvotes: 2

CD..
CD..

Reputation: 74176

Exiting a using block calls .Dispose() on the object, for a SqlConnection will close the connection and any open resources.

So the try, finally block is not needed.

Upvotes: 2

Anuraj
Anuraj

Reputation: 19618

As per my understanding Dispose() method of connection object will close the Connection. You don't need to call Connection.Close explicitly.

Upvotes: 1

Related Questions