469,927 Members | 1,939 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Question about Dispose

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

Nov 18 '05 #1
17 1517

"Bob Lehmann" <none> wrote in message
news:OK**************@TK2MSFTNGP10.phx.gbl...
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
Nov 18 '05 #2
<"Bob Lehmann" <none>> wrote:
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().

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 18 '05 #3
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" <none> wrote in message
news:OK**************@TK2MSFTNGP10.phx.gbl...
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

Nov 18 '05 #4
Thanks, Jon.

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

Bob Lehmann

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
<"Bob Lehmann" <none>> wrote:
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().

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Nov 18 '05 #5

"Bob Lehmann" <none> wrote in message
news:ux**************@TK2MSFTNGP09.phx.gbl...
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
Nov 18 '05 #6
Ashish M Bhonkiya <bh******@hotmail.com.nospam> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 18 '05 #7
Mythran <ki********@hotmail.com> wrote:
So, is there any reason to *ever* use Close() instead?
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 - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 18 '05 #8
OK - got it.

Thanks,
Bob Lehmann

"Mythran" <ki********@hotmail.com> wrote in message
news:#2**************@TK2MSFTNGP12.phx.gbl...

"Bob Lehmann" <none> wrote in message
news:ux**************@TK2MSFTNGP09.phx.gbl...
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

Nov 18 '05 #9
mikeb wrote:
Bob Lehmann wrote:
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.


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).

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:

-------------------------------------------------------------
For types where calling a Close method is more natural than calling a
Dispose method, add a public Close method to the base type. The Close
method in turn calls the Dispose method without parameters, which
performs the proper cleanup operations.
-------------------------------------------------------------

Essentially, a Close() method should have the same behavior as Dispose()
- it's just that sometimes 'Close' is a more natural name to use.

--
mikeb
Nov 18 '05 #10

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP***********************@msnews.microsoft.co m...
Jon Skeet [C# MVP] <sk***@pobox.com> wrote:
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?


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
Nov 18 '05 #11
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
"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om... Ashish M Bhonkiya <bh******@hotmail.com.nospam> wrote:
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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too


Nov 18 '05 #12
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
Nov 18 '05 #13
Does anyone know if the Dispose() method supresses the call to Finalize() if
Dispose has been called (for the SqlConnection object)?

Mythran
"Cor Ligthert" <no**********@planet.nl> wrote in message
news:e3**************@TK2MSFTNGP10.phx.gbl...
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

Nov 18 '05 #14
Mythran wrote:
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.


"Cor Ligthert" <no**********@planet.nl> wrote in message
news:e3**************@TK2MSFTNGP10.phx.gbl...
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


--
mikeb
Nov 18 '05 #15
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,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
"Mythran" <ki********@hotmail.com> wrote in message
news:e4**************@TK2MSFTNGP11.phx.gbl...

"Bob Lehmann" <none> wrote in message
news:OK**************@TK2MSFTNGP10.phx.gbl...
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

Nov 18 '05 #16
The following information is not true for SqlClient:

<Warning, not true>
"The Dispose() method releases all resources associated to the connection
object (including removing it from the connection pool). Not a good practice to call Dispose() unless you want connection to be removed from the
connection pool." </Warning>

It is very simple to write an application that disproves this so if you have
any questions you should try it yourself. Dispose only does two things,
first it deletes the connection string and then it calls Close. There is no
functional difference between Close and Dispose and you can call either or
both without a problem.

It is highly recommended that you call Close inside of a Finally block or
that you use the "using" construct when using SqlConnections to make sure
that the connections are properly closed and that you don't leak connections
in cases where execute throws an exception.
Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
"Bob Lehmann" <none> wrote in message
news:uF**************@TK2MSFTNGP10.phx.gbl... OK - got it.

Thanks,
Bob Lehmann

"Mythran" <ki********@hotmail.com> wrote in message
news:#2**************@TK2MSFTNGP12.phx.gbl...

"Bob Lehmann" <none> wrote in message
news:ux**************@TK2MSFTNGP09.phx.gbl...
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


Nov 18 '05 #17
Kudos on trying it for yourself! Yes you are completely correct, Close and
Dispose are functionally exchangeable and when using Pooling Dispose will
certainly not remove the connection from the pool.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
"Sami Vaaraniemi" <sa**********@pleasejippii.fi> wrote in message
news:c8**********@phys-news1.kolumbus.fi...

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP***********************@msnews.microsoft.co m...
Jon Skeet [C# MVP] <sk***@pobox.com> wrote:
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?
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

Nov 18 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Franco Gustavo | last post: by
9 posts views Thread by Alvin Bruney [MVP] | last post: by
4 posts views Thread by xyu | last post: by
5 posts views Thread by Mathias L. | last post: by
11 posts views Thread by Michael Rodriguez | last post: by
9 posts views Thread by Hasani \(remove nospam from address\) | last post: by
21 posts views Thread by Roland | last post: by
4 posts views Thread by phl | last post: by
7 posts views Thread by heddy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.