More on Dispose

Wednesday, March 17, 2004 12:47 PM Filed under:

General

More on Dispose

I see things surface from time to time on the question of Dispose vs. Close on objects that implement IDisposable. I posted a while back my ideas on why calling Dispose is not only correct, but necessary. To me the facts are clear, but you'll find the question arise frequently. There seems to be two areas of FUD (fear, uncertainty, doubt) surrounding the use of Dispose.

0) Not knowing what Dispose is, or why to use it. Why is it that any .NET book with a section on data access completely omits any explanation of Dispose? In fact, any code samples you'll find in the book (even samples you'll see in the Framework QuickStarts or on MSDN) will simply call Close and never Dispose? I have no idea why. By definition alone, the purpose of the Dispose method of classes that implement IDisposable is to perform application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. Not sure why that would be something you wouldn't want to happen immediately. When I am done with something that holds references to unmanaged resources, I want those resources released.

1) In the case of connections, fear that calling Dispose on a connection removes the connection from the pool. This is not correct. The connection pooler will remove a connection from the pool if the connection lifetime has expired, or if the pooler detects that the connection with the server has been severed. Calling Dispose does not produce this effect. Calling Close on the connection will return it to the pool. However, calling Dispose both returns it to the pool and releases unmanaged resources from the active process. I think it is the statement that “unmanaged resources are released” is where the confusion comes from about Dispose not returning connections to the pool. You can read more on connection pooling in the MSDN article: Connection Pooling for the .NET Framework Data Provider for SQL Server.

Comments

Jason Buss 3/17/2004 1:45 PM

Jeez Ryan, we just talked about this today! Quite a coincidence, huh!

;)

Anyway, I appreciate the clarification.
.NET 247 4/16/2004 2:33 PM
Brad Mastaler 1/7/2005 1:02 PM

Hey, Ryan,

I had no FUD about the fact that calling Dispose() is a good practice, but then reading Visual Studio .NET 2002's online help created some confusion.

I don't think it's the statement "unmanaged resources are released" that creates the confusion. I ended up reading this entry in your blog because there's a statement in VS2002's included help for the SqlConnection.Displose() method that says "The Dispose method calls Close, and removes the SqlConnection from the connection pool." I thought that was unusual, so I wanted to verify the statement. (MS's technical writers have been known to be wrong).

On MSDN, I can't find a page with the same statement. The SqlConnection.Dispose() method page links you to the Component.Dispose() page.

In any case, there's one easy way to find out (which I haven't tried yet). Perfmon has a set of .NET SqlClient counters that track connection pools and the number of connections in them. Test code could be created to settle this.
Alex Lowe 3/8/2005 4:29 PM

The documentation is wrong, unfortunately. The error slipped past the QA team when they were pushing for RTM but I've heard Scott Guthrie say that the docs are wrong so I know it to be true. Dispose does NOT remove a connection from the pool unless it should (i.e. it expired).
Just another happy user 11/17/2005 2:45 AM

Upon some googling, i found your explanation, wich clarifies things quite a bit. Short and concise. Right to the point. I'm still a beginner (moved from network administration to programming about 2 months ago) but the code is getting bigger and bigger, and i guess i'm getting the hang of it. So ... thanks for your explanation.
Brian 12/16/2005 5:50 AM

My typical database code looks something like this:

cn.Open();
da.Fill (myRecords);
cn.Close();
cn.Dispose();

Is this overkill? I'm wondering if I need to call Close() at all, since Dispose() is going to close the connection for me. Also, should I dispose the command object as well? I've heard that would cause the connection to not be pooled.

Thanks for any info!
Ryan Farley 12/16/2005 7:23 AM

Brian,

Yes, that is overkill. It doesn't hurt anything, just redundant. A better approach IMO is to wrap it (or anything that implements IDisposable) in a using block. This way dispose is automatically called for the object.

ie:

using (OleDbConnection cn = new OleDbConnection(connstr))
{
cn.Open();
using (OleDbCommand cmd = new OleDbCommand(sqlstatement, cn))
{
//...do whatever
}
}

Much cleaner in my book.

-Ryan
Zinahe 12/27/2005 12:14 AM

Brian,

In your case I think you could just forget about opening the connection as the SqlDataAdapter object manages that.

da.Fill (myRecords);

-Zinahe A.
John 2/9/2006 8:42 PM

The RegistryKey class implements from the IDisposable interface but does not have a dispose() method. It does have a close method. Do you know why this is?

John
Ahmet Kaymaz 1/2/2008 11:34 AM

when Dispose() is called, it first calls Close() and than it nulls out the connection string.

C#, SQL, VB.NET örnek ve kodlari
Ahmet Kaymaz
http://www.ahmetkaymaz.com
Yangxin 3/2/2008 6:01 PM

Actually I think it depends on the class you are using. For example, FileStream class's base class Stream has

public void Dispose()
{
this.Close();
}

public virtual void Close()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}

In this case, Dispose() and Close() are equivalent. After the call to either of them, the object is destroied and cannot be resued. This is different from sqlConnection which has

protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}

It's Close() doesn't destroy the object itself unlike the Stream class.This means you can reuse the sqlConnection object and reopen the connection.

However, I think there is little advantage of reusing of the object after you call the close(). To make code clear and consistant, using the "using" statement should be a elegant solution.
jzq 7/16/2008 12:07 PM

great great clarifications. really really helpful! Thanks a lot.