.Net SQL Server Object Cleanup

[PTNL]

I'm currently using this block of code that gets called in the "Finally" blocks of my data handler class for connecting to SQL Server (2008):

private void CleanupPrivateComplexObjects()
{
    if (this._sqlConnection != null)
    {
        if (this._sqlConnection.State != ConnectionState.Closed)
        {
            this._sqlConnection.Close();
        }
        this._sqlConnection.Dispose();
        this._sqlConnection = null;
    }
    if (this._sqlCommand != null)
    {
        this._sqlCommand.Dispose();
        this._sqlCommand = null;
    }
    if (this._sqlAdapter != null)
    {
        this._sqlAdapter.Dispose();
        this._sqlAdapter = null;
    }
}

What I'm wondering is whether the order I'm cleaning up objects matters. Currently, it's SqlConnection, SqlCommand, and SqlAdapter. Is there a better or preferred order for cleaning up private database connection objects?

 

[Wiseguy2001]

What I'm wondering is whether the order I'm cleaning up objects matters. Currently, it's SqlConnection, SqlCommand, and SqlAdapter. Is there a better or preferred order for cleaning up private database connection objects?

The .dispose() method, doesn't free up memory, it just marks it ready for collection. What it does do however is release non-memory resources (such as ports, file handles).

The garbage collector may take a long time until it is called, there are times when calling GC.Collect() manually can lower a programs memory footprint by hundreds of megabytes (my idea of efficiency and the GC's differ sometimes).

Setting the variable to null isn't needed, it doesn't actually anything in this case. Just calling .dispose() is all you need.

As for using using{} or try/ finally, it doesn't matter, the .net compiler will output the same code (try/ finally) in the end, in the same manner that switch a statement is really a stack of "else if"s. Just write which ever code you prefer.

EDIT:-

if (this._sqlConnection.State != ConnectionState.Closed)
        {
            this._sqlConnection.Close();
        }

Isn't needed, as mentioned above. Dispose() will do it anyway.

 

[Lunknumb]

Setting them null is just inheritance from pre-.Net VB, when it had RAII garbage collection. Setting an object to nothing would immediately reduce the reference counter and run the desctuctor when it got to 0, which in turn would clean up resources and free memory. It's cleaner to just just dispose to get rid of resources and let them go out of scope for GC nowadays.

The best practise would be to implement IDisposable and do the cleanup you're doing in Dispose, so your class can be used in a using block. (minus the setting to null and checking status stuff)

If it's re-usable instance with both "open" and "close"-type methods, than it's a good idea to have the "open" return an inner class that implements IDisposable that does the cleanup. Open returns the thing you need to close again, so it's "using-friendly" and all the defensive state managment code is neatly encapsulated inside the class.

 

[PTNL]

IDisposable is implemented on the Dat Handler class, which cleans up the private complex objects of the class by calling the method listed above. This is done so the Business layer can take advantage of "using" calls to the Data Handler class.

Is there any gain by reversing the order of the objects that get cleaned up, ie: first the SqlDataAdapter, then SqlCommand, and finally the SqlConnection?