A topic that comes up all the time is how to properly dispose of objects. To use IDisposable or GC or Connection Pool Manager?
For typical value and reference types, it’s simple. When they go out of scope, they are marked for deletion and the Garbage Collector wipes them out and frees up the memory they were using. When I say “scope”, I mean that every variable has a scope in which it can be used. For example, a private field in a class can be used in any member of that class. A variable declared inside of a method can only be used inside that method and goes “out of scope”, when the method returns. So again, for regular variables – this works fine.
IDisposable interface:
In .NET, the universal way to tell others “hey, I need to know when you are done with me, because I need to explicitly close some things down” – is to implement the IDisposable interface. Likewise, as a consuming developer – if you are using a class that implements IDispoable, you need to call .Dispose() to that it can clean up after itself properly.
Who uses IDisposable?
In general, there are 3 common uses that come up a lot:
- COM and Interop functionality.
- Streams of any sort (NetworkStream, FileStream, MemoryStream, etc)
- Database stuff (connections and commands, at least)
So we now know that we should always call .Dispose() on our database connections, but why – exactly?
When you call .Open() and .Close() on a database connection object, you aren’t actually opening and closing a connection. When you call .Open(), you are allocating a connection and .Dispose() de-allocates it. So if .Dispose() isn’t called – then every time .Open() is called, the connection pool manager can’t re-use the old connection and instead must create a NEW connection to the database.
This can become extremely significant in a web environment, where just a couple of users using a couple of pages can force the Connection Pool Manager to open HUNDREDS of actual connections to the database server. This is because you didn’t tell it you were done with those other connections, and Garbage Collection hasn’t de-allocated them for you yet either. This is bad for performance, it’s not scalable, it taxes the licenses and physical connection limits of the database server – and it’s simply not proper.
Even in a non-web environment (like a desktop app), this still is significant – because many parts of a form may make many calls to a database to populate the form. If those connections are managed incorrectly, similar problems can occur.
How do I call .Dispose()?
There are really only two proper ways:
- In a “finally” block (of a try/catch)
- Using the “using” statement
The “Finally” block:
When you have a try/catch statement – that is how you can catch an exception that occurs. But after the catch block, you can also have a “finally” block. The finally block will ALWAYS run, no matter what. Because of this, THAT is where you should call .Dispose() on your object. However, this isn’t the recommended approach because A) you (and future developers) have to remember to add the .Dispose() and B) it’s more code than the alternative (see below). Below is an example implementation:
C#:
SqlConnection connection = null;
SqlCommand command = null;
try
{
connection = new SqlConnection();
// Some exception handling should be added here to make sure
//the connection string isn't null or empty.
connection.ConnectionString =
ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString;
// Allocation the connection
connection.Open();
command = connection.CreateCommand();
// Use and execute this command...
}
catch (SqlException _exception)
{
Debug.WriteLine(_exception.ToString());
throw;
}
finally
{
command.Dispose();
connection.Dispose();
}
VB.NET:
Dim connection As SqlConnection = Nothing
Dim command As SqlCommand = Nothing
Try
connection = New SqlConnection()
' Some exception handling should be added here to make sure
' the connection string isn't null or empty.
connection.ConnectionString = ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
' Allocation the connection
connection.Open()
' Use and execute this command...
command = connection.CreateCommand()
Catch _exception As SqlException
Debug.WriteLine(_exception.ToString())
Throw
Finally
command.Dispose()
connection.Dispose()
End Try
The “using” statement (RECOMMENDED):
The using statement creates a scope for your disposable variable, then when you go out of scope, it *automatically* calls .Dispose() for you (even if an exception occurs).
C#:
try
{
using (SqlConnection connection = new SqlConnection())
{
// Some exception handling should be added here to make sure
// the connection string isn't null or empty.
connection.ConnectionString = ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString;
// Allocation the connection
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
// Use and execute this command...
}
}
}
catch (SqlException _exception)
{
Debug.WriteLine(_exception.ToString());
throw;
}
VB.NET:
Try
Using connection As New SqlConnection
' Some exception handling should be added here to make sure
' the connection string isn't null or empty.
connection.ConnectionString = _
ConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
' Allocation the connection
connection.Open()
Using command As SqlCommand = connection.CreateCommand()
' Use and execute this command...
End Using
End Using
Catch _exception As SqlException
Debug.WriteLine(_exception.ToString())
Throw
End Try
As you can see, the using statement approach is cleaner and it becomes much more obvious to other developers that may support your code. From the using statement example above, you can be 100% sure that “connection” variable isn’t used anywhere else. In addition, this technique doesn’t require a developer to remember (or even know!) about calling .Dispose() in the finally block.