9

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.

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
user2981411
  • 859
  • 12
  • 34
  • Since it's open source, you could review the implementation yourself here: https://github.com/StackExchange/Dapper/blob/master/Dapper/SqlMapper.Async.cs#L668-L687. 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 Jan 27 '19 at 14:46
  • @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 Jan 27 '19 at 20:17
  • 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 Jan 27 '19 at 22:41
  • @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 Jan 28 '19 at 01:49
  • 1
    "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: https://github.com/StackExchange/Dapper/issues/672 – IronGeek Jan 28 '19 at 05:19

1 Answers1

8

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

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • @AtimJoshi using (var conn = ConnectionFactory()) { //conn.Open(); var result = await conn.ExecuteAsync(sql, p, commandType: CommandType.StoredProcedure); return result; } this is disposed and you also do not open connection explicitly – user2981411 Feb 09 '19 at 22:19
  • 1
    @user2981411: Yes; but it is disposed by `using` block and **you** write the `using` block. So, that means you disposed the connection. Note that it is not disposed by Dapper. – Amit Joshi Feb 11 '19 at 05:41