这是一个关于mysql连接的对象池设计模式的问题。实现这种设计模式的目的是在需要创建多个示例时重用相同的连接/对象。下面的代码是用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");
        }
    }
}

上面的代码是对象池设计模式的一个实现，每次通过调用这个函数“getobject”创建一个新示例时，下面的类就是一个重写objectpooling方法并与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();
    }
}

上面的类演示了三种重写方法，它们是“create”，在连接过期时创建一个新示例，“expire”，在连接已经过期时关闭连接。验证当前连接是否打开的最后一个函数。现在，下面的代码是一个普通类，它将与对象池和数据库连接相关联。

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);
    }

}

从这两个函数可以看出 CheckForExistingPT_NUM1(String pt) 以及 CheckForExistingPT_NUM2(String pt) 实际上，这两个函数只是使用同一个连接与数据库进行交互，而不是关闭连接并再次打开新连接。下面是实现所有逻辑的主类的函数。

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);

}

下面是在输出框中打印出来的一行消息

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

这里的问题是，当没有创建示例时，连接将保持空闲。从输出框中可以看到，connection2仍然处于空闲状态，创建新示例时连接将被销毁，如何关闭所有空闲连接？谢谢