You know, a SqlConnection object, a SqlCommand object, a SqlDataReader
object and many other data access objects, have a Close() method and a
Dispose() method.

What are their differences?

Q1: Should we call both Close() and Dispose() on an object in finally
block? If not, then which one should we call and why?

Q2: If it makes sense to call both, does the order of calling them
matter?

Q3: Does the order of calling either Close() or Dispose() or both
matter on a SqlConnection object, a SqlCommand object which contains
this connection, a SqlDataReader object which is a result of
ExecuteReader of this SqlCommand object?

Thank you.

"gnewsgroup" <gnewsgroup (AT) gmail (DOT) com> wrote


Close() closes the connection and returns it to the pool, leaving the object
in memory until the garbage collector decides it can destroy it - Dispose()
removes it from memory by first calling Close:
http://blog.devstone.com/aaron/archi...06/03/184.aspx

No.

Neither - use the "using (.......) {.....}" syntax as described in the above
article.

See above - Dispose calls Close anyway...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

On Dec 18, 10:48 am, "Mark Rae [MVP]" <m... (AT) markNOSPAMrae (DOT) net> wrote:
Thank you. So, I guess if the connection object is gonna be used
soon, we better simply call Close() to improve the performance.

I am aware of the using syntax, but have never seriously used it.

I read the article which you referred to above. Very nice, but I am
sorta confused by this:

<quote>
When Close() is called (or Dispose() by virtue of what it does) the
connection is not actually closed but is returned to the database
connection pool - remember that the connection pool is a collection of
SQL Connections, not SqlConnection objects.
</quote>

Does the author imply (by way of the parenthesized part) that Close()
and Dispose makes no difference in terms of returning the connection
object to the conection pool?

"Mark Rae [MVP]" <mark (AT) markNOSPAMrae (DOT) net> wrote

This is a bit misleading, as the following are functionally equivalent:

using(SqlConnection conn = new SqlConnection())
{
// work here
}

SqlConnection conn = new SqlConnection();
try
{
conn.Open();
// work here
}
finally
{
conn.Dispose();
}


The main advantage of using, here, is it is more succinct and therefore
faster to code. The advantage of the try ... finally is you have the option
of adding a catch without recoding the entire instantiation and use.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

"gnewsgroup" <gnewsgroup (AT) gmail (DOT) com> wrote


No - use the using syntax...

I recommend that you do...

Yes.


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld (AT) comcast (DOT) netNoSpamM> wrote in
message news:uvP4vVZQIHA.5524 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

True.

Indeed, which is why I recommended it... :-)


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

"gnewsgroup" <gnewsgroup (AT) gmail (DOT) com> wrote

I generally use this pattern:

try
{
conn.Open();
//work here
}
finally
{
conn.Dispose();
}

The Dispose() will call Close on the object and mark it for cleanup. As
connections are returned to the pool, for reuse, they are dead from your
perspective, but still have an underlying lifetime. Not a big deal.

The other option is using:

using(SqlConnection conn = new SqlConnection())
{
//work here
}

This automatically disposes the object for you and makes for very clean
syntax. I generally go to the extra work of setting up the try ... finally,
however, as it allows me to add a catch.

It doesn't make sense, but you have to call Close() first if you do.

Try to clean up any objects as they are on the stack. With the command,
there really is no clean up as killing connection will solve it. You have
the option on the DataReader, if you so desire.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

"Mark Rae [MVP]" <mark (AT) markNOSPAMrae (DOT) net> wrote


I find too many cases where I temporary have to add a catch, so I am a
creature of habit. ;-)

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld (AT) comcast (DOT) netNoSpamM> wrote in
message news:uWMBwdZQIHA.3940 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

Yeah, me too...

I have a DAL that I use for everything, so I don't really need to think
about this on a daily basis...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net