C# mysql连接线程池

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace Common
{
    /// <summary>
    /// 连接池对象
    /// </summary>
    public class ConnectionObject
    {
        private MySqlConnection mySqlConnection = null;   //数据库连接

        /// <summary>
        /// 是否可用
        /// </summary>
        public bool isCanUse { get; set; } = true;     

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connection"></param>
        public ConnectionObject(ref MySqlConnection connection) {

            this.mySqlConnection = connection;
        }

        /// <summary>
        /// 得到当前连接对象
        /// </summary>
        /// <returns></returns>
        public MySqlConnection GetCurrentConnectionObject() {

            return mySqlConnection;
        }

        /// <summary>
        /// 设置连接对象
        /// </summary>
        /// <param name="connection"></param>
        public void SetConnection(ref MySqlConnection connection) {

            mySqlConnection = connection;
        }



    }
}

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Threading;

namespace Common
{
    /// <summary>
    /// 连接池
    /// </summary>
    public class ConnectionPool
    {

        #region 属性

        /// <summary>
        /// 测试数据库表名
        /// </summary>
        public string testTable { get; set; } = "single_table";

        /// <summary>
        /// 初始化连接池大小
        /// </summary>
        public int initConnections { get; set; } = 10;

        /// <summary>
        /// 连接池自增大小
        /// </summary>
        public int incrementConnections { get; set; } = 5;

        /// <summary>
        /// 连接池最大大小
        /// </summary>
        public int maxConnections { get; set; } = 50;

        /// <summary>
        /// 连接池数组
        /// </summary>
        private List<ConnectionObject> connectionObjects = new List<ConnectionObject>();

        /// <summary>
        /// 连接字符串
        /// </summary>
        private string connectionString = "";

        #endregion 属性结束

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connection">连接字符串</param>
        public ConnectionPool(string connection) {

            connectionString = connection;

            CreateConnections(initConnections);

        }

        


        //-----------------------------------------  函数分割线  --------------------------------------------------------


        /// <summary>
        /// 创建数据库并把它放进连接池
        /// </summary>
        /// <param name="numConnections">创建的数量</param>
        private void CreateConnections(int numConnections) {

            for (int i = 0; i < numConnections; i++)
            {
                //如果当前最大连接大于0，并且 当前数组的总数大于或等于最大值，则跳出循环
                if (maxConnections > 0 && this.connectionObjects.Count >= maxConnections) {
                    break;
                }

                try
                {
                    MySqlConnection tmpConnect = NewConnection();
                    connectionObjects.Add(new ConnectionObject(ref tmpConnect));
                }
                catch (Exception ex)
                {

                    Console.WriteLine(ex);
                }

            }

        }


        /// <summary>
        /// 创建一个新连接
        /// </summary>
        /// <returns>返回连接</returns>
        private MySqlConnection NewConnection() {

            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.Open();
            return conn;

        }


        /// <summary>
        /// 得到一个连接
        /// </summary>
        /// <returns></returns>
        public MySqlConnection GetConnection() {

            if (connectionObjects == null) return null;

            MySqlConnection conn = GetFreeConnection();  //得到一个可用的空闲的连接

            //如果为空就循环到可以返回可用连接为止
            while (conn==null) {

                Thread.Sleep(250);
                conn = GetFreeConnection();

            }

            return conn;

        }


        /// <summary>
        /// 得到空闲连接
        /// </summary>
        /// <returns></returns>
        private MySqlConnection GetFreeConnection() {

            MySqlConnection conn = FindFreeConnection();

            if (conn == null) {

                CreateConnections(incrementConnections);
                conn = FindFreeConnection();
            }

            return conn;

        }


        /// <summary>
        /// 获取空闲的连接
        /// </summary>
        /// <returns>有就返回，没有则返回null</returns>
        private MySqlConnection FindFreeConnection() {

            MySqlConnection conn = null;

            for (int i = 0; i < connectionObjects.Count; i++)
            {
                ConnectionObject connection = connectionObjects[i];

                if (connection.isCanUse) {

                    conn = connection.GetCurrentConnectionObject();
                    connection.isCanUse = false;

                    //测试连接是否可用
                    if (TestConnection(ref conn)==false) {

                        conn = NewConnection();
                        connection.SetConnection(ref conn);
                    }
                }
                break;
            }

            return conn;
        }


        /// <summary>
        /// 把数据库归还到连接池
        /// </summary>
        /// <param name="conn">数据库</param>
        public void ReturnConnection(MySqlConnection conn) {

            if (connectionObjects == null) return;

            for (int i = 0; i < connectionObjects.Count; i++)
            {

                if (conn == connectionObjects[i].GetCurrentConnectionObject()) {

                    connectionObjects[i].isCanUse = true;
                    break;
                }

            }

        }


        /// <summary>
        /// 刷新连接池中所有的连接对象
        /// </summary>
        public void RefreshConntionPool() {

            if (connectionObjects == null) return;

            for (int i = 0; i < connectionObjects.Count; i++)
            {

                if (connectionObjects[i].isCanUse == false) Thread.Sleep(5000);

                //关闭当前连接
                CloseConnection(connectionObjects[i].GetCurrentConnectionObject());
                MySqlConnection tmpConnect = NewConnection();
                connectionObjects[i].SetConnection(ref tmpConnect);
                connectionObjects[i].isCanUse = true;

            }

        }


        /// <summary>
        /// 关闭并清空连接池
        /// </summary>
        public void CloseConnectionPool() {

            if (connectionObjects == null) return;

            for (int i = 0; i < connectionObjects.Count; i++)
            {
                if (connectionObjects[i].isCanUse == false) Thread.Sleep(5000);

                CloseConnection(connectionObjects[i].GetCurrentConnectionObject());

                connectionObjects.RemoveAt(i);

            }

            connectionObjects.Clear();
            connectionObjects = null;


        }


        /// <summary>
        /// 测试连接是否可用
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <returns></returns>
        private bool TestConnection(ref MySqlConnection conn) {

            try
            {
                string sql = $"SELECT Id FROM {testTable} WHERE Id = 1";
                MySqlCommand dbComm = new MySqlCommand(sql, conn);
                MySqlDataReader dbReader = dbComm.ExecuteReader();
                dbReader.Close();
            }
            catch
            {
                CloseConnection(conn);
                return false;
            }

            return true;
 
        }



        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="conn"></param>
        private void CloseConnection(MySqlConnection conn) {

            try{ conn.Close();}catch { }
        }




    }
}