2 ways to close a connection

[Eric Sabine]

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

 

[Raymond Lewallen]

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]

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

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

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

 

[Mike McIntyre [MVP]]

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 [MVP]]

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

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

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

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

 

[Marina]

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]

Very helpful.

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

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

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

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

 

[Al Reid]

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

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.

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 Ligthert]

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]

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.

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

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.

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 Ligthert]

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

 

[Gary Hunt]

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

 

[Gary Hunt]

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

Very helpful.

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

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



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

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

 

[Al Reid]

Cor,

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

 

[Marina]

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]

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

 

[Marina]

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]

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

 

[Marina]

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

 

[Mike McIntyre [MVP]]

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/online/santanna/default_pf.asp