Previous | Index | Next 

[PRB] Connection and Recordset objects should be closed explicitly

Actually, COM Interop is such a magic that it's easy to forget that all the COM objects that we manipulate from .NET aren't the "real" COM objects. Instead, they are .NET wrappers that redirect all calls to the actual COM object by means of COM Interop. This fact has many implications. Consider for example this VB6 code:

        Function GetRecordCount(ByVal cn As ADODB.Connection, ByVal sql As String) As Integer
            Dim rs As New ADODB.Recordset
            rs.Open sql, cn
            GetRecordCount = rs.RecordCount
        End Function

As you see, the Recordset object isn't closed explicitly, but it isn't a problem in VB6 because all COM objects are automatically closed when they are set to Nothing or go out of scope. However, if you translate this code to VB.NET you are in trouble, because nothing happens when the method exits. In other words, you'll have an open recordset hanging somewhere in memory and cause unexplainable errors later in the application. For example, you'll get an error when you'll later try to open another recordset on the same connection.
We know that some customers had to work around this issue by enabling the MARS option with SQL Server, which enables multiple active recordsets on any given connection. This might be a viable solution if you are working with SQL Server 2005 or 2008, but VB Migration Partner offers a better way to handle this case: use the AutoDispose pragmas:

        '## AutoDispose Force

When this pragma is used, all disposable objects are correctly disposed of when they go out of scope. In this case, VB Migration Partner emits the following code:

        Function GetRecordCount(ByVal cn As ADODB.Connection, ByVal sql As String) As Integer
            Dim rs As New ADODB.Recordset
            Try
                rs.Open(sql, cn)
                GetRecordCount = rs.RecordCount
            Finally
                SetNothing6(rs)
            End Try
        End Function 

where the SetNothing6 helper method takes care of orderly invoking the Close or Dispose method, if necessary.

 

Previous | Index | Next