Conn.Close & Conn.Dispose

[Simon Harris]

Hi All,

Do I need to use both Conn.Close() & Conn.Dispose() when I have finished
with an SQL connection?

 

[Karl Seguin]

This can lead to heated debate, hopefully I'll answer first and then you can
ignore everyone else

Seriously though, this is what Dispose does (actual code):
switch (this._objectState)
{
case ConnectionState.Open:
{
this.Close();
break;
}
}
this._constr = null;


as you can see, all it does it call Close(). So you might be tempted to say
"i should just call close and save some stack". However, there is a dipose
pattern which you should follow. while you can call one, or the other or
both to achieve the same result, this behaviour could change with future
release. One day (for example 2.0) Dispose might do more...and then you'd
have to go through all your code and make sure you had called Dispose.
Additionally, languages like C# are dispose-aware thanks to the using
keyword.

As far as i'm concerned, those two points are enough to make sure I always
call Dipose on classes which inherit IDisposable.

As for close, my personal feeling is that it doesn't need to be called if
you are calling Dispose. And, since it's better to be consistent, I'd say
never call it. Why? well, none of the .net language are close-aware, and by
definition of what Dispose does, it would be a mistake from the class
designer (ie, microsoft) to implement cleanup functionality in close which
Dispose also woudlnt' do (possibly simply by running close, as it currently
does).

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[rkvajrala]

If You use Connection.Dispose(),Dispose method itself closes the
connection and make the reffrence of the connection object to null. Its
fine if you just use the Connection.Dispose().


-Ram

 

[Simon Harris]

Thank you both for your answers. I think I'll just use dispose from now on.

Whilst were on the subject, we had a situation recently where an application
was not releasing connections back into the connection pool, even though we
were calling both .close and .dispose. I'm afraid I dont have any more
details to hand, but I do know one of our developers spent a week on the
problem, eventually giving in and turning off connection pooling on the
server.

Has anyone come across this?

Thanks again,
Simon.

 

[Karl Seguin]

If you had integrated security (windows login) and windows authentication
on, I'd expect it to happen. Else I wouldn't know without further detail.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[Web Team @ Borough of Poole]

Hi,

Yes, we are using integrated security (It's an Intranet). How is this
related?

Thanks.

 

[Karl Seguin]

Check out:
http://weblogs.asp.net/sjoseph/archive/2005/03/23/395601.aspx

and do a search for "integrated security" ...connections are pooled based on
their connnection string, and w/integrated security the connection string is
different per user, hence no pooling happens cross-user. My knowledge of
this is very limited, just what I've read in passing...not sure if it's the
problem you saw, but it's the only thing I know..

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)