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

"Bob Lehmann" <none> wrote in message
news:(E-Mail Removed)...
> 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

<"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 - <(E-Mail Removed)>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

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:(E-Mail Removed)...
> 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
>
>
>

Thanks, Jon.

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

Bob Lehmann

"Jon Skeet [C# MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> <"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 - <(E-Mail Removed)>
> http://www.pobox.com/~skeet
> If replying to the group, please do not mail me too

"Bob Lehmann" <none> wrote in message
news:(E-Mail Removed)...
> 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

Ashish M Bhonkiya <(E-Mail Removed)> 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 - <(E-Mail Removed)>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

OK - got it.

Thanks,
Bob Lehmann

"Mythran" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
>
> "Bob Lehmann" <none> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>

Mythran <(E-Mail Removed)> 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 - <(E-Mail Removed)>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Jon Skeet [C# MVP] <(E-Mail Removed)> 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?

--
Jon Skeet - <(E-Mail Removed)>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too