468,463 Members | 2,034 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

2 ways to close a connection

In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain this
line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric

Nov 20 '05 #1
35 9715
Eric,

The connection is converted to a type IDisposable and then the Dispose
method is called to release unmanaged resources. When you call Dispose(),
all the necessary cleanup is done when the call returns instead of waiting
on the GC to do it when it feels it is necessary at an indeterminate point
in the future.

Raymond Lewallen

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:#8*************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain this line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric

Nov 20 '05 #2
Thank you. What you said makes sense. If I had used cn.close, I am simply
at the mercy of the garbage collector to clean it up when it wants. Here
are 2 questions back to you.

1) is it not better to let the GC manage the resouces, or should I override
it?
2) I notice that the SQLConnection object has its own Dispose method. Why
do you suppose it's not used here?

Eric

"Raymond Lewallen" <Ra******************@nospam.faa.gov> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Eric,

The connection is converted to a type IDisposable and then the Dispose
method is called to release unmanaged resources. When you call Dispose(),
all the necessary cleanup is done when the call returns instead of waiting
on the GC to do it when it feels it is necessary at an indeterminate point
in the future.

Raymond Lewallen

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:#8*************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain

this
line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric


Nov 20 '05 #3
CType(cn, IDisposable).Dispose()

Treat(an object, as an IDisposable interface).and call Dispose method on the
result.

If cn is a variable that points to an ADO.NET connection object the code
below will work because ADO.NET connection objects implement the IDisposable
interface's Dispose method as a public method you can call directly.

cn.Dispose()

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain this line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric

Nov 20 '05 #4
Unmanaged resources should me managed by you. Learn more at:

http://www.devcity.net/net/article.aspx?alias=gc_manage

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:eR**************@TK2MSFTNGP11.phx.gbl...
Thank you. What you said makes sense. If I had used cn.close, I am simply at the mercy of the garbage collector to clean it up when it wants. Here
are 2 questions back to you.

1) is it not better to let the GC manage the resouces, or should I override it?
2) I notice that the SQLConnection object has its own Dispose method. Why
do you suppose it's not used here?

Eric

"Raymond Lewallen" <Ra******************@nospam.faa.gov> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Eric,

The connection is converted to a type IDisposable and then the Dispose
method is called to release unmanaged resources. When you call Dispose(), all the necessary cleanup is done when the call returns instead of waiting on the GC to do it when it feels it is necessary at an indeterminate point in the future.

Raymond Lewallen

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:#8*************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain

this
line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric



Nov 20 '05 #5
This calls the Dispose method of the connection (same thing the garbage
collector would call). In the case of SqlConnection, I believe both Dispose
and Close really do the same thing.

However, you should just use Close, as this is very clear in what it does.
This is sufficient in closing the connection and releasing it back into the
pool. The garbage collector will clean up the actual object when it runs.

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain this line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric

Nov 20 '05 #6
Very helpful.
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:Oy**************@TK2MSFTNGP11.phx.gbl...
Unmanaged resources should me managed by you. Learn more at:

http://www.devcity.net/net/article.aspx?alias=gc_manage

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:eR**************@TK2MSFTNGP11.phx.gbl...
Thank you. What you said makes sense. If I had used cn.close, I am

simply
at the mercy of the garbage collector to clean it up when it wants. Here
are 2 questions back to you.

1) is it not better to let the GC manage the resouces, or should I

override
it?
2) I notice that the SQLConnection object has its own Dispose method. Why do you suppose it's not used here?

Eric

"Raymond Lewallen" <Ra******************@nospam.faa.gov> wrote in message news:%2****************@TK2MSFTNGP12.phx.gbl...
Eric,

The connection is converted to a type IDisposable and then the Dispose
method is called to release unmanaged resources. When you call

Dispose(), all the necessary cleanup is done when the call returns instead of waiting on the GC to do it when it feels it is necessary at an indeterminate point in the future.

Raymond Lewallen

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:#8*************@tk2msftngp13.phx.gbl...
> In my Finally block, I was using cn.close (where cn is an ADO.NET
> connection object, SQLConnection to be exact) and then I came across the > following in some microsoft code.
>
> If Not cn Is Nothing Then
> CType(cn, IDisposable).Dispose()
> End If
>
> I have to admit, I'm not sure what happens here. Will someone explain this
> line of code (the middle one, not the if statement LOL) to me please? > [hopefully I won't get flamed for this :-) ]
>
> Eric
>
>
>



Nov 20 '05 #7
Just curious. If I close a connection, can I not, at a later time, just open it again and use it?

If I dispose of it, would I not have to reinitialize it before I could open it again?

If so, then Closing and Disposing are net the same.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Marina" <so*****@nospam.com> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl...
This calls the Dispose method of the connection (same thing the garbage
collector would call). In the case of SqlConnection, I believe both Dispose
and Close really do the same thing.

However, you should just use Close, as this is very clear in what it does.
This is sufficient in closing the connection and releasing it back into the
pool. The garbage collector will clean up the actual object when it runs.

"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the
following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain

this
line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric


Nov 20 '05 #8
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor
Nov 20 '05 #9
I guess that really depends on what Dispose does. If all it does is close
the connection - then maybe you can. If it does more then that, then
possibly you wouldn't.

But closing the connection is suffient in releasing it back to the pool and
not taking up unnecessary resources.

"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:OS*************@TK2MSFTNGP12.phx.gbl...
Just curious. If I close a connection, can I not, at a later time, just open it again and use it?
If I dispose of it, would I not have to reinitialize it before I could open it again?
If so, then Closing and Disposing are net the same.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so." --- Mark Twain

"Marina" <so*****@nospam.com> wrote in message

news:%2****************@TK2MSFTNGP10.phx.gbl...
This calls the Dispose method of the connection (same thing the garbage
collector would call). In the case of SqlConnection, I believe both Dispose and Close really do the same thing.

However, you should just use Close, as this is very clear in what it does. This is sufficient in closing the connection and releasing it back into the pool. The garbage collector will clean up the actual object when it runs.
"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...
In my Finally block, I was using cn.close (where cn is an ADO.NET
connection object, SQLConnection to be exact) and then I came across the following in some microsoft code.

If Not cn Is Nothing Then
CType(cn, IDisposable).Dispose()
End If

I have to admit, I'm not sure what happens here. Will someone explain

this
line of code (the middle one, not the if statement LOL) to me please?
[hopefully I won't get flamed for this :-) ]

Eric



Nov 20 '05 #10
Hi there,

I came across this recently and though I'd throw in my two pennies...

The Connection object does not in fact represent a connection to SQL
server - there is a seperate class - I can't remember the name off-hand that
actually encapsulates the SQL connection and it is this that the pooling
methods use to pool connections. By closing/disposing your Connection object
you free the internal connection objects ability to be re-used within the
pool.

If you want to see what I've tried to explain above then use a profiling
tool (there are plenty out there with eval licenses :-) and watch what
happens. You'll see various objects created and then destroyed at a later
date - after you've disposed of your Connection object. It's well worth
turning connection pooling off (within the connection string) and repeating
the excercise to see the difference. You'll spot that there is a background
thread created who's sole job is to manage the destruction of the actual
connections rather than the Connection objects.

cheers,

g

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that the connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor

Nov 20 '05 #11
Hi Al,
Just curious. If I close a connection, can I not, at a later time, just open it again and use it?
You can reopen it

If I dispose of it, would I not have to reinitialize it before I could open it again?
You would have to reintialize
If so, then Closing and Disposing are not the same.

See that as a book,
Closing it is something else than throwing it in the dustbin

Cor
Nov 20 '05 #12
As for this piece of sample code well it's very great is it!

Two things I guess - firstly shouldn't that be a DirectCast() rather than a
CType() [ctype is much slower as does additional checks).

My guess is that the sample was from back in the early days where I believe
that like in C# the interfaces are not exposed like they are in modern
VB.Net.

Correct me if I'm wrong but that should now read:

cn.Dispose()

cheers,

g
"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:O9*************@TK2MSFTNGP10.phx.gbl...
Very helpful.
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:Oy**************@TK2MSFTNGP11.phx.gbl...
Unmanaged resources should me managed by you. Learn more at:

http://www.devcity.net/net/article.aspx?alias=gc_manage

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
"Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
news:eR**************@TK2MSFTNGP11.phx.gbl...
Thank you. What you said makes sense. If I had used cn.close, I am

simply
at the mercy of the garbage collector to clean it up when it wants. Here are 2 questions back to you.

1) is it not better to let the GC manage the resouces, or should I

override
it?
2) I notice that the SQLConnection object has its own Dispose method. Why do you suppose it's not used here?

Eric

"Raymond Lewallen" <Ra******************@nospam.faa.gov> wrote in message news:%2****************@TK2MSFTNGP12.phx.gbl...
> Eric,
>
> The connection is converted to a type IDisposable and then the Dispose > method is called to release unmanaged resources. When you call

Dispose(),
> all the necessary cleanup is done when the call returns instead of

waiting
> on the GC to do it when it feels it is necessary at an indeterminate

point
> in the future.
>
> Raymond Lewallen
>
> "Eric Sabine" <mopar41@___ho_y_tmail.ScPoAmM> wrote in message
> news:#8*************@tk2msftngp13.phx.gbl...
> > In my Finally block, I was using cn.close (where cn is an ADO.NET
> > connection object, SQLConnection to be exact) and then I came
across
the
> > following in some microsoft code.
> >
> > If Not cn Is Nothing Then
> > CType(cn, IDisposable).Dispose()
> > End If
> >
> > I have to admit, I'm not sure what happens here. Will someone

explain > this
> > line of code (the middle one, not the if statement LOL) to me please? > > [hopefully I won't get flamed for this :-) ]
> >
> > Eric
> >
> >
> >
>
>



Nov 20 '05 #13
Cor,

Thanks. I am a long time VB programmer who is in transition to .NET. I just want to be clear on the distinction.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

"Cor Ligthert" <no**********@planet.nl> wrote in message news:O%****************@TK2MSFTNGP09.phx.gbl...
Hi Al,
Just curious. If I close a connection, can I not, at a later time, just

open it again and use it?
You can reopen it

If I dispose of it, would I not have to reinitialize it before I could

open it again?
You would have to reintialize
If so, then Closing and Disposing are not the same.

See that as a book,
Closing it is something else than throwing it in the dustbin

Cor

Nov 20 '05 #14
I would be curious to know exactly the difference. I opened up the Dispose
method in ILDASM, to see what it does. And though I am no expert in reading
IL, it seemed to me that it was basically calling Close, and then the
Dispose of the base class, and that's about it.

If this is the case, it would seem to suggest, that calling Close is enough
to release the connection (as the Dispose of the base class, wouldn't be
able to do anything else as far as releasing the connection since
SqlConnection inherits from Component, which isn't a data specific class).

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that the connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor

Nov 20 '05 #15
Hi Marina,

I thought that you where also in Angel Saenz-Badillos made some sample code
to try it, however he did sound so sure (and I never saw him write strange
things) that I absolute did found it not needed to test it.

http://tinyurl.com/2sh6k

This is the thread. (and I saw you where in it too) :-)

Cor
Nov 20 '05 #16
Yes, I guess I was, and I had the same advice then to.

I just tried reproducing what Angel did. Here is my code:

Sub Main()
Try
For i As Integer = 1 To 1000
ConnTest()
Next
Console.WriteLine("ok")
Catch ex As System.Exception
Console.WriteLine(ex.ToString())
End Try
Console.ReadLine()
End Sub

Private Sub ConnTest()
Dim conn As New SqlConnection(connString)
Dim command As New SqlCommand("Select count(*) from mytable", conn)
conn.Open()
command.ExecuteReader().Read()
conn.Close()
End Sub

Doing this, I would run it in debug mode, and pause it, at say i = 100,
etc. At all times, there was never more then 2 connections from this app on
my db server (I passed in an 'applicationname' parameter in the connection
string, to make sure I knew which connections were coming from my test app).

So as of yet, I have not been convinced, as the example that was given, does
not produce the results that it supposedly would.

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:uM**************@TK2MSFTNGP11.phx.gbl...
Hi Marina,

I thought that you where also in Angel Saenz-Badillos made some sample code to try it, however he did sound so sure (and I never saw him write strange
things) that I absolute did found it not needed to test it.

http://tinyurl.com/2sh6k

This is the thread. (and I saw you where in it too) :-)

Cor

Nov 20 '05 #17
Hi Marina,

I have to believe you too, then we go in discussion with Angel OK in the
Adonet newsgroup?

I will start this no problem at all, mostly he is on friday active.

You know we have the same idea about this, you could have seen that in that
thread that I did support your answer and that we are always writting the
same about this.

(And I stop supporting the dispose for the connection string, which I also
did not support before the message from Angel)

:-)

Cor
Nov 20 '05 #18
Well, that's not necessary unless you feel like starting the thread, which I
guess would be interesting.

Can you (or anyone else), confirm or deny my results?

I'm glad we've been agreeing :)

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:Op*************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

I have to believe you too, then we go in discussion with Angel OK in the
Adonet newsgroup?

I will start this no problem at all, mostly he is on friday active.

You know we have the same idea about this, you could have seen that in that thread that I did support your answer and that we are always writting the
same about this.

(And I stop supporting the dispose for the connection string, which I also
did not support before the message from Angel)

:-)

Cor

Nov 20 '05 #19
Calling Close on a SqlConnection object does not release the other unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close to
close the connection and then Dispose on the base class to release the other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

.... Connection.Close() will automatcially be called because 'using' keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a connection
object is left up to us. We must 1) call Dispose explicity in our code OR 2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that the connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor

Nov 20 '05 #20
Marina,

Your results are confirmed. Calling Close is how to return the connection
to the connection pool.

But behind the scenes the GC is getting hit hard with a lot of Connection
objects that are holding onto other unmanaged resources. See my last post
for information about the difference between closing a connection and
disposing it. There is a time to use close and a time to use Dispose ;-)
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com
"Marina" <so*****@nospam.com> wrote in message
news:eH**************@tk2msftngp13.phx.gbl...
Well, that's not necessary unless you feel like starting the thread, which I guess would be interesting.

Can you (or anyone else), confirm or deny my results?

I'm glad we've been agreeing :)

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:Op*************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

I have to believe you too, then we go in discussion with Angel OK in the
Adonet newsgroup?

I will start this no problem at all, mostly he is on friday active.

You know we have the same idea about this, you could have seen that in

that
thread that I did support your answer and that we are always writting the same about this.

(And I stop supporting the dispose for the connection string, which I also did not support before the message from Angel)

:-)

Cor


Nov 20 '05 #21
Mike,

Thanks for the clear and concise explanation. This does, indeed, match what
I had thought would be the case.
--

Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other unmanaged resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in VB. In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using' keyword wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a connection object is left up to us. We must 1) call Dispose explicity in our code OR 2) leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as you are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection object's other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to the connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing a connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object that uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor


Nov 20 '05 #22
What other resources exactly does it release?

How could the base class's dispose (which is Component) have any database
specific code, since it is just a generic class to be inherited from?

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other unmanaged resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in VB. In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using' keyword wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a connection object is left up to us. We must 1) call Dispose explicity in our code OR 2) leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as you are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection object's other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to the connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing a connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object that uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor


Nov 20 '05 #23
Marina,

Thank you for pointing out a mistake in my post.

Where I wrote:
Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.
I should have written:
Calling the Dispose method of a SqlConnection object: 1. Checks to see if the connection is open and if it is it closes it.
2. Performs some resource housekeeping (see decompiled Dispose method for SqlConnection class below).
3. Calls Dispose on the base class (which does not have anything to do with the database connection).

Decompiled SqlConnection Class:

Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Begin Sub
Dim state1 As ConnectionState
state1 = Me._objectState
Switch (state1)
Case 0:
goto L_0035

End Case
Case 1:
goto L_0017
End Case
End Switch
goto L_0035
L_0017:
If disposing Then
Me.Close
Me._connectionOptions = Nothing
Me._cachedOptions = Nothing
Me._connectionString = Nothing
End If
L_0035:
MyBase.Dispose(disposing)
End Sub


By the way I am referring to Framework version 1.0.3705 in my posts to this thread.

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Marina" <so*****@nospam.com> wrote in message news:OH**************@TK2MSFTNGP11.phx.gbl... What other resources exactly does it release?

How could the base class's dispose (which is Component) have any database
specific code, since it is just a generic class to be inherited from?

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other

unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close

to
close the connection and then Dispose on the base class to release the

other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in

VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using'

keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a

connection
object is left up to us. We must 1) call Dispose explicity in our code OR

2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as

you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection

object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to

the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing

a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object

that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor



Nov 20 '05 #24
Ok. So looking at this code, Dispose does virtually nothing other then calling Close. It sets some internal variables to Nothing, and that's about it. We both agree that calling Dispose of the base class is not relevant here.

Here is the decompiled version of 1.1:

protected virtual void Dispose(bool disposing) {
ConnectionState local0;

if (disposing) {
local0 = this._objectState;
switch (local0)
case 1:
this.Close();
break;
this._constr = null;
}
this.Dispose(disposing);
}

So here it is very similarly, calls Close, sets a variable to null, and calls the base class Dispose.

So I am still not convinced how Dispose is any better then Close... Sorry!
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:eq**************@tk2msftngp13.phx.gbl...
Marina,

Thank you for pointing out a mistake in my post.

Where I wrote:
Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.
I should have written:
Calling the Dispose method of a SqlConnection object: 1. Checks to see if the connection is open and if it is it closes it.
2. Performs some resource housekeeping (see decompiled Dispose method for SqlConnection class below).
3. Calls Dispose on the base class (which does not have anything to do with the database connection).

Decompiled SqlConnection Class:

Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Begin Sub
Dim state1 As ConnectionState
state1 = Me._objectState
Switch (state1)
Case 0:
goto L_0035

End Case
Case 1:
goto L_0017
End Case
End Switch
goto L_0035
L_0017:
If disposing Then
Me.Close
Me._connectionOptions = Nothing
Me._cachedOptions = Nothing
Me._connectionString = Nothing
End If
L_0035:
MyBase.Dispose(disposing)
End Sub


By the way I am referring to Framework version 1.0.3705 in my posts to this thread.

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Marina" <so*****@nospam.com> wrote in message news:OH**************@TK2MSFTNGP11.phx.gbl... What other resources exactly does it release?

How could the base class's dispose (which is Component) have any database
specific code, since it is just a generic class to be inherited from?

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other

unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close

to
close the connection and then Dispose on the base class to release the

other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in

VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using'

keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a

connection
object is left up to us. We must 1) call Dispose explicity in our code OR

2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as

you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection

object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to

the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing

a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object

that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor



Nov 20 '05 #25
Hi Marina,
So looking at this code, Dispose does virtually nothing other then calling Close.


This one I have tested, try it also if you want, when you disposing a connection you need to create the connection again and with a close that is not needed.

Cor


Nov 20 '05 #26
I agree that calling Dispose of the base class is irrelevant

Experience has shown me that calling Close vs Dispose on the SqlConnection object itself (not the base class) gives different results in Framework version 1.0x. Close has it purpose and Dispose has its purpose. Close does some but not all of the things Dispose does. Dispose does a Close and performs additional housekeeping.

With Microsoft's help we solved an issue related to this two years ago:

At aZ Software we discovered the difference between Closing and Disposing a connection object the hard way. Two+ years ago our first Vb.NET large enterprise application started leaking Windows resources when put under heavy testing by 100 end users. We were exclusively using Close on the connection object in our web pages. Within an hour we had heavy complaints and approx 75 minutes in the application crashed. Once we implemented the proper use of Close with Dispose each time a connection was no longer needed, the memory leak went away.

You have me wondering about version 1.1x. The decompiled Dispose code you provided for 1.1x is different than the decompiled Dispose code I provided for 1.0x. I also noticed that in VB.NET 2005 (unless changed by release date) that the SqlConnection's dispose method is changed again (hidden it appears).

I am also wondering if your experience is with web pages or web forms. The problem we had showed up in a web application where 100 end users were doing order entry which was generating over 3,000 connections an hour.

I will do deeper research and testing on the SqlConnection and its Close and Dispose classes on 1.0x and 1.1x. This is one of the most widely debated topics related to ADO.NET on the internet (as I am sure you know). There must be a definitive answer out there. Maybe the Marina theory is it ;-)

"Marina" <so*****@nospam.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl...
Ok. So looking at this code, Dispose does virtually nothing other then calling Close. It sets some internal variables to Nothing, and that's about it. We both agree that calling Dispose of the base class is not relevant here.

Here is the decompiled version of 1.1:

protected virtual void Dispose(bool disposing) {
ConnectionState local0;

if (disposing) {
local0 = this._objectState;
switch (local0)
case 1:
this.Close();
break;
this._constr = null;
}
this.Dispose(disposing);
}

So here it is very similarly, calls Close, sets a variable to null, and calls the base class Dispose.

So I am still not convinced how Dispose is any better then Close... Sorry!
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:eq**************@tk2msftngp13.phx.gbl...
Marina,

Thank you for pointing out a mistake in my post.

Where I wrote:
Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.
I should have written:
Calling the Dispose method of a SqlConnection object: 1. Checks to see if the connection is open and if it is it closes it.
2. Performs some resource housekeeping (see decompiled Dispose method for SqlConnection class below).
3. Calls Dispose on the base class (which does not have anything to do with the database connection).

Decompiled SqlConnection Class:

Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Begin Sub
Dim state1 As ConnectionState
state1 = Me._objectState
Switch (state1)
Case 0:
goto L_0035

End Case
Case 1:
goto L_0017
End Case
End Switch
goto L_0035
L_0017:
If disposing Then
Me.Close
Me._connectionOptions = Nothing
Me._cachedOptions = Nothing
Me._connectionString = Nothing
End If
L_0035:
MyBase.Dispose(disposing)
End Sub


By the way I am referring to Framework version 1.0.3705 in my posts to this thread.

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Marina" <so*****@nospam.com> wrote in message news:OH**************@TK2MSFTNGP11.phx.gbl... What other resources exactly does it release?

How could the base class's dispose (which is Component) have any database
specific code, since it is just a generic class to be inherited from?

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other

unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close

to
close the connection and then Dispose on the base class to release the

other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in

VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using'

keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a

connection
object is left up to us. We must 1) call Dispose explicity in our code OR

2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as

you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection

object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to

the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing

a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object

that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor



Nov 20 '05 #27
Cor - What version of the Framework are you using? 1.0x or 1.1x?

Thanks,

Mike
"Cor Ligthert" <no**********@planet.nl> wrote in message news:eY**************@TK2MSFTNGP10.phx.gbl...
Hi Marina,
So looking at this code, Dispose does virtually nothing other then calling Close.


This one I have tested, try it also if you want, when you disposing a connection you need to create the connection again and with a close that is not needed.

Cor


Nov 20 '05 #28
I should have said calling Dispose on the base class is irrelevant in terms of database resources.

It is relevant in terms of optimizing GC performance. The Dispose method of the base class which is the Component class suppresses Finalize on the object. If Finalize is not supressed the object takes longer to reach the point where the GC destroys it.

Public Sub Dispose()
Begin Sub
Me.Dispose(1)
GC.SuppressFinalize(Me)
End Sub

Rapidly opening and Closing thousands of SqlConnection objects (rather than disposing them bloats the GC Finalizer que. And, as we know, the longer an object lives the more likely it is moved to a different generation rather than flushed.

Disposing the connections instead would mean that the connections would never hit the GC Finalizer que. They would be destroyed much sooner. In the case of web pages they would almost never live long enough to be moved into a different GC generation.
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl...
I agree that calling Dispose of the base class is irrelevant

Experience has shown me that calling Close vs Dispose on the SqlConnection object itself (not the base class) gives different results in Framework version 1.0x. Close has it purpose and Dispose has its purpose. Close does some but not all of the things Dispose does. Dispose does a Close and performs additional housekeeping.

With Microsoft's help we solved an issue related to this two years ago:

At aZ Software we discovered the difference between Closing and Disposing a connection object the hard way. Two+ years ago our first Vb.NET large enterprise application started leaking Windows resources when put under heavy testing by 100 end users. We were exclusively using Close on the connection object in our web pages. Within an hour we had heavy complaints and approx 75 minutes in the application crashed. Once we implemented the proper use of Close with Dispose each time a connection was no longer needed, the memory leak went away.

You have me wondering about version 1.1x. The decompiled Dispose code you provided for 1.1x is different than the decompiled Dispose code I provided for 1.0x. I also noticed that in VB.NET 2005 (unless changed by release date) that the SqlConnection's dispose method is changed again (hidden it appears).

I am also wondering if your experience is with web pages or web forms. The problem we had showed up in a web application where 100 end users were doing order entry which was generating over 3,000 connections an hour.

I will do deeper research and testing on the SqlConnection and its Close and Dispose classes on 1.0x and 1.1x. This is one of the most widely debated topics related to ADO.NET on the internet (as I am sure you know). There must be a definitive answer out there. Maybe the Marina theory is it ;-)

"Marina" <so*****@nospam.com> wrote in message news:%2****************@tk2msftngp13.phx.gbl...
Ok. So looking at this code, Dispose does virtually nothing other then calling Close. It sets some internal variables to Nothing, and that's about it. We both agree that calling Dispose of the base class is not relevant here.

Here is the decompiled version of 1.1:

protected virtual void Dispose(bool disposing) {
ConnectionState local0;

if (disposing) {
local0 = this._objectState;
switch (local0)
case 1:
this.Close();
break;
this._constr = null;
}
this.Dispose(disposing);
}

So here it is very similarly, calls Close, sets a variable to null, and calls the base class Dispose.

So I am still not convinced how Dispose is any better then Close... Sorry!
"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:eq**************@tk2msftngp13.phx.gbl...
Marina,

Thank you for pointing out a mistake in my post.

Where I wrote:
Calling the Dispose method of a SqlConnection object in turn calls Close to close the connection and then Dispose on the base class to release the other unmanaged resources used by the object.
I should have written:
Calling the Dispose method of a SqlConnection object: 1. Checks to see if the connection is open and if it is it closes it.
2. Performs some resource housekeeping (see decompiled Dispose method for SqlConnection class below).
3. Calls Dispose on the base class (which does not have anything to do with the database connection).

Decompiled SqlConnection Class:

Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Begin Sub
Dim state1 As ConnectionState
state1 = Me._objectState
Switch (state1)
Case 0:
goto L_0035

End Case
Case 1:
goto L_0017
End Case
End Switch
goto L_0035
L_0017:
If disposing Then
Me.Close
Me._connectionOptions = Nothing
Me._cachedOptions = Nothing
Me._connectionString = Nothing
End If
L_0035:
MyBase.Dispose(disposing)
End Sub


By the way I am referring to Framework version 1.0.3705 in my posts to this thread.

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Marina" <so*****@nospam.com> wrote in message news:OH**************@TK2MSFTNGP11.phx.gbl... What other resources exactly does it release?

How could the base class's dispose (which is Component) have any database
specific code, since it is just a generic class to be inherited from?

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Calling Close on a SqlConnection object does not release the other

unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close

to
close the connection and then Dispose on the base class to release the

other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in

VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using'

keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a

connection
object is left up to us. We must 1) call Dispose explicity in our code OR

2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as

you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection

object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to

the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing

a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object

that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor



Nov 20 '05 #29
Hi Mike,

I use 1.1, I've seen you have not visit the dotnet newsgroups for a long time.

The more overall meaning for the regulars is to use dispose only when it helps.

In an other way of saying that, let the GC do his work an avoid extra roundtrips to the managed heap or whatever which gives nothing extra on a normal computer.

I hope this helps?

Cor

Nov 20 '05 #30
Hi Cor,

I have been very active in the DotNet community newsgroups with over 5,600 posts this past year so I didn't have as much time in the MS DotNet newsgroups. Also I added 22 developers to my company aZ Software Developers and have been busy training them. I plan to spend more time in the MS groups this coming year.

I agree with you, the 'regulars' you mention, Microsoft .NET Framework architects, and the most respected .NET Framework technical authors such as Jeffrey Richter - you may choose to use Dispose only where it helps. I like this excerpt from Applied .NET Framework Programming in Visual Basic .NET from Microsoft Press by Jeffrey Richter and Francesco Ballena:

"I recommend that you call Dispose or Close at a place in your code where you know you must must clean up the resource...or at a place where it is safe and you want to improve performance by preventing object promotion so that the Finalize method can run."

But when giving advice to the general .NET programmer population I recommend using Dispose on every .NET Framework object. I find 7 out of 10 (approx) programmers that approach me with peformance issues are getting sub-standard performance in their applications because they don't know the purpose of the Dispose pattern and "where it helps". Almost all .NET Framework experts both inside and outside Microsoft agree that calling Dispose has no negative effect so I say "why not". This is not my recommendation to expert programmers who know "where it helps" but it is my recommendation to anyone is not sure "where it helps".

My thinking is along the lines of what is found in the article "Take Charge of Garbage Collection" at: http://www.fawcette.com/vsm/2002_08/online/santanna/

Cheers,

Mike

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com


"Cor Ligthert" <no**********@planet.nl> wrote in message news:e7*************@TK2MSFTNGP11.phx.gbl...
Hi Mike,

I use 1.1, I've seen you have not visit the dotnet newsgroups for a long time.

The more overall meaning for the regulars is to use dispose only when it helps.

In an other way of saying that, let the GC do his work an avoid extra roundtrips to the managed heap or whatever which gives nothing extra on a normal computer.

I hope this helps?

Cor

Nov 20 '05 #31
Cor,
great thread! I am only sorry that I found it so late, it is always
good to see pooling posts by you and Marina, you truly do a great job.
If I dispose of it, would I not have to reinitialize it before I could

open it again?
You would have to reintialize


Sorry, this is not correct. SqlConnection Dispose does only two
things:
1) sets the connection string to ""
2) calls connection close.

After calling connection dispose you can set the connection string
property and call open again without problems. Close and Dispose truly
are the same thing.

When we first designed the API the ado.net objects did not implement
idisposable. When we became aware of the cool "using" construct that
the CLR team had added to the framework we decided that this would be
the perfect way to make sure pooled connections where user disposed.
We quickly slapped the idisposable interface into all of our objects
and everything was well until we got to the connection. Connections
are "different". The actual connection is on the server and we really
don't have access to it. What we do have is a handle to it that the
network layer gives us, we wrap this handle inside of an internal
connection class, then we assign this internal connection to the
SqlConnection when you call open. All that calling close or dispose
that is remove this assignment.

For the best reference to the managed pooler I have seen I would
recommend you read this excelent article by Gui Smith-Ferrier:
http://www.sys-con.com/dotnet/article.cfm?id=483
--
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.
Nov 20 '05 #32
Marina,
Great to see you and Cor again talking about pooling, you guys do a
great job in this newsgroup, thank you!

As far as the code below, the discussion was not whether to use close
or dispose, the issue that the code snippet I posted addresses was
whether to rely on GC to close the connections for you.

Try the repro code below after removing the following line:
conn.Close()

and you should see a much larger number of connections being created
as the GC cannot keep up with con.open.

The code that you have below is correct _as long as_ ExecuteReader
does not throw an exception. Every time that execute throws an
exception close will not be called and you will end up with the
behavior shown above. You can try this by adding a throw command in
your code after execute.

Bottom line:
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.

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

"Marina" <so*****@nospam.com> wrote in message news:<#$**************@TK2MSFTNGP10.phx.gbl>...
Yes, I guess I was, and I had the same advice then to.

I just tried reproducing what Angel did. Here is my code:

Sub Main()
Try
For i As Integer = 1 To 1000
ConnTest()
Next
Console.WriteLine("ok")
Catch ex As System.Exception
Console.WriteLine(ex.ToString())
End Try
Console.ReadLine()
End Sub

Private Sub ConnTest()
Dim conn As New SqlConnection(connString)
Dim command As New SqlCommand("Select count(*) from mytable", conn)
conn.Open()
command.ExecuteReader().Read()
conn.Close()
End Sub

Doing this, I would run it in debug mode, and pause it, at say i = 100,
etc. At all times, there was never more then 2 connections from this app on
my db server (I passed in an 'applicationname' parameter in the connection
string, to make sure I knew which connections were coming from my test app).

So as of yet, I have not been convinced, as the example that was given, does
not produce the results that it supposedly would.

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:uM**************@TK2MSFTNGP11.phx.gbl...
Hi Marina,

I thought that you where also in Angel Saenz-Badillos made some sample

code
to try it, however he did sound so sure (and I never saw him write strange
things) that I absolute did found it not needed to test it.

http://tinyurl.com/2sh6k

This is the thread. (and I saw you where in it too) :-)

Cor

Nov 20 '05 #33
Mike,
In the SqlConnection class we really are not releasing internal
resources on Dispose. Really, I mean it, it is true <g>. I am really
sorry to hear about your first large ADO.NET app but I can guarantee
that the fix did not come from calling dispose instead of close.

The most likely cause for the problem was code like this:
con.open
command execute
con.close

Perfect looking code, until execute throws an exception. Whith an
exception close never gets called, when this happens we leak the
resource and need to rely on GC to recover it for us. GC is too slow
for 100 users so we end up with massive resource consumption and a
crash very much like what you describe. Here is a thread where I
discuss this in detail:

http://tinyurl.com/2sh6k

The code above will not magically start working if we substitute
con.close for dispose, the problem in the first place was not that
close was not working but that it was not getting called at all. To
fix this problem we need to make sure that close or dispose (or both)
get called on a finalizer:

using (con)
command.execute

or
try
con.open
command.execute
finally
con.close

With either of these code snippets we guarantee that close or dispose
will get called even if execute throws an exception for whatever
reason.

Comments, thoughts, suggestions?
Thanks,

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

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:<#q**************@TK2MSFTNGP12.phx.gbl>...
Calling Close on a SqlConnection object does not release the other unmanaged
resources used by the object. It only closes the connection.

Calling the Dispose method of a SqlConnection object in turn calls Close to
close the connection and then Dispose on the base class to release the other
unmanaged resources used by the object.

A recent blog that helps clarify Connection Close and Dispose and their
relationship to releasing connections back into a connection pool:
http://ryanfarley.com/blog/archive/2004/03/17/444.aspx

We must be careful when comparing what happens in C# and what happens in VB.
In C# the 'using' keyword forces the Dispose method on an object to be
called. In this C# statement:

using(SqlConnection connection = new SqlConnection(...))
{
// code that uses the connection...
}

... Connection.Close() will automatcially be called because 'using' keyword
wraps a Try block around the code that includes a Finally clause which
contains Connection.Dispose().

We don't have a 'using' keyword in VB. In VB calling Dispose on a connection
object is left up to us. We must 1) call Dispose explicity in our code OR 2)
leave it to the GC (which does call Dispose when it finalizes a connection
object).

Explicitly disposing any object that uses unmanaged resources as soon as you
are finished with it does improve the efficiency of the GC. Read more at:
http://www.devcity.net/net/article.aspx?alias=gc_manage

Here are some facts about the ADO.NET SqlConnection class which can be
proven with a good profiling tool.

A. Close - Closes a connection, it does not dispose the Connection object's
other unmanaged resources. Connection is returned to connection pool.
B. Dispose - Closes and Disposes a connection. Connection is returned to the
connection pool (because Dispose calls Close first, then releases the
connection object's unmanaged resources.

At aZ Software we discovered the difference between Closing and Disposing a
connection object the hard way. Two+ years ago our first Vb.NET large
enterprise application started leaking Windows resources when put under
heavy testing by 100 end users. We were exclusively using Close on the
connection object in our web pages. Within an hour we had heavy complaints
and approx 75 minutes in the application crashed. Once we implemented the
proper use of Close with Dispose each time a connection was no longer
needed, the memory leak went away.

The bottome line - Close connections when you know your application will
Open and use the connection again - but when you are finished using the
connection object dispose it to release unmanaged resources.

Here is another article you may find helpful. It is not specific to
connections but it does empasize the importance of Disposing ANY object that
uses unmanaged resources.

http://www.fawcette.com/vsm/2002_08/...default_pf.asp
--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Hi Marina,

In full respect of you and mostly we agree, however Angel has told that

the
connection.dispose is one of the rare situations that has to be done
whenever and as soon as possible because of some behaviour of the
connection pooling.

And Angel is one of them who I always believe when it is about Ado.net.

Cor

Nov 20 '05 #34
Mike,
Excelent post! I could not agree more with this. Thanks for making the
point so clear. Do you mind if I point people to this post for
reference?

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

"Mike McIntyre [MVP]" <mi****@dotnetshowandtell.com> wrote in message news:<#Q**************@TK2MSFTNGP12.phx.gbl>...
Hi Cor,

I have been very active in the DotNet community newsgroups with over
5,600 posts this past year so I didn't have as much time in the MS
DotNet newsgroups. Also I added 22 developers to my company aZ Software
Developers and have been busy training them. I plan to spend more time
in the MS groups this coming year.

I agree with you, the 'regulars' you mention, Microsoft .NET Framework
architects, and the most respected .NET Framework technical authors such
as Jeffrey Richter - you may choose to use Dispose only where it helps.
I like this excerpt from Applied .NET Framework Programming in Visual
Basic .NET from Microsoft Press by Jeffrey Richter and Francesco
Ballena:

"I recommend that you call Dispose or Close at a place in your code
where you know you must must clean up the resource...or at a place where
it is safe and you want to improve performance by preventing object
promotion so that the Finalize method can run."

But when giving advice to the general .NET programmer population I
recommend using Dispose on every .NET Framework object. I find 7 out of
10 (approx) programmers that approach me with peformance issues are
getting sub-standard performance in their applications because they
don't know the purpose of the Dispose pattern and "where it helps".
Almost all .NET Framework experts both inside and outside Microsoft
agree that calling Dispose has no negative effect so I say "why not".
This is not my recommendation to expert programmers who know "where it
helps" but it is my recommendation to anyone is not sure "where it
helps".

My thinking is along the lines of what is found in the article "Take
Charge of Garbage Collection" at:
http://www.fawcette.com/vsm/2002 08/online/santanna/

Cheers,

Mike

--
Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com


"Cor Ligthert" <no**********@planet.nl> wrote in message
news:e7*************@TK2MSFTNGP11.phx.gbl...
Hi Mike,

I use 1.1, I've seen you have not visit the dotnet newsgroups for a
long time.

The more overall meaning for the regulars is to use dispose only when
it helps.

In an other way of saying that, let the GC do his work an avoid extra
roundtrips to the managed heap or whatever which gives nothing extra on
a normal computer.

I hope this helps?

Cor

--

Nov 20 '05 #35
Hi Angel,

I answer all your answers in this message.

All the three messages from you together: This last explanation about the
amount of users and the connections; the explanation about Idisposable; and
the one about exception, makes everything much clearer for me.

Is it not possible for you to make from those three messages one and put
that on MSDN, I think it will help many people.

Thank you very much.

Cor
Nov 20 '05 #36

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Daniel | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.