The Dapper documentation states that it requires an open connection. However in Steve Michelotti's pluralsight course he doesn't open the connection before executing SQL, and I have found that my own testing connecting to SQL Server and MS Access confirms this.
Is it best practice to manually control the connections or is it fine to just leave this to Dapper? Are there situations where Dapper absolutely requires that an opened connection is provided?
Here is an example of the code that I'm executing against an Access database. At no point do I open the connection, however Dapper happily returns a collection of Fund objects:
Private ReadOnly _conn As IDbConnection = New OleDbConnection(ConnectionStrings.GetAccessConnectionString(ConnectionStrings.AccessVersion.v2003, ConfigurationManager.AppSettings("MSAccessLocation"), "")) Public Function GetAll() As List(Of Fund) Implements IFundRepository.GetAll Return _conn.Query(Of Fund)("SELECT * FROM Funds").ToList() End Function
Decided to post this as an answer instead because comments have limited formatting options and max length ... I second TimSchmelter's suggestion, "don't create the connection as field but as local variable." Regardless of whether or not dapper disposes the connection, your code should dispose of it as soon as it is not needed.
In this case,
Public Function GetAll() As List(Of Fund) Implements IFundRepository.GetAll Using conn As IDbConnection = New DbConnection (_connectionString) Dim funds As List(Of Fund) = _conn.Query(Of Fund)("SELECT * FROM Funds").ToList() Return funds End Using End Function