C# MySQL connection pool limits, and cleaning up connections

Question

I have a simple DB manager class (a grander name than it's abilities deserve):

class DbManager
{
    private MySqlConnectionStringBuilder _connectionString;

    public DbManager()
    {
        _connectionString = new MySqlConnectionStringBuilder();
        _connectionString.UserID = Properties.Database.Default.Username;
        _connectionString.Password = Properties.Database.Default.Password;
        _connectionString.Server = Properties.Database.Default.Server;
        _connectionString.Database = Properties.Database.Default.Schema;
        _connectionString.MaximumPoolSize = 5;
    }


    public MySqlConnection GetConnection()
    {
        MySqlConnection con = new MySqlConnection(_connectionString.GetConnectionString(true));
        con.Open();
        return con;
    }

}

I then have another class elsewhere that represents records in one of the tables, and I populate it like this:

class Contact
{
    private void Populate(object contactID)
    {
        using (OleDbConnection con = DbManager.GetConnection())
        {
            string q = "SELECT FirstName, LastName FROM Contacts WHERE ContactID = ?";

            using (OleDbCommand cmd = new OleDbCommand(q, con))
            {
                cmd.Parameters.AddWithValue("?", contactID);

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        this.FirstName = reader.GetString(0);
                        this.LastName = reader.GetString(1);
                        this.Address = new Address();
                        this.Address.Populate(ContactID)
                    }
                }
            }
        }
    }
}


class Address
{
    private void Populate(object contactID)
    {
        using (OleDbConnection con = DbManager.GetConnection())
        {
            string q = "SELECT Address1 FROM Addresses WHERE ContactID = ?";

            using (OleDbCommand cmd = new OleDbCommand(q, con))
            {
                cmd.Parameters.AddWithValue("?", contactID);

                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        this.Address1 = reader.GetString(0);
                    }
                }
            }
        }
    }
}

Now I thought that all the using statements would ensure that connections are returned to the pool as they're done with, ready for the next use, but I have a loop that creates hundreds of these Contacts and populates them, and it seems the connections are not being freed up.

The connection, the command and the reader are all in their own using statements.

Answer 1

If the app is multithreaded then you are potentially going to have say 10 threads running at the same time. Each of those needs its own connection but if you are limiting that pool size to 5 then you the 6th thread is going to be unable to get a connection from the pool.

You may be limiting your threads in some way but I'd suggest increasing the size of your app pool significantly to ensure that you have more connections available than you might have threads. As an indicator the default size (which is normally good enough for most people) is 100.

Additionally if you have any recursion inside your using block, (eg calling the populate again) as you have indicated you have in comments as opposed to the code above then you are going to run into further problems.

If you call populate inside the using block then you will have the connection from the parent open and in use (so not reusable) and then the child call will open another connection. If this happens just a few times you will run out of your allocation of connections.

To prevent this you want to move the secondary Populate call out of the using block. The easiest way is rather than looping through your recordset calling populate for each ID is to add the IDs to a list and then after you've closed your connection then do the populate for all the new IDs.

Alternatively you could just lazily evaluate things like the Address. Stored the addressID in a private field and then make Address a Property that checks if its backing field (not the addressID) is populated and if not then looks it up with the AddressID. This has the advantage that if you never look at the address you don't even do the database call. Depending on use of the data this may save you a lot of database hits but if you definitely use all the details then it just shifts them around, potentially spreading them out a bit more which might help with performance or maybe just making no difference at all. :)

In general with database access I try to just grab all the data out and close the connection as soon as I can, preferably before doign any complicated calculations on the data. Another good reason for this is that depending on your database query, etc. you could potentially be holding locks on the tables that you are accessing with your queries that could cause locking issues on the database side of things.