Able to access Database without opening connection, using Dapper

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0