What is an appropriate way for managing a MySQL connection through C#

Question

My question, at the bottom line, is what is the appropriate(best) way to manage our connection towards MySQL db with C#.

Well, currently I'm working on some C# (WinForms type) <-> MySQL application and I've been looking at Server Connections in MySQL Administrator, been witness of execution of my MySQL queries, connection opens an closes, ... an so on! In my C# code I'm working like this and this is an example:

public void InsertInto(string qs_insert)
{

     try
     {
          conn = new MySqlConnection(cs);
          conn.Open();

          cmd = new MySqlCommand();
          cmd.Connection = conn;
          cmd.CommandText = qs_insert;
          cmd.ExecuteNonQuery();

     }
     catch (MySqlException ex)
     {
         MessageBox.Show(ex.ToString());
     }
     finally
     {
         if (conn != null)
         {
            conn.Close();
         }
     }
}

Meaning, every time I want to insert something in db table I call this table and pass insert query string to this method. Connection is established, opened, query executed, connection closed. So, we could conclude that this is the way I manage MySQL connection.

For me and my point of view, currently, this works and its enough for my requirements.

Well, you have Java & Hibernate, C# & Entity Framework and I'm doing this :-/ and it's confusing me. Should I use MySQL with Entity Framework?

What is the best way for collaboration between C# and MySQL? I don't want to worry about is connection that I've opened closed, can that same connection be faster, ...

Answer 1

I would suggest using

using(MySqlConnection conn = new MySqlConnetion(cs)
{
    conn.open();
    //setup and execute query

} //conn gets closed here

Here, once you exit the using block, the connection is closed.

Answer 2

The rule is: open as late as possible. Close as soon as possible. (actually you are getting and putting to the connection pool, but the method names are open/close).

Your code snippet is OK, but if you want to follow the rule literally, then Open() could be moved right before ExecuteNonQuery().

As DFord suggested, the using blocks are preferred in C#. This is for stylistic reasons. This is a scope-based style. You can treat your resources (connections) somewhat like you would ordinary local primitives like int, double, etc. When they are out of scope, then everything is automatically cleaned up. The "using" block is converted to a try/finally by the compiler.

The using block is a simulation of RAII. However it is a simulation, not the real thing. Using blocks are a syntactic sugar for writing try/catch. In real RAII you don't have to write any special syntax to get the stack pop effect as it's controlled by the literal stack. Real RAII is slightly safer as you don't need to remember to write any special constructs like "using" or "try".