Disposing of memory leaks.

One of the biggest causes of memory leaks that I find from time to time is from forgetting to dispose of some SQLConnection object. Even if the connection is closed, the SQLConnection object seems to stay alive forever, which keeps any form objects alive as well.

This happens in VB a lot if you forget to call the Dispose method of the SQLConnection object or if you don’t use the Using keyword. While cleaning up code, the best way to get in the habit of doing it, seems to be changing everything into a Using statement so I get in the habit of using it…
Instead of:
view plaincopy to clipboardprint?

1. Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. myConnection.Close()
7. myConnection.Dispose()
8. myCommand.Dispose()

Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myConnection.Close()
myConnection.Dispose()
myCommand.Dispose()

Do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Using myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. End Using
7. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Using myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
End Using
End Using

Since the IDisposable interface on the SQLConnection object will automatically close open connections, there is no need to close it… However, if you decide to do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
2. , New SqlClient.SqlConnection(ConnectionString))
3. myCommand.Connection.Open()
4. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
5. myCommand.Connection.Close()
6. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
, New SqlClient.SqlConnection(ConnectionString))
myCommand.Connection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myCommand.Connection.Close()
End Using

Make sure you close the connection first… Although, I don’t recommend doing it that way, because the IDisposable interface of the SQLCommand object does not call the IDisposable interface of the SqlConnection object that it holds. I’ve read that all the SqlConnection object’s IDisposable interface does is close an open connection, I wouldn’t trust that completely and dispose of it properly.

About these ads