Using .close vs. .dispose

[Garry Greer]

We're trying to determine which is the best method for closing up
sqlconnections & datareaders while using Application Blocks (.close or
..dispose?). There are a significant amount of articles that have conflicting
opinions. Some say NOT to use .dispose because the connection will be
removed from the connection pool, others say this is not true. Some say to
use .close & let everything be garbage collected. I'd say it's about 50/50
from the pages I've read. We'd like to close the connection but have it
available in the pool.

The Application Blocks sample code looks like this:

Dim connection As New SqlConnection("myConnection")

connection.open
' do some stuff
If Not connection Is Nothing Then
CType(connection, IDisposable).Dispose() ' as opposed to
connection.close
End If

Comments?

 

[Scott M.]

Unless you've got some additional cleanup that you are writing into the
Dispose() method, I'd use .close() and let the GC take care of the rest.

By calling .close(), the connection will still be around (until it falls out
of scope) so, you could re-open it and use it again if needed.

 

[William \(Bill\) Vaughn]

I'm with Scott. Dispose does NOT remove the connection from the pool. This
rumor was started based on Beta docs that should have been corrected long
ago. Close is enough--just make SURE it happens while you still have a
handle on the Connection.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[William Ryan]

Bill:

This is good to know...I read those docs and thought that dispose was safe
to use not only with connections with with forms as well. This explains
some 'weird' behavior I've seen with connections.

Do you recommend calling dispose() after closing it or is that
inconsequential?

thanks again!

Bill

 

[bruce barker]

it makes little difference other than style. the dispose special handling is
to call close. if your a vb programmer, I'd use close. if your a c#
developer, then you can use the use statement to call dispose

use (SqlConnection conn = new SqlConnection("myConnection"))
{
// do stuff
}

otherwise you need a try catch block to handle close

 

[Bob Grommes]

I like "using" blocks:

using (myConnection) {
// Do stuff with the connection
}

This way you don't have to remember to close it.

I do the same with DataReaders.

Obviously if you intend to reuse the connection you'll have to close it.

--Bob

 

[Scott M.]

I wouldn't bother calling dispose directly. If the procedure is almost
done, the connection will fall out of scope (if declared locally). If you
wanted to dispose the connection sooner, set it to nothing, which forces it
out of scope, thus calling dispose behind the scenes.

 

[William \(Bill\) Vaughn]

Dispose is not really necessary--as long as the Connection is closed. Yes,
using Using is a good idea in C# and in Whidbey's VB.NET.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cowboy \(Gregory A. Beamer\)]

As a proponent of explicit coding, I prefer to use both personally. If it
causes a bit of a perf hit, I have not noticed, as the apps are well under
the bar. It also lets junior developers easily find where I am finished with
data access. In most apps, I also use the App Block, so this gets a little
bit gray, esp. when using overloads that you hand a connString, commandText
and a set of params.

I have not seen the problem you are speaking of [removed from pool] since
beta 1 (maybe beta 2?) of the 1.0 release, so I am not worried there.

Which method should you use. Why not use the Application Block as an
indicator of the correct direction. Take, for example, the overload of
FillDataSet:

public static void FillDataset(string connectionString, CommandType
commandType,
string commandText, DataSet dataSet,
string[] tableNames)
{
if( connectionString == null || connectionString.Length == 0 )
throw new ArgumentNullException( "connectionString" );
if( dataSet == null ) throw new ArgumentNullException( "dataSet" );

// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

// Call the overload that takes a connection in place of the
connection string
FillDataset(connection, commandType, commandText, dataSet,
tableNames);
}
}

The using() block calls Dispose(), so the MS preferred method is to
Dispose() rather than Close(). The block could be coded:

SqlConnection connection = new SqlConnection(connectionString)

try
{
connection.Open();
FillDataSet(connection, commandType, commandText, dataSet, tableNames);
}
finally
{
if(!connection == null)
connection.Dispose();
}

This is easier to see in Visual Basic .NET, which has no using keyword (this
is directly from the VB.NET version of the app block):

Try
connection = New SqlConnection(connectionString)

connection.Open()

' Call the overload that takes a connection in place of the connection
string
FillDataset(connection, commandType, commandText, dataSet, tableNames)
Finally
If Not connection Is Nothing Then connection.Dispose()
End Try

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************