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
35  11105 
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
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
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
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
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
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
>
>
>
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
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
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
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
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
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
> >
> >
> >
>
>
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
--
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Daniel |
last post by:
TcpClient close() method socket leak
when i use TcpClient to open a connection, send data and close the TcpClient
with myTcpClientInstance.Close(); it takes 60 seconds for the actual socket
on...
|
by: InvisibleMan |
last post by:
Hi, I feel a little dumb for asking this (considering im writing
TSQL) but there doesn't seem to be any definitive answers on the
search engines...
Okay I understand that if you open the ADO...
|
by: Asha |
last post by:
greetings, besides using objconn.State() to find out if a sql connection is
close or open, is there any other way to accomplish this task? thanks in
advance.
anywhere is sql server or the machine...
|
by: Greg Strong |
last post by:
Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |
