How do you dispose a local SqlConnection?

Question

Im programming in vb.net

I want to read data from an mssql database. I want to make it flexible for several different queries, so I put the connecting part into a separate class. Whenever I want to make a query I can get with it a preconfigured DataAdapter. But because of this separation I dont know how to correctly dispose my SqlConnection after collecting the data.

An examplatory use:

Public Class Form1
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim DBA As New DBAccess("dummycommand")
        DBA.provideAdapter.Fill(dummytable)
        ...Dispose? 'This is the part where you usually dispose your datacollecting ressources
    End Sub
End Class

Friend Class DBAccess
    Private SqlString As String

    Friend Sub New(ByVal sql As String)
        SqlString = sql
    End Sub

    Friend Function provideAdapter() As SqlDataAdapter
        Dim cn As New SqlConnection("dummyconstring")
        Dim da As New SqlDataAdapter(SqlString, cn)
        Return da
    End Function
End Class

Can you tell me how I change this concept to fit a dispose?

Answer 1

You could make your data access class Disposable

Friend Class DBAccess
    Implements IDisposable

    Private ReadOnly sqlString As String
    Private disposedValue As Boolean
    Private cn As SqlConnection
    Private da As SqlDataAdapter

    Friend Sub New(sql As String)
        sqlString = sql
    End Sub

    Friend Function provideAdapter() As SqlDataAdapter
        cn = If(cn, New SqlConnection("dummyconstring"))
        da = If(da, New SqlDataAdapter(SqlString, cn))
        Return da
    End Function

    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not disposedValue Then
            If disposing Then
                da?.Dispose()
                cn?.Dispose()
            End If
            disposedValue = True
        End If
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(disposing:=True)
        GC.SuppressFinalize(Me)
    End Sub

End Class

and use it like this

Using DBA As New DBAccess("dummycommand")
    DBA.provideAdapter.Fill(dummytable)
End Using ' Will automatically call Dispose here

but in the long run you could look at an ORM such as Entity Framework to make your life easier.