C# – an appropriate way for managing a MySQL connection through C#

cdatabaseMySQLserver

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, …

Best Answer

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.

Related Topic