is it necessary to use using Dispose the connection , open and close connection if using DbContext dapper in MS access database on vb.net

Question

is it necessary to use using Dispose the connection, open and close connection if using DbContext dapper in MS access database on vb.net.

This is my first time using dapper, please guide me so that there are no errors in doing the crud and efficiently use Dapper in oledb database access. and also I'm afraid there is an error in implementing my code.

Thanks

'model
Public Class User
        Public Property Id() As Integer
        Public Property Name() As String
        Public Property Email() As String
        Public Property Password() As String
    End Class

'Database
  Public Module DbContext
        Public Function GetOledbConnectionString() As String
            Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
        End Function
    End Module

 Public Class UserService
        Private ReadOnly _conn As OleDbConnection
        Private _connectionString As String = DbContext.GetOledbConnectionString()
        Public Sub New()
            _conn = New OleDbConnection(_connectionString)
        End Sub
        Public Function GetUser() As IEnumerable(Of User)
            Dim sql = "SELECT * FROM Users"
            Dim result = Me._conn.Query(Of User)(sql).ToList()
            Return result
        End Function
        Public Sub InserUser(ByVal user As User)
            Dim sql = $"INSERT INTO `users` (`Name`, `Email`, `Password`) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');"
            Me._conn.Execute(sql)
        End Sub

'form load in datagridview
 Partial Public Class Pguser
        Inherits UserControl
        Private uService As New Database.UserService()
        Public Sub New()
            If Program.IsInDesignMode(Me) Then
                Return
            End If
            InitializeComponent()
            LoadData()
        End Sub
        Private Sub LoadData()
            Dim user = uService.GetUser()
            grid.DataSource = user
        End Sub
    End Class

*Anything* that implements `IDisposable` should be disposed. — Craig, May 22 '23 at 15:19

djv · Accepted Answer · 2023-05-23T13:20:45.727

About your request

please guide me... efficiently use Dapper in oledb database access

Properly using the object which implements IDisposable should come before you mention efficiency. See this answer for more info about Dapper specifically. It seems Dapper will open (and close) the connection for you but since you are doing the opening, you might as well do both.

Public Class UserService
    Public Function GetUser() As IEnumerable(Of User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
        End Using
    End Function
    Public Sub InserUser(ByVal user As User)
        Using _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
            _conn.Execute($"INSERT INTO `users` (`Name`, `Email`, `Password`) VALUES ('{user.Name}', '{user.Email}', '{user.Password}');")
        End Using
    End Sub
End Class

With Try ... Finally

Public Function GetUser() As IEnumerable(Of User)
    Dim _conn = New OleDbConnection(DbContext.GetOledbConnectionString())
    Try
        Return _conn.Query(Of User)("SELECT * FROM Users").ToList()
    Finally
        _conn?.Dispose()
    End Try
End Function

You can explicitly close with a Try ... Finally but the Using syntax is much cleaner.

You can also look into Entity Framework to move away from text commands.

Thank you for your answer. If I want to use Try... Finally should be used in the position where in my code — roy, May 23 '23 at 12:20
Thank you for your answer and sorry if I am late in your response — roy, May 26 '23 at 13:53

is it necessary to use using Dispose the connection , open and close connection if using DbContext dapper in MS access database on vb.net

1 Answers1