Do I need to dispose an instance of my custom class to free up memory? Then how? VB.NET

Question

I just want to save time and codes to create a custom class in executing my SQL queries so I created just like this:

Imports ADODB

Public Class MySQLConnection
    Private SQLConnection As ADODB.Connection
    Private SQLConnectionString As String = "Provider=SQLOLEDB;Data Source=111.111.10.201;Initial Catalog=dbSample;User ID=User;password=123456;"
    Private SQLRecordSet As ADODB.Recordset
    Public Recordset As ADODB.Recordset
    Public Message As String

    Public Function ExecuteSQLQuery(vQuery As String) As Boolean
        Try
            SQLConnection.Open(SQLConnectionString)

            SQLRecordSet.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            SQLRecordSet.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            SQLRecordSet.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

            SQLRecordSet.Open(vQuery, SQLConnection)

            Recordset = SQLRecordSet 'passing the content of recordset to a public recordset for later use in my main program.

            SQLRecordSet.Close()

            SQLConnection.Close()

            Message = "Query executed successfully."
            Return True

        Catch ex As Exception
            Message = ex.Message
            Return False

        End Try
    End Function
End Class

QUESTION #1 But since I will be creating multiple instance of this class throughout my program, do I need to somehow dispose the instance to free up the memory immediately after use?

Whenever I need to execute my query, I use my code below in my main program:

    Private Sub COnnectToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles COnnectToolStripMenuItem.Click
        Dim DB_CONN As New MySQLConnection
        If DB_CONN.ExecuteSQLQuery("SELECT * FROM tbl_Stores") Then

            If DB_CONN.Recordset.RecordCount <> 0 Then
                DB_CONN.Recordset.MoveFirst()
                Do While Not DB_CONN.Recordset.EOF
                    'Read each record here
                    DB_CONN.Recordset.MoveNext()
                Loop
            End If

            DB_CONN.Recordset.Close()

            '==============================
            'This is where I think I should dispose my class instance [DB_CON].

            MsgBox("MESSAGE: " & DB_CONN.Message)
        Else
            MsgBox("ERROR: " & DB_CONN.Message)
        End If
    End Sub

QUESTION #2: How do I dispose the instance of my class after use?

I just want to know this so I can clean up my previous programs.

All I find in Google is for C++ so I'm not sure if it works for VB.Net

Please help! :(

Answer 1

If I were you I'd look at making the ExecuteSQLQuery completely self contained such that it takes an Action(Of ADODB.Recordset). Then it can clean up after itself immediately after it has executed.

I'd initially write it like this:

Public Module MySQLConnection
    Private SQLConnectionString As String = "Provider=SQLOLEDB;Data Source=111.111.10.201;Initial Catalog=dbSample;User ID=User;password=123456;"
    Public Function ExecuteSQLQuery(vQuery As String, process As Action(Of ADODB.Recordset), message As Action(Of String)) As Boolean
        Try
            Dim conn As ADODB.Connection = New ADODB.Connection()
            conn.Open(SQLConnectionString)
            Dim rs As ADODB.Recordset = New ADODB.Recordset()
            rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
            rs.Open(vQuery, conn)
            process?(rs)
            rs.Close()
            conn.Close()
            message?("Query executed successfully.")
            Return True
        Catch ex As Exception
            message?(ex.Message)
            Return False
        End Try
    End Function
End Module

Now you'd use it like this:

Private Sub COnnectToolStripMenuItem_Click2(sender As Object, e As EventArgs) Handles COnnectToolStripMenuItem.Click
    Dim success As Boolean = MySQLConnection.ExecuteSQLQuery("SELECT * FROM tbl_Stores",
        Sub(recordset)
            If recordset.RecordCount <> 0 Then
                recordset.MoveFirst()
                Do While recordset.EOF
                    'Read each record here
                    recordset.MoveNext()
                Loop
            End If
        End Sub, AddressOf MsgBox)
End Sub

---

Or even better - make the method return an enumerable of some value:

Public Module MySQLConnection
    Private SQLConnectionString As String = "Provider=SQLOLEDB;Data Source=111.111.10.201;Initial Catalog=dbSample;User ID=User;password=123456;"
    Public Iterator Function ExecuteSQLQuery2(Of T)(vQuery As String, process As Func(Of ADODB.Recordset, T)) As IEnumerable(Of T)
        Dim conn As ADODB.Connection = New ADODB.Connection()
        conn.Open(SQLConnectionString)
        Dim rs As ADODB.Recordset = New ADODB.Recordset()
        rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
        rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
        rs.Open(vQuery, conn)
        If rs.RecordCount <> 0 Then
            rs.MoveFirst()
            Do While rs.EOF
                Yield process(rs)
                rs.MoveNext()
            Loop
        End If
        rs.Close()
        conn.Close()
    End Function
End Module

Then you can do this:

Private Sub COnnectToolStripMenuItem_Click2(sender As Object, e As EventArgs) Handles COnnectToolStripMenuItem.Click
    Dim values As IEnumerable(Of Integer) = MySQLConnection.ExecuteSQLQuery2(Of Integer)("SELECT * FROM tbl_Stores", Function(recordset) CType(recordset.Fields("Value").Value, Integer))
End Sub