Verify connections are disposed in class library

Question

User115974766 posted

I'm working a class library that contains functions to retrieve information from the database.   I added "Implements IDisposable" to make sure each connection is properly being closed.  I added a break point to the dispose method but it is not being hit after the dataset has been returned from the function.  How can step thru the dispose method to verify the connection resources are being disposed?

 Private _disposed As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not _disposed Then
                ' Need to dispose managed resources if being called manually
                If disposing Then
                    If _conn IsNot Nothing Then
                        _conn.Dispose()
                        _conn = Nothing
                    End If
                End If
                _disposed = True
            End If
        End Sub
 
        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub

  Public Function GetCustomerDataSet() As DataSet

            Using cmd As SqlCommand = New SqlCommand("GetCustomers", conn)
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter(cmd)
                Dim ds As New DataSet
                da.Fill(ds)
                Return ds
            End Using
          

        End Function

 

Answer 1

User-851967432 posted

The SQLConnection class already implements IDisposable. Just wrap that around your SqlCommand as  you already have. Implementing IDisposable on your class won't affect the connections but only the disposal of your class.

Answer 2

User281315223 posted

If you are using a class that supports IDisposable then you should be able to use it within a Using statement (which will handle disposing of it properly).

Using blocks are most commonly seen when dealing with SqlConnections (or really any kind of connections) and provide a much safer / cleaner method for properly disposing of objects : 

'Your Using Statement'
Using sqlConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    
    'Your SQL'
    Dim sql = "SELECT * FROM [YourTableNameHere]"

    'Additional Using statement to dispose of the SqlCommand'
    Using sqlCommand As New SqlCommand(sql , sqlConnection)
        'Open your Connection'
        sqlConnection.Open()

        'Execute your query / code'
         sqlCommand.ExecuteNonQuery()
            
    End Using
End Using