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?

First and foremost you shouldn't forget to close the connection (even better to wrap connection in a using statement). Connections are unmanaged resources that stay alive until thay are explicitly closed. Connections should never longer be open than needed, so using a single connection for the lifetime of your application is a pretty bad idea. — Jay, May 20 '12 at 16:26
Yes, I was aware of the closing issue. So what I have to do is, metaphorically, break down all the DB activity in single queries or group of subsequent queries doing a consistent "action"/"operation" (f. ex. a loop with some INSERTs) and open before and close the connection after these operations? — Cranio, May 20 '12 at 16:33

score 4 · Answer 1 · answered May 20 '12 at 19:44

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
    }

moribvndvs · Accepted Answer · 2012-05-20T17:18:51.767

You typically want to open one connection per unit of work. The MySQL ADO.NET driver can pool connections for you, however, I would recommend against having each query open a connection. What can happen is you start using several methods in order to service one business transaction, and since each is opening a connection, you can end up that one business transaction starving your available connections. This of course can lead to timeouts, poor performance, etc.

Instead, consider defining an IUnitOfWork/UnitOfWork API that creates and opens a connection and transaction, and having your data access layer request the current IUnitOfWork, which will provide the current connection and transaction.

The trick, of course, is knowing when a unit of work begins and when it ends. They are associated with a complete, meaningful action (a "business transaction"). For example, when you are servicing a request to a method on one of your WCF services. When the service instance starts, in response to a request, a unit of work should be created. Then you DAL components can ask for the current unit of work and use it. Then when the request is completed, the unit of work should be committed (which should commit the transaction and close the connection).

Correct use of connections with C# and MySQL

2 Answers2

Linked