Need help managing MySql connections

Question

I'm having trouble finding a clear explanation of connection pooling. I'm building an app using the .NET connector I downloaded from mysql.com. The app only needs one db connection but will be running simultaneously on about 6 machines on my network. Normally, I'd create the connection at startup and just leave it. But I'm seeing lots of posts from people who say that's bad practice. Also I'm concerned about timeouts. My app will run 24/7 and there may be extended periods without database activity.

I'm leaning toward the following:

using (MySqlConnection conn = new MySqlConnection(connStr))
{
    conn.Open();
    // use connection
}

But I'm not sure I understand what's going on in the background. Is this actually closing the connection and allowing gc to kill the object, or is there a built in pooling behavior that preserves the object and redelivers it the next time I try to create one?

I certainly don't want the app reauthenticating across the network every time I hit the database.

Can anyone offer me some advise?

Answer 1

Its customary of the .net providers to use connection pooling in that scenario.

The connection should be just returning to the pool at the end of your using.

I haven't looked under the hook with a mysql profiler, but I have code in place that depends on it - haven't had trouble with it.

Update: I just looked at the calls done during dispose and it definitely does connection pooling i.e. it ends up calling:

internal void CloseFully()
{
    if (this.settings.Pooling && this.driver.IsOpen)
    {
        if ((this.driver.ServerStatus & ServerStatusFlags.InTransaction) != 0)
        {
            new MySqlTransaction(this, IsolationLevel.Unspecified).Rollback();
        }
        MySqlPoolManager.ReleaseConnection(this.driver);
    }
    else
    {
        this.driver.Close();
    }
    this.driver = null;
}

Update 2 / answering the comment: the MySqlConnection instance is a different one, as the using statement just deals with disposing (freeing of resources). Because of that, you don't need to check if its closed. The MySqlConnection internally uses other classes / instances, which is were it grabs the approppiate instance. That's transparent to your code, so you use it just like if it were a new+different connection / just like in the code you posted.

Like you said, in order to be able to reuse the lower level connection (called Driver in the mysql connector code), each pool is determined by the connection string.

Answer 2

According to this post the connections are kept alive and pooled by default for 60 seconds before being closed.

Answer 3

The disconnected model is the most used throughout the world, though not abusing on authentication over and over again.

Disconnected Mode

Here are some reason why you want to work disconnected most of the time:

1. The number of CLA (Client Licences Agreements) bought for your database server (well, not really applicable here as it is MySQL);
2. If too many people are connected at once, this slows down the DBE (Database Engine);
3. Keeping connections opened keeps the network busy, and too busy network are more likely to fail;
4. While a user is editing, let's say a customer details, he doesn't need to preserve his connection to the database server, and perhaps lock a row or a data table, which would result of big concurrent locking problems.

This code:

using(MySqlConnection conn = new MySqlConnection(connStr)) {
    if (conn.State == ConnectionState.Closed)
        try {
            conn.Open();
        } catch (MySqlException ex) {
            // Exception handling here...
        }
    // Use of connection here...
}

The using keyword is used to automatically dispose objects instantiated within it as the article reference states:

Defines a scope, outside of which an object or objects will be disposed.

This way, you ensure that once the connection is no longer required, you dispoe of it. So yes, a new authentication will be required once this connection is instantiated again, as it no longer exists. There's a little polling done here for a short time, but this is nothing you need to worry about.

Connected Mode

In order to ensure the use of only one such connection throughout your application, you should use it as a singleton. However, once your connection string changes, someday, you'll have to ensure that all the application gets closed and reopen, so that this connection gets a refreshed connection string. That is not viable, but I don't know your context.

Using Enterprise Library Data Access Application Block

In order to bring your connection pool manageable, you may want to use the Enterprise Library Data Access Application Block.

The DAAB is an easy to use, fully configurable data access layer engineered by Microsoft engineers and other participating companies. Then, managing connection pool can be as easy as 1-2-3!

I think you could gain a lot using the DAAB which is fully configurable within XML file and requires very low maintenance.

EDIT If I may push a little further, I would perhaps consider using the Façade design pattern with Factories.

Using Facade and Factories Design Pattern Effectively

Having an "intelligent" façade, it is this that provide you with the connection you need. As such, here's a simple example (assuming you have a project setting called "DefaultConnectionString"):

public static class ApplicationFacade {
    private static readonly ApplicationFactory _applicationFactory = new ApplicationFactory();

    public static DefaultConnectionString {
        get {
            return Properties.Settings.Default.DefaultConnectionString;
        }
    }

    public static IList<ICustomer> GetCustomers() {
        using(var connection = OpenConnection())
           _applicationFactory.GetCustomers(connection);
    }

    public MySqlConnection OpenConnection() {
        var newConnection = new MySqlConnection(DefaultConnectionString);
        try {
            newConnection.Open();
        } catch (Exception ex) {
            // Exception handling...
        }
        return newConnection;
    }
}

internal sealed class ApplicationFactory {
    internal ApplicationFactory() {
    }

    internal IList<ICustomer> GetCustomers(MySqlConnection connection) {
        if (connection.State != ConnectionState.Open)
            throw new InvalidOperationException()

        IList<ICustomer> customers = new List<ICustomer>();

        var command = new MySqlCommand(connection, @"select * from Customers");
        // Place code to get customers here...

        return customers;
    }
}

// So you'll be able to use share the same connection throught your factory whenever needed, preventing the overkill of authentication over and over again. Here's how this would be used:

public partial class MainForm : Form {
    private void PopulateGrid() {
        dataGridView1.DataSource = ApplicationFacade.GetCustomers();
        // And you never care about the connection! All you want is the list of customers, that's all!
    }
}

That is a pattern that I often use in my development projects. It allows one single entry point to my class libraries, and they're very easy to use.

Well, that's perhaps more than what you asked for, but I hope it helps.