During my recent VB6 to VB.NET Forms upgrade adventure, I had to wade through quite a bit of code that, while still functional, should be upgraded further when time permits. Most of this code is used for data access, and being that it was written back in the VB6 / Classic ASP days, Active Data Objects (ADO) was used rather than ADO.NET, which is the newer .NET equivalent of ADO.
A common feature of ADO which was widely used in this project was the Recordset. An ADO Recordset Object is defined by Microsoft as an object that “represents the entire set of records from a base table or the results of an executed command. At any time, the Recordset object refers to only a single record within the set as the current record.” (Link)
One of the problems in using the recordsets object with VB.NET and Oracle is that if the recordset is not closed and garbage collected once the program is done with it, an Oracle error reporting too many open cursors (ORA-01000) is often thrown. Many Web sites say that one possible fix to this error is to increase the number of allowed open cursors, but this solution only hides bad code. Instead, you must make certain that the object is closed, set to Nothing, and garbage collected.
To resolve this problem, I created a module that contains methods to destroy serveral types of objects: ADODB Connections, ADODB Recordsets, Excel applications, and Scripting FileSystemObjects.
Module modDestroyObjects
Public Sub DestroyConnection(ByRef cn As ADODB.Connection)
If cn IsNot Nothing Then
If Not cn.State = 0 Then
cn.Close()
End If
cn = Nothing
CollectGarbage("ADODB.Connection")
End If
End Sub
Public Sub DestroyRecordset(ByRef rs As ADODB.Recordset)
If rs IsNot Nothing Then
If Not rs.State = 0 Then
rs.Close()
End If
rs = Nothing
CollectGarbage("ADODB.Recordset")
End If
End Sub
Public Sub DestroyExcelApp(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)
If Not (xlApp Is Nothing) Then
For i As Integer = xlApp.Workbooks.Count To 1 Step -1
xlApp.Workbooks(i).Close(False)
Next i
xlApp.Quit()
xlApp = Nothing
CollectGarbage("Excel.Application")
End If
End Sub
Public Sub DestroyFileSystemObject(ByRef fso As Scripting.FileSystemObject, ByVal str As String)
If Not (fso Is Nothing) Then
If fso.FileExists(str) Then
fso.DeleteFile(str)
End If
CollectGarbage("FileSystemObject")
End If
End Sub
Private Sub CollectGarbage(ByVal strName As String)
GC.Collect()
'' Uncomment below to see a Message Box each time Garbage Collection is manually invoked.
'MsgBox("Object " & strName & " has been collected.", MsgBoxStyle.OkOnly)
End Sub
End Module
By calling the appropriate method, the object is removed from memory immediately, rather than waiting for automatic garbage collection to take place. While this does increase system overhead somewhat, I noticed no decrease in performance. On an older / slower computer, this increase in overhead could be noticeable. However, this ensures that — at least until I can rewrite the code using ADO.NET — the program can run many queries without throwing the dreaded ORA-01000 error.