connection.close vs. connection.dispose

[Scott M.]

I understand that the dispose method releases umnanaged resources, but I am
trying to understand what those resources are when it comes to a database
connection. If my connection is closed, then what unmanaged resources does
the connection object still have?

 

[Sahil Malik [MVP C#]]

There is no hard and fast rule that the Dispose method must release
unmanaged resources. All dispose means is IDisposable is implemented, and
then it is upto the implementer what he wishes to do there. For all you know
he could allocate even more resources in dispose (Bad implementation of
course).

Re: SqlConnection.Dispose - in addition to closing the connection, it also
clears stateful nature of the SqlConnection instance, like connection string
etc.Beneath this layer, in SqlConnection atleast, there is a connection
pooling mechanism, which eventually holds actual physical database
connections. Again, they aren't really unmanaged resources, but non .NET
resources nonetheless. Lest I cause any confusion SqlClient has TDS parsing
built into .NET code, so technically in this scenario - there wasn't any
unmanaged resource.

So in short, Dispose does not **have** to release unmanaged resources - and
by convention it does cleanup. Thats exactly what SqlConnection.Dispse
does - "clean up clean up everybody do your share"

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Scott M.]

Thanks Sahil. I do understand the concept of IDisposable when it comes to
custom classes, but my question is strictly about the ADO.NET Connection
class(es).

I'm trying to understand from your message exactly what it is about a
connection that would need to be released by using Dispose though. Are you
saying that calling Dispose on a connection would remove it from the
connection pool? And, what exactly do you mean by "the stateful nature of a
connection string"? How can a connection string be stateful? As I pondered
earlier, if I close my connection, how could the .NET connection object
still use any DB resources?

Thanks.

 

[Sahil Malik [MVP C#]]

Calling Dispose or Close marks the underlying physical connection as "Not in
use" - but doesn't really close it. A "Not in use" connection that isn't yet
physically closed is thus available for pooling.

Therefore - calling dispose would return a connection to the pool.

Re: the stateful nature - the stateful nature is of the SqlConnection
object. A "closed" SqlConnection can be reopened, but if you dispose it, you
can't reopen it again.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Cor Ligthert [MVP]]

Scott,

Pablo Castro writes this in my opinion the most simple in this newsgroup.

http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/msg/515919344c3c556a

See as well his follow up message.

Cor

 

[Scott M.]

Calling Dispose or Close marks the underlying physical connection as "Not
in use" - but doesn't really close it. A "Not in use" connection that
isn't yet physically closed is thus available for pooling.

Therefore - calling dispose would return a connection to the pool.

Then why bother calling Dispose on a connection object if it doesn't do any
more to free up any more resources than Close does?

Re: the stateful nature - the stateful nature is of the SqlConnection
object. A "closed" SqlConnection can be reopened, but if you dispose it,
you can't reopen it again.

Isn't this true of all the connection objects as well?

Thanks,

Scott

 

[Sahil Malik [MVP C#]]

Then why bother calling Dispose on a connection object if it doesn't do

any more to free up any more resources than Close does?

Call either Close or Dispose. Dispose will close an open connection for you,
or simply leave a closed conn as is. So as long as you've called Close, not
calling Dispose is not a huge big deal.

Isn't this true of all the connection objects as well?

MOSTLY The exact implementation depends on the .NET data provider though.
So I can't say 100% confidently that nobody will ever break this rule.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________