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:

    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:

    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:

    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.