This is a question about object pooling design pattern with MySql Connection. The intention of implementing this design pattern is to reuse the same connection/object when more than one instances needs to be created. Below code is a class of object pooling design pattern written in c#.
public abstract class ObjectPool<T>
{
private long expirationTime;
private Dictionary<T, long> Running, Idle;
//Constructor
public ObjectPool()
{
expirationTime = 30000; //30 seconds
Running = new Dictionary<T, long>();
Idle = new Dictionary<T, long>();
}
protected internal abstract T create();
public abstract Boolean validate(T o);
public abstract void expire(T o);
public virtual T GetObject
{
get
{
//lock the first thread
//make sure first thread is executed before the second thread
lock (this)
{
long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
T t;
if (Idle.Count > 0)
{
IEnumerator<T> element = Idle.Keys.GetEnumerator();
while (element.MoveNext())
{
t = element.Current;
//unlocked[t] -> Get the VALUE asscoiated with the specified KEY
if ((now - Idle[t]) > expirationTime)
{
//object has expired
System.Diagnostics.Debug.WriteLine("object has expired");
Idle.Remove(t);
expire(t);
//to obtain the default value of a Generic Type
t = default(T);
}
else
{
if (validate(t))
{
System.Diagnostics.Debug.WriteLine("Connection is still open. used back the same object");
Idle.Remove(t);
Running.Add(t, now);
return (t);
}
else
{
//object failed validation
System.Diagnostics.Debug.WriteLine("object has failed validation");
Idle.Remove(t);
expire(t);
t = default(T);
}
}
}
}
// no objects avaialable, create a new one
System.Diagnostics.Debug.WriteLine("no object is available, creating a new one");
t = create();
Running[t] = now;
return (t);
}
}
}
public virtual void releaseObject(T t)
{
lock (this)
{
Running.Remove(t);
Idle[t] = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
System.Diagnostics.Debug.WriteLine("release object");
}
}
}
The above code is an implementation of object pooling design pattern, each time a new instance is created by calling this function "GetObject", The below class is a class that override ObjectPooling 's method and integrates with MySql
class DatabaseConnection : ObjectPool<MySqlConnection>
{
private String ConnectionString =
"datasource=127.0.0.1;" +
"port=3306;" +
"username=root;" +
"password=;" +
"database=mesa;" +
"Integrated Security = SSPI;"+
"Min Pool Size = 1;" +
"Max Pool Size = 9;" +
"Connection Lifetime = 600;";
public DatabaseConnection() : base()
{
}
public override void expire(MySqlConnection o)
{
try
{
o.Close();
System.Diagnostics.Debug.WriteLine("Closing Database Connection Successful");
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Diagnostics.Debug.WriteLine("Closing Database Connection Unsuccessful");
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"MySql Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
throw new NotImplementedException();
}
public override bool validate(MySqlConnection o)
{
System.Diagnostics.Debug.WriteLine("Validating");
try
{
if (o.State.ToString() == "Open")
{
return true;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Diagnostics.Debug.WriteLine("Database cannot check current state");
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"MySql Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
throw new NotImplementedException();
}
protected internal override MySqlConnection create()
{
try
{
MySqlConnection mySqlConnection = new MySqlConnection(ConnectionString);
mySqlConnection.Open();
System.Diagnostics.Debug.WriteLine("Database Connection Successful");
return (mySqlConnection);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"MySql Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
throw new NotImplementedException();
}
}
The above class illustrates three override method which are "create" which will create a new instance when the connection is expired , and "expire" this is to close a connection when the connection is already expired. The last function which is to validate whether the current connection is open or not. Now, the below code is a normal class that will associate with object pooling and databas connection.
class InputValidation
{
private DatabaseConnection databaseConnection = new DatabaseConnection();
//Constructor
public InputValidation()
{
}
//Destructor
~InputValidation()
{
//databaseConnection.expire();
}
public void CheckForExistingPT_NUM1(String pt)
{
MySqlConnection connection1 = databaseConnection.GetObject;
try
{
string sql = "SELECT * FROM carrier";
MySqlCommand cmd = new MySqlCommand(sql, connection1);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string someStringFromColumnZero = reader.GetString(0);
string someStringFromColumnOne = reader.GetString(1);
System.Diagnostics.Debug.WriteLine(someStringFromColumnZero + "," + someStringFromColumnOne);
}
reader.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
databaseConnection.releaseObject(connection1);
}
public void CheckForExistingPT_NUM2(String pt)
{
MySqlConnection connection1 = databaseConnection.GetObject;
try
{
string sql = "SELECT * FROM carrier";
MySqlCommand cmd = new MySqlCommand(sql, connection1);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string someStringFromColumnZero = reader.GetString(0);
string someStringFromColumnOne = reader.GetString(1);
System.Diagnostics.Debug.WriteLine(someStringFromColumnZero + "," + someStringFromColumnOne);
}
reader.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
databaseConnection.releaseObject(connection1);
}
}
Now, as you can see from these two functions CheckForExistingPT_NUM1(String pt) and CheckForExistingPT_NUM2(String pt) actually these two functions just using the same connection to interact with the database instead of close the connection and open a new connection again. Below is a function of main class that implement all the logic.
private void AliasButton_Click(object sender, EventArgs e)
{
String PT_NUM = PTNumTextBox.Text;
InputValidation inputValidation = new InputValidation();
inputValidation.CheckForExistingPT_NUM1(PT_NUM);
inputValidation.CheckForExistingPT_NUM2(PT_NUM);
}
Below is line of messages that printed out in output box
no object is available, creating a new one
The thread 0x994 has exited with code 0 (0x0).
Database Connection Successful
801BRWUE,BCM88682CA1KFSBG
U10BRT7E,BCM3450KMLG
release object
Validating
Connection is still open. used back the same object
801BRWUE,BCM88682CA1KFSBG
U10BRT7E,BCM3450KMLG
release object
The problem here is the connection will remain idle when there is no instance is created. As you can see from the output box, the connection2 is still remain idle, the connection will be destroy when a new instance is created, how do I close all the idle connections? Thanks
