How does Dapper execute query without explicitly opening connection?

Question

We are using Dapper for some data access activity and are using the standard recommended approach for connecting to database as follows:

public static Func<DbConnection> ConnectionFactory = () => new SqlConnection(ConnectionString);

However, if we try and execute a statement, in the docs it show that you need to first state:

using (var conn = ConnectionFactory())
{
   conn.Open();
   var result =  await conn.ExecuteAsync(sql, p, commandType: CommandType.StoredProcedure);
   return result;
}

That means, you have to explicitly open the connection. However, if we leave out the statement conn.open(), it also works and we are worried if in such cases the connection may not be disposed of properly.

I would appreciate any comments as to how the SQL gets executed without explicitly opening any connection.

Since it's open source, you could review the implementation yourself here: github.com/StackExchange/Dapper/blob/master/Dapper/…. You could see that for every execution the connection state is checked (and opened if it's not already open) and again closed at the finally block (if it's previously closed)... — IronGeek
@IronGeek Thanks for your response. That is a good point. But we never opened any connection in the first place. That is what is confusing us! — user2981411
Yes. If you're executing a command with a closed connection, dapper will automatically open it for you, and close it again at the end of execution... — IronGeek
@IronGeek. Thank again for responding. Obviously I am not getting something. So why do they recommend using conn.Open() as I have stated above if this statement is actually not necessary? — user2981411
"Dapper will close the connection if it needed to open it. So if you're just doing 1 quick query - let Dapper handle it. If you're doing many, you should open (once) and close at the end, with all the queries in the middle...". Here's a relevant thread on GitHub: github.com/StackExchange/Dapper/issues/672 — IronGeek

Amit Joshi Amit Joshi · Accepted Answer · 2019-01-29T07:01:27

Dapper provide two ways to handle connection.

First is - Allow Dapper to handle it.
Here, you do not need to open the connection before sending it to Dapper. If input connection is not in Open state, Dapper will open it - Dapper will do the actions - Dapper will close the connection.

This will just close the connection. Open/Close is different than Dispose. So, if you really want to Dispose the connection better switch to second way.

Second is - Handle all yourself.
Here, you should explicitly create, open, close and dispose the connection yourself.

Please refer to following links for more details:
https://stackoverflow.com/a/51138718/5779732
https://stackoverflow.com/a/41054369/5779732
https://stackoverflow.com/a/40827671/5779732

How does Dapper execute query without explicitly opening connection?

1 Answers