Question about Dispose

[Bob Lehmann]

Hi,

My understanding is that Dispose() should not be used for destroying a
connection object, and that Close() is preferred.

However, in one of MS's Quickstart Apps I see this being used....

This is found in the SqlHelper class -

Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection
string
Return ExecuteScalar(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try

Is this the correct way to do this?

Bob Lehmann

 

[Mythran]

Hi,

My understanding is that Dispose() should not be used for destroying a
connection object, and that Close() is preferred.

However, in one of MS's Quickstart Apps I see this being used....

This is found in the SqlHelper class -

Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection
string
Return ExecuteScalar(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try

Is this the correct way to do this?

Bob Lehmann

This is done because the Open method is inside the Try block and in the Finally
block you have no idea whether or not the connection opened successfully. What
if the connection opened but there's a bug in the connection itself? If you call
close you may get an error, or any other method may throw an exception.

Dispose makes sure the connection is properly closed prior to the object being
destroyed. This is a proper technique used to ensure that whether or not the
connection was opened, it will be closed before the end of the Try...Finally
block has been closed.

Make sense?

Mythran

 

[Jon Skeet [C# MVP]]

My understanding is that Dispose() should not be used for destroying a
connection object, and that Close() is preferred.

However, in one of MS's Quickstart Apps I see this being used....

This is found in the SqlHelper class -

Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection
string
Return ExecuteScalar(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try

Is this the correct way to do this?

I believe this is correct. I don't think there's any advantage in
calling Close() over calling Dispose().

 

[Ashish M Bhonkiya]

Hi Bob Lehmann,

You are correct.

The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool.

Regards
Ashish M Bhonkiya

 

[Bob Lehmann]

Thanks, Jon.

So, is there any reason to *ever* use Close() instead?

Bob Lehmann

 

[Mythran]

Thanks, Jon.

So, is there any reason to *ever* use Close() instead?

Bob Lehmann

Yes, quoted from Ashish:

"The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool."


That about sums it up

If you are worried about connection pooling, you will want to not use
Try...Finally and Dispose(). You will use Try...Finally and then check to see if
1.) the connection exists 2.) it's open 3.) call Close().

Hope this helps

Mythran

 

[Jon Skeet [C# MVP]]

You are correct.

The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool.

Any evidence for this? I've heard talk of it before, but thought that
someone had disproved it in the same thread. It would certainly seem an
odd decision to make, as in languages such as C# with its "using"
statement, Dispose() is the most natural method to call.

 

[Bob Lehmann]

OK - got it.

Thanks,
Bob Lehmann

Yes, quoted from Ashish:

"The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool."


That about sums it up

If you are worried about connection pooling, you will want to not use
Try...Finally and Dispose(). You will use Try...Finally and then check to see if
1.) the connection exists 2.) it's open 3.) call Close().

Hope this helps

Mythran

 

[Jon Skeet [C# MVP]]

Yes, quoted from Ashish:

"The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool."

That about sums it up

Well, if it's correct. I have some doubts about it, to be honest.

If you are worried about connection pooling, you will want to not use
Try...Finally and Dispose(). You will use Try...Finally and then check to see if
1.) the connection exists 2.) it's open 3.) call Close().

Why would MS make life so hard for developers, relatively speaking?
It's much easier just to call Dispose(), and C# encourages this
practice with the "using" statement.

Anyone have appropriate experience which would allow them to easily
test this? While I have SQL Server on my laptop, I'm not convinced I
would know how to really *reliably* test this. I'll have a go though

 

[Jon Skeet [C# MVP]]

Anyone have appropriate experience which would allow them to easily
test this? While I have SQL Server on my laptop, I'm not convinced I
would know how to really *reliably* test this. I'll have a go though

I've had a go, and it seems to be false. Here's my test code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

class Test
{
static void Main()
{
OpenAndCloseConnection();
OpenAndCloseConnection();
OpenAndCloseConnection();
Console.WriteLine ("Finished opening and closing");
Console.ReadLine();
OpenAndDisposeConnection();
OpenAndDisposeConnection();
OpenAndDisposeConnection();
Console.WriteLine ("Finished opening and disposing");
Console.ReadLine();
}

static void OpenAndCloseConnection()
{
SqlConnection conn = new SqlConnection
("Server=treebeard;Integrated Security=SSPI;Database=Northwind");

conn.Open();
SqlCommand cmd = new SqlCommand
("SELECT COUNT(*) FROM REGION");
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
}

static void OpenAndDisposeConnection()
{
SqlConnection conn = new SqlConnection
("Server=treebeard;Integrated Security=SSPI;Database=Northwind");

conn.Open();
SqlCommand cmd = new SqlCommand
("SELECT COUNT(*) FROM REGION");
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Dispose();
}
}

And here's what I saw in my profiler:

Audit Login
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
Audit Login
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
Audit Logout
Audit Logout

The SPID was 51 except for one Login and one Logout where it was 53 - I
assume that was another connection being started in case it was needed,
although I wouldn't like to say for sure.

Anyway, I can't see any difference from the above between calling
Dispose and calling Close. Have I done something wrong, or is the idea
that Dispose removes the connection from the pool just an urban myth?

 

[mikeb]

Hi,

My understanding is that Dispose() should not be used for destroying a
connection object, and that Close() is preferred.

However, in one of MS's Quickstart Apps I see this being used....

This is found in the SqlHelper class -

Dim cn As New SqlConnection(connectionString)
Try
cn.Open()
'call the overload that takes a connection in place of the connection
string
Return ExecuteScalar(cn, commandType, commandText, commandParameters)
Finally
cn.Dispose()
End Try

Is this the correct way to do this?

The Dispose() method in SqlConnection() checks the current state of the
connection, and calls Close() if it's open. Nothing more.

Close() makes the same check (ie., it's safe to close a connection that
is not open), so the check in Dispose() is not really necessary.

The Framework docs have advice on Dispose() vs. Close() naming:

 

[Chris R. Timmons]

Hi Bob Lehmann,

You are correct.

The Dispose() method releases all resources associated to the
connection object (including removing it from the connection
pool). Not a good practise to call Dispose() unless you want
connection to be removed from the connection pool.

Regards
Ashish M Bhonkiya

Not according to the documentation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

(http://tinyurl.com/2y2ru)

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/cpguide/html/cpconConnectionPoolingForOLEDBNETDataProvider.asp

(http://tinyurl.com/2rody)

 

[Scott Allen]

As you said earlier Jon, this is an urban myth. The documentation for
SqlConnection clearly states Dispose results in a call to Close, and
the *connection* returns to the connection pool (if connection pooling
is enabled). This can be verified, as you pointed out, with SQL
Profiler.

Note there is a difference between SqlConnection, the managed object,
and a SQL connection, the unmanged resource. The connection pool is a
pool of free SQL connections, not a pool of SqlConnection objects.

There is no need to call both Close and Dispose. There are, however,
slight differences in calling one or the other. For example, Dispose
will null out the ConnectionString property.

 

[mikeb]

The Dispose() method in SqlConnection() checks the current state of the
connection, and calls Close() if it's open. Nothing more.

After reading Scott Allen's post elsewhere in this thread, I have to
correct my above statement: Dispose() does set the SqlConnection
object's connection string to null (which Close() does not seem to do).

This means that a connection that has been closed using Close() can be
reopened. A connection that has been closed using Dispose() cannot
without reinitializing the connection string. I don't think this
affects the pooling algorithm one way or another.

However, continuing to use a disposed object is probably not a good
practice in general (operations on a disposed object should result in
ObjectDisposed exception).

 

[Sami Vaaraniemi]

I've had a go, and it seems to be false. Here's my test code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

class Test
{
static void Main()
{
OpenAndCloseConnection();
OpenAndCloseConnection();
OpenAndCloseConnection();
Console.WriteLine ("Finished opening and closing");
Console.ReadLine();
OpenAndDisposeConnection();
OpenAndDisposeConnection();
OpenAndDisposeConnection();
Console.WriteLine ("Finished opening and disposing");
Console.ReadLine();
}

static void OpenAndCloseConnection()
{
SqlConnection conn = new SqlConnection
("Server=treebeard;Integrated Security=SSPI;Database=Northwind");

conn.Open();
SqlCommand cmd = new SqlCommand
("SELECT COUNT(*) FROM REGION");
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
}

static void OpenAndDisposeConnection()
{
SqlConnection conn = new SqlConnection
("Server=treebeard;Integrated Security=SSPI;Database=Northwind");

conn.Open();
SqlCommand cmd = new SqlCommand
("SELECT COUNT(*) FROM REGION");
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Dispose();
}
}

And here's what I saw in my profiler:

Audit Login
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
Audit Login
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
RPC:Completed - exec sp_reset_connection
SQL:BatchCompleted - SELECT COUNT(*) FROM REGION
Audit Logout
Audit Logout

The SPID was 51 except for one Login and one Logout where it was 53 - I
assume that was another connection being started in case it was needed,
although I wouldn't like to say for sure.

Anyway, I can't see any difference from the above between calling
Dispose and calling Close. Have I done something wrong, or is the idea
that Dispose removes the connection from the pool just an urban myth?

I'm sure it is a myth. First, just like you say, MS encourages developers to
use the 'using' statement with the connection which ends up calling Dispose.
I don't think MS would encourage this practice if it indeed removed the
connection from the pool as this would be a costly operation. Second, if you
take a look at what SqlConnection.Dispose does with your favorite
decompiler, you'll see that Dispose does almost nothing but calls Close.
Therefore, Dispose does the same thing as Close does, which is to put the
connection *back* to the connection pool if pooling is on. If pooling is not
on, it closes the physical connection.

Sami

 

[Ashish M Bhonkiya]

Hi,

After executing the sample example in 'Jon Skeet' post and examining the
sqlprofiler
i have to agree with him, its a urban Myth..

The following statement is wrong.

The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practise
to call Dispose() unless you want connection to be removed from the
connection pool.

Thanks Jon for correcting me.

Regards
Ashish M Bhonkiya

 

[Cor Ligthert]

Hi Bob,

Have a look at this thread wherefore I have the url below (and only needed
are some answers from Angel, those declares in my opinion everything)

This end statement from him in one message was for me the most clear
The code for Dispose does two things:
1) sets the connection string to ""
2) calls close.
There is no difference between calling either close or dispose (or
both), but you have to make sure to call them during a "finally" to
ensure that you close the connection even when there is an exception.
We added IDisposable support for ado.net classes just so that we could
use the "using" clr construct which is the most visually pleasing way
to write this type of code. In VB.NET you have to make sure to use try
finally blocks.

An advice from him in this thread as well which I found important is to use
dispose for application which have more than 100 connections.

http://tinyurl.com/yrzh4

I hope this helps?

Cor

 

[Mythran]

Does anyone know if the Dispose() method supresses the call to Finalize() if
Dispose has been called (for the SqlConnection object)?

Mythran

 

[mikeb]

Does anyone know if the Dispose() method supresses the call to Finalize() if
Dispose has been called (for the SqlConnection object)?

GCSuppressFinalize() actually gets called when the connection is
created. Since the connection pool is managing the object lifetime
(regardless of whether it's disposed), the constructor disables the
finalizer right off the bat.

I'm not sure what the implications of this are for non-pooled
connections if you forget to close or dispose them.

 

[Angel Saenz-Badillos[MS]]

There is a common misconception going around regarding SqlConnection close
and dispose being different, this is not true.

Dispose does only two things, first it clears the SqlConnection connection
string, then it calls Close.

Calling Close or Dispose on a finally block or using the SqlConnection
inside a "using" statement is highly recommended, this will avoid very
common problems with leaking connections when an exception happens on
execute.

Hope this helps,