Why does Dapper not require an open connection?

Question

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

Dapper does require that a connection be opened before executing a query. Dapper does not open a connection for you. — G-Man, Jun 09 '15 at 15:56
I have looked at the source, it never opens the connection. So you surely get an exception which you could have checked easily. — Tim Schmelter, Jun 09 '15 at 15:57
your pluralsight course probably has a helper class defined that takes care of opening and closing the connection. — G-Man, Jun 09 '15 at 15:59
@Gaetano It does not, and as I have mentioned I have been able to use the exact same pattern to return data without opening the connection — majjam, Jun 09 '15 at 16:01
Now that you've provided your method, have you tested it without openeing the connection? Note that it's best practise to use the `Using`-statement to ensure that the connection gets disposed as soon as possible. Therefore don't create the connection as field but as local variable. — Tim Schmelter, Jun 09 '15 at 16:01
@TimSchmelter yes I've tested it without opening the connection, it works fine, returning the data as requested. I agree that normally I would use a using statement to explicitly control the connection, however Id rather avoid extra code if possible, especially if Dapper is doing this for me — majjam, Jun 09 '15 at 16:03
@TimSchmelter I've had a look at the source too, there seem to be a large number of "if (wasClosed) cnn.Open()" lines — majjam, Jun 09 '15 at 16:07
@bamie9l: i have also tested it, the result: _"System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed. at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) ...."_ So maybe you use a different version. I don't know PluralSight. — Tim Schmelter, Jun 09 '15 at 16:09
@TimSchmelter thanks for testing this Tim, I'll continue to see if I can figure this one out — majjam, Jun 09 '15 at 16:17
According to this post: http://stackoverflow.com/questions/12628983/why-doesnt-dapper-dot-net-open-and-close-the-connection-itself Dapper automatically manages its own connections. I'm using version NET40 which has the ExecuteCommand method, containing cnn.Open. — majjam, Jun 10 '15 at 10:32

score 0 · Answer 1 · answered Nov 04 '16 at 21:48

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

Why does Dapper not require an open connection?

1 Answers1