Correct use of connections with C# and MySQL

Question

A typical example for C#/MySQL interaction involves code like this (I'm skipping try/catches and error checking for simplicity's sake):

conn = new MySqlConnection(cs);
conn.Open();
string stm = "SELECT * FROM Authors";
MySqlCommand cmd = new MySqlCommand(stm, conn);
rdr = cmd.ExecuteReader();

The scenario is a class library for both a standalone application and a WCF web-service. So, should I open a connection whenever I make a query? Or should I open it once when I open the program?

Answer 1

To expand on HackedByChinese's recommendation consider the following. You have one main coordinating method that handles creating the connection, opening it, setting the transaction, and then calling the worker methods that do the different types of work (queries).

  public static void UpdateMyObject(string connection, object myobject)
        {
        try
        {
            using (SqlConnection con = new SqlConnection(connection))
            {
                con.Open();
                using (SqlTransaction trans = con.BeginTransaction())
                {
                    WorkingMethod1(con, myobject);
                    WorkingMethod2(con, myobject);
                    WorkingMethod3(con, myobject);
                    trans.Commit();
                }
                con.Close();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("SOMETHING BAD HAPPENED!!!!!!!  {0}", ex.Message);
        }
    }

    private static void WorkingMethod1(SqlConnection con, object myobject)
    {
        // Do something here against the database
    }

    private static void WorkingMethod2(SqlConnection con, object myobject)
    {
        // Do something here against the database
    }

    private static void WorkingMethod3(SqlConnection con, object myobject)
    {
        // Do something here against the database
    }