Able to access Database without opening connection, using Dapper

Question

So I have the following piece of code that is showing a strange behavior or I am missing some point here:

public async Task<IEnumerable<MyData>> GetMyData()
{
    MySqlConnection conn;
    IEnumerable<MyData> list;
    string querystring = "SELECT * FROM tbl_mydata;";

    using (conn = new MySqlConnection(mydbconnectionString))
    {
        //conn.Open();
        ConnectionState check1 = conn.State;
        list = await conn.QueryAsync<MyData>(querystring, commandType: CommandType.Text);
        ConnectionState check2 = conn.State;
    }

    ConnectionState check = conn.State;

    return list;
}

Now on running this, I can see that the check1 is Closed and I am still able to get the data from the database. In the above case both check1and check are in Closed state.

When I uncomment, the line: conn.Open();, I can see that check1 is an Open state and getting the data from the database (expected). Upon coming out from the using block, I can see that check is set to Closed (expected).

My connection string looks like:

<add name="MyDBConnection" providerName="MySql.Data.MySqlClient" connectionString="server=127.0.0.1;port=3306;user id=root; password=root; database=ht_ep;Convert Zero Datetime=True;Allow Zero Datetime=False;Connection Timeout=10;pooling=true" />

So why I am still able to access the database without opening the connection? Am I missing something here?

See: https://stackoverflow.com/questions/12628983/why-doesnt-dapper-dot-net-open-and-close-the-connection-itself — Steve, Feb 11 '21 at 19:40
@Steve Okay, but if that were happening internally, then `check1` would have been set to `Open` since Dapper manages it internally but it is still not. That is what I am trying to understand here. — Rahul Sharma, Feb 11 '21 at 19:43
No, because you start interacting with Dapper at the QueryAsync not when you create the connection — Steve, Feb 11 '21 at 19:44
Dapper knows how to open the connection itself, it opens it, reads the data and closes it. If you open it, it leaves it open when it's done — Caius Jard, Feb 11 '21 at 19:45
@Steve So I have put another `check2` variable and I can see it the state is still `Closed` even after the `QueryAsync` is invoked. Does Dapper open and close the connection all by itself? If that is the case, then do I need the `using` statement for database pooling management? Thanks — Rahul Sharma, Feb 11 '21 at 19:48
Yes, it opens the connection if closed and closes it if the open is made by dapper itself. So you can avoid the open if the call is atomic, instead if you need to handle a transaction then you can open it yourself. The using is still mandatory. It releases everything and your code remains consistent. What happens in the pool is no concern for us (at least if you don't need to dig deeper for some particular reason) — Steve, Feb 11 '21 at 19:51
(This isn't a new concept; DataAdapters have done it for years too - if you open the conn they leave it open in case you started a transaction on it, otherwise they open and close it as necessary) — Caius Jard, Feb 11 '21 at 19:51
Related: https://stackoverflow.com/q/40824948/5779732, https://stackoverflow.com/a/51138718/5779732 — Amit Joshi, Feb 12 '21 at 07:28

score 3 · Accepted Answer · edited Feb 12 '21 at 08:34

Internally, Dapper code that gets stuff looks something like this:

QueryAsync(this IDbConnection con, ...){

  bool wasClosed = con.State == Closed;

  if(wasClosed)
    con.Open();

  // run query, do magic


  if(wasClosed)
    con.Close();

}

So you can see if you pass on a connection that is open it'll leave it alone. If you pass a connection that is closed it opens and closes it for you, and gives it you back in the same state it received it

As to whether you still need to using; I'd say technically no, given that the most significant thing disposing a connection does is close it (returns it to the pool if it's pooled) but I'd say it's still a good thing to do because:

the using statement doesn't take up much more space/make things any less readable than a var declaration would
it defines a clear scope over which the object will live and makes it obvious in the code when it's no longer available, which helps keep it "no longer than necessary"
it could help prevent future bugs if dapper is not used(and instead a direct connection use is performed), or if someone thinks "oh, there's an open missing .. I'll add it", or if they copy pasta and screw something up.. in a similar fashion to the aim of code style rules like "always use braces, even for single line if" (like I didn't in my code above, hah) that help prevent bugs such as "new line of code written at same indent but not actually in the scope of the if" - being consistent with using on disposables helps avoid having to think about whether to remember to call dispose, close etc
you just don't know, without reading code, what other things dispose will do, and if something's disposable it is thus for a reason

It was indeed; https://www.techopedia.com/definition/31470/copypasta "a block of text that has been copy/pasted multiple times" ->often giving rise to common programming errors where we change one of the things in the paste but forget to change the other one.. But if it's not well understood worldwide, then I'm happy for an edit that makes more sense to a global audience :) — Caius Jard, Feb 12 '21 at 08:35

Able to access Database without opening connection, using Dapper

1 Answers1