ADO.Net and Garbage Collection

[NetNut]

Hello Everyone,

I have received mixed reviews on this question so maybe I can get some more info.

Situation:

You have a ASP.Net application that is highly used and extremely data cetric. There is a large number of anticipated users and you want to make sure that all resources are regained as soon as possible.

Which code snipplet is accurate or the best choice for the situation above:

Dim Cn As SQLClient.SqlConnection = New SqlCilent.SqlConnection(ConfigurationSettings.AppSettings("SQLConn"))
CN.Open

Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("@sp_Test", Cn)
.
.
.
Answers
----------------------------------------------------------------
A.
Cn.Close()
Cmd.Dispose()
Cn.Dispose()
Cn = Nothing
Cmd = Nothing

B.
Cn.Close()

Cmd.Dispose()
Cn.Dispose()

With GC
.SuppressFinialize(Cmd)
.SuppressFinialize(Cn)

.Collect()
End With

C.
Cn.Close

Cn = Nothing
Cmd = Nothing

--------------------------------------------------------------------------------
Please feel free to add something if you have any better techniques

[Aaron Weiker]

NetNut wrote:

> Dim Cn As SQLClient.SqlConnection = New SqlCilent.SqlConnection(ConfigurationSettings.AppSettings("SQLConn"))
> CN.Open
>
> Dim Cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("@sp_Test", Cn)
>

> C.
> Cn.Close
>
> Cn = Nothing
> Cmd = Nothing

I always use C. For more information check out
http://blogs.sqladvice.com/dpenton/archive/2004/03/16/786.aspx

--
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/

[Marina]

Neither.

Cn.Close is enough. The important thing is to close the connection, to
release it back into the pool. As soon as the variable goes out of scope,
the GC knows to collect the object. Setting it to Nothing doesn't do
anything, that was something you had to do in VB6.

"NetNut" <anon...@discussions.microsoft.com> wrote in message
news:7FF5F715-2614-4FDD...@microsoft.com...

[Aaron Weiker]

Aaron Weiker wrote:

> I always use C. For more information check out
> http://blogs.sqladvice.com/dpenton/archive/2004/03/16/786.aspx
>

Yeah, I forgot to mention, I also through in a Dispose to Connection. I
program using C# so I just use the "using" statement which handles this
automatically for me.

[Cor]

Hi,

Same answer as Marina here,

Cor

[Angel Saenz-Badillos[MS]]

NetNut,

Good question but you did not include the guaranteed to work catch all
option:

D: call dispose on a finally block for all IDisposable objects.

Using the "using" contruct or the equivalent try finally block is the only
way to guarantee clean up, take this example:

con.Open()
//use connection here, throws exception
//enter any of the options (A, B or C) here.

Have you cleaned up? Lets run this:
The connection is opened
I use the connection
an exception is thrown, exit out of context.
Done.

No clean up, sorry. You will handle the exception at some level but your
connection has leaked and you will have to rely on the urt garbage collector
to clean up for you, exactly what you want to avoid.

try{
con.Open()
//use connection here, throws exception
}finally{
con.Dispose()
}
What about now?
The connection is opened
I use the connection
an exception is thrown
Finally is guaranteed, I dispose the connection
exit out of context.
...and the crowd goes wild!

This try finally block is exactly equivalent to the "using" contstruct which
is quite a bit easier on the eyes. Make sure that all objects that implement
IDisposable are implemented with the using or with a try finally dispose
block.

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

"NetNut" <anon...@discussions.microsoft.com> wrote in message
news:7FF5F715-2614-4FDD...@microsoft.com...

[Cor]

Hi Angel,

>
> No clean up, sorry. You will handle the exception at some level but your
> connection has leaked and you will have to rely on the urt garbage
collector
> to clean up for you, exactly what you want to avoid.

Can you explain your words a little bit more expliciet, from this I
understand that the garbage collector is unreliable?

Cor

[Mary Chipman]

PMJI, Angel's point was that you should use the "using" contruct or a
try finally block to guarantee clean up.

--mary

[Cor]

Hi Mary,

> PMJI, Angel's point was that you should use the "using" contruct or a
> try finally block to guarantee clean up.

I think Angel did not mean what he has written, so I try to help him to
correct that.

You know this stays very long in Google.

Cor

[Miha Markic [MVP C#]]

Inline...

"Mary Chipman" <mc...@nomail.please> wrote in message
news:vajo50p49tm807kci...@4ax.com...

> PMJI, Angel's point was that you should use the "using" contruct or a

> try finally block to guarantee clean up *when you want* :).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

[Angel Saenz-Badillos[MS]]

Cor,
I make more than a fair share of mistakes when posting to the newsgroups,
this is especially true when I post at 1am. It is very possible that I need
correcting, however if your objection is to the following statement:

[angel]

>> No clean up, sorry. You will handle the exception at some level but your
>> connection has leaked and you will have to rely on the urt garbage
collector
>> to clean up for you, exactly what you want to avoid.

[cor]

>Can you explain your words a little bit more expliciet, from this I
>understand that the garbage collector is unreliable?

I DEFINITELLY mean that you should not rely on the garbage collector to
clean up pooled connections for you. At best you are going to open an
unnecesary number of connections and you will start seeing the "Max pool
size reached" exception, at worst your app will not throw this exception and
your customers will complain that you'r app is slow. GC is not unreliable,
it will clean up the resources properly, however it is my experience that
you will use connections faster than they will get GC'd. Increasing max pool
size is not the answer, you are forcing your server to work harder.

Try running the following with and without closing the connection while
monitoring the number of connections on the server (sp_who or server side
performance counters)

using System.Data.SqlClient;
using System.Data;
using System;
namespace DataViewer.Repro {

public class Repro {
public static int Main(string[] args) {
Repro r= new Repro();
for (int i=0; i<100;i++)
r.run();
return 1;
}
public void run() {
Console.Write(". ");
SqlConnection sqlconnection1 = new
SqlConnection(<connectionstring>);
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
sqlcommand1.CommandText = "exec sp_who;exec sp_who;exec
sp_who;exec sp_who; exec sp_who";
SqlDataReader sqldatareader1 =
sqlcommand1.ExecuteReader(CommandBehavior.Default);
sqldatareader1.Read(); // true
//sqlconnection1.Close();
}
}
}

Just a quick shout out to Mary and Miha, thanks for your posts, it is
amazing to see the community that you guys are building here.

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

"Cor" <n...@non.com> wrote in message
news:#fUr7mpD...@TK2MSFTNGP09.phx.gbl...

[Angel Saenz-Badillos[MS]]

I was thinking about what you said about these posts being on google for a
long time, I should have posted what the behavior of the code below would
look like:

calling connection close: You will see only one connection.
not calling close and letting GC collect the resources for you: You will see
anywhere between 15-99 connections depending on how big your loop is.

This shows how _extremely bad_ not closing your connection is, however the
code below is bad even whith the sqlconnection.close uncommented. What if
your command execute throws an exception? The following code shows why you
should use a try finally or a "using" block:

using System.Data.SqlClient;
using System.Data;
using System;

namespace DataViewer.Repro {

public class Repro {
public static int Main(string[] args) {
Repro r= new Repro();

for (int i=0; i<100;i++){
try{
r.run();
}catch(Exception){} //ignore

}
return 1;
}
public void run() {
Console.Write(". ");
SqlConnection sqlconnection1 = new
SqlConnection("<connectionstring>");
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "raiserror('myerror',16,1)";

SqlDataReader sqldatareader1 =
sqlcommand1.ExecuteReader(CommandBehavior.Default);
sqldatareader1.Read(); // true

sqlconnection1.Close();
}
}
}

This code calls connection close, but monitoring the number of connections
to the server will show that it never gets called (I cheated, the
CommandText is a raiserror, but in a real application your code could throw
for any number of reasons). You are still opening 20+ connections.

Finally for archives sake here is the correct way to make sure you dispose
your SqlConnections:

using System.Data.SqlClient;
using System.Data;
using System;

namespace DataViewer.Repro {
public class Repro {
public static int Main(string[] args) {
Repro r= new Repro()'

for (int i=0; i<300;i++){
try{
r.run();
}catch(Exception){} //ignore

}
return 1;
}
public void run() {
Console.Write(". ");

using (SqlConnection sqlconnection1 = new
SqlConnection("<connection string>")) {

sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "raiserror('myerror',16,1)";

SqlDataReader sqldatareader1 =
sqlcommand1.ExecuteReader(CommandBehavior.Default);
sqldatareader1.Read(); // true

sqlconnection1.Close();
}
}
}
}

the "using (SqlConnection sqlconnection1 = new SqlConnection("<connection
string>")) {" line guarantees that SqlConnection.Dispose is called even
after the raiserror and you will only use 1 connection as expected.

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

"Angel Saenz-Badillos[MS]" <ang...@online.microsoft.com> wrote in message
news:uLpGlvtD...@TK2MSFTNGP10.phx.gbl...

[Cor]

Hi Angel,

You are one of those who do not have to prove for me, I saw to much good
messages from you in this newsgroup.

It was just the text which issolated could bring people on wrong ideas in my
opinion.

Cor