2 ways to close a connection

  • Thread starter Thread starter Eric Sabine
  • Start date Start date
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,

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
 
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?
 
Marina,

Thank you for pointing out a mistake in my post.

Where said:
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.
Click to expand...

I should have written:
Calling the Dispose method of a SqlConnection object:
Click to expand...
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
 
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!
Marina,

Thank you for pointing out a mistake in my post.

Where said:
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.
Click to expand...

I should have written:
Calling the Dispose method of a SqlConnection object:
Click to expand...
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
 
Hi Marina,
So looking at this code, Dispose does virtually nothing other then calling Close.
Click to expand...

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





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!
Marina,

Thank you for pointing out a mistake in my post.

Where said:
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.
Click to expand...

I should have written:
Calling the Dispose method of a SqlConnection object:
Click to expand...
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
 
Cor - What version of the Framework are you using? 1.0x or 1.1x?

Thanks,

Mike
So looking at this code, Dispose does virtually nothing other then calling Close.
Click to expand...

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

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





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!
Marina,

Thank you for pointing out a mistake in my post.

Where said:
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.
Click to expand...

I should have written:
Calling the Dispose method of a SqlConnection object:
Click to expand...
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
 
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




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.
open it again?
You would have to reintialize
Click to expand...

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.
 
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,
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?
 
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
 
Back
Top