user3343366 user3343366 - 2 months ago 19
C# Question

C# MySQL Connector: Connection pools are not working

I want to make use of the connection pooling so i could use the database without passing the MysqlConnection object to each class. I have a code like this:

Main.cs:

namespace batman
{

class Program
{
static void Main(string[] args)
{
using (MysqlConnection conn = new MysqlConnection("Server=localhost;User=root;Database=test;Password=root;Min Pool Size=3;Max Pool Size=5;Pooling=True"))
{
MonitorClass monitor = new MonitorClass();
monitor.Run();
}

//...
}
}
}


MonitorClass.cs:

namespace batman
{

public class MonitorClass
{

public void Run()
{

using (MySqlConnection conn = new MySqlConnection())
using (MySqlCommand cmd = conn.CreateCommand())
{


try
{
conn.Open();
cmd.CommandText = "SELECT id, package_type FROM package_registry WHERE finish_time <= @ftime";
cmd.Parameters.AddWithValue("@ftime", 0);
cmd.Prepare();

MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int packageId = reader.GetInt32(0);
string packageType = reader.GetString(1);

Unirest.post("http://localhost/gears/ops/packagefinish")
.field("package", packageId)
.asStringAsync();

Console.WriteLine("[PackageMonitor] Package {0} ({1}) expired", packageId, packageType);
}
}
catch (MySqlException ex)
{

}
}
}
}
}


MonitorClass should take the connection from the connection pool.
But once I run my program it throws System.InvalidOperationException with


Additional information: Unable to connect to any of the specified MySQL hosts.


at:


cmd.Prepare();


Now, i think I'm doing something wrong, but I couldn't figure out what exactly.

Oracle docs says this


The Connector/Net supports connection pooling for better performance and scalability with database-intensive applications. This is enabled by default. You can turn it off or adjust its performance characteristics using the connection string options Pooling, Connection Reset, Connection Lifetime, Cache Server Properties, Max Pool Size and Min Pool Size. See Section 5.2, “Creating a Connector/Net Connection String” for further information.

Connection pooling works by keeping the native connection to the server live when the client disposes of a MySqlConnection. Subsequently, if a new MySqlConnection object is opened, it will be created from the connection pool, rather than creating a new native connection. This improves performance.

Answer

Well, there seem to be two different problems:

  1. Connection is opened twice - first time in Main method and second time (independently) in Monitor class. This can be fixed by opening connection in monitor class only and passing connection string inside of it.

  2. You never opened connection by invoking .Open() method.

Let's refactor your code keeping this in mind :

Main :

    static void Main(string[] args) 
    {
       var connStr = "Server=localhost;User=root;Database=test;Password=root;Min Pool Size=3;Max Pool Size=5;Pooling=True";
       MonitorClass monitor = new MonitorClass(connStr);
       monitor.Run();
       //...
    }

Monitor :

public class MonitorClass
{
   private readonly string _connStr;

   public MonitorClass(string connectionString) 
   {
      this._connStr = connectionString;
   }

   public void Run()
   {
       using (MySqlConnection conn = new MySqlConnection(_connStr))
       using (MySqlCommand cmd = conn.CreateCommand())
       {
           conn.Open();
           ...
       }
   }
}
Comments