C# mysql 连接池

jopen 5年前

      该连接池除了基本功能,还多增加了一个管理连接的功能,我看了别人写的,没有这个功能,所以臭屁地说这是我这个程序的亮点,哈哈。

      这个管理连接的功能主要是创建了一个后台线程,然后死循环,不断检测连接池中的连接是否空闲已久(比如空闲了10s),如果空闲已久,就把它关了。

      刚学 C# 不久,如果有哪里不对或者需要改进的地方,还望指教。

using MySql.Data.MySqlClient;  using System;  using System.Collections.Generic;  using System.Linq;  using System.Threading;  using System.Web;  using System.Web.Configuration;    namespace NetBlog.Data  {      /// <summary>      /// Mysql 连接池      /// 单例模式      /// </summary>      public class MysqlPool      {          /// <summary>          /// 数据库配置信息          /// </summary>          private static string ConnectinString = "";          /// <summary>          /// 连接池中的数据库连接对象          /// </summary>          private List<MySqlConnection> connections = null;          /// <summary>          /// 数据库连接对象的状态          /// true 为占用,false 为空闲          /// </summary>          private List<bool> status = null;          /// <summary>          /// 目前连接对象的总数          /// </summary>          private int total = 0;          /// <summary>          /// 目前在用的连接对象数量          /// </summary>          private int inUseNum = 0;          private static int minPoolSize = 10;          private static int maxPoolSize = 100;          /// <summary>          /// 连接对象空闲存活时间 100s          /// </summary>          private static double activeTime = 10000;          /// <summary>          /// 空闲状态起始时间          /// </summary>          private Dictionary<int, double> idleTime = null;          /// <summary>          /// 单例          /// </summary>          private static MysqlPool pool = null;          /// <summary>          /// 连接池创建时间          /// </summary>          private DateTime startTime;          /// <summary>          /// 管理连接池的线程          /// </summary>          private Thread thread = null;            private MysqlPool()          {              this.connections = new List<MySqlConnection>();              this.status = new List<bool>();              this.idleTime = new Dictionary<int, double>();              this.startTime = DateTime.Now;              // 将线程设置为后台线程              // 使得在程序退出后,线程自动结束              this.thread = new Thread(this.ManagePool)              {                  Name = "MysqlPoolManagerThread",                  IsBackground = true              };              this.thread.Start();          }            public static MysqlPool GetInstance()          {              lock (typeof(MysqlPool))              {                  if (pool == null)                  {                      pool = new MysqlPool();                  }              }              return pool;          }            public MySqlConnection GetConnection()          {              lock (this.connections)              {                  if (this.inUseNum == this.total)                  {                      // 连接已占满                      return CreateNewConnection();                  }                  else                  {                      // 有空闲连接                      for(int i = 0; i< this.status.Count; i++)                      {                          if (this.status[i])                          {                              continue;                          }                          else                          {                              this.inUseNum++;                              this.status[i] = true;                              this.idleTime.Remove(i);                              return this.connections[i];                          }                      }                      return null;                  }              }          }            private MySqlConnection CreateNewConnection()          {              if (this.total < maxPoolSize)              {                  MySqlConnection conn = new MySqlConnection(ConnectinString);                  this.connections.Add(conn);                  this.status.Add(true);                  this.total++;                  this.inUseNum++;                  return conn;              }              return null;          }            /// <summary>          /// 归还连接          /// 在方法结束前调用 conn = null 使用户是去对连接对象的引用          /// 避免再次调用连接          /// </summary>          /// <param name="conn"></param>          public void ReleaseConnection(ref MySqlConnection conn)          {              if (conn == null)              {                  return;              }              else              {                  int index = this.connections.IndexOf(conn);                  if (index < 0)                  {                      conn.Close();                      conn = null;                  }                  else                  {                      this.inUseNum--;                      this.status[index] = false;                      this.idleTime.Add(index, DateTime.Now.Subtract(this.startTime).TotalMilliseconds);                      conn = null;                  }              }          }            /// <summary>          /// 管理连接池          /// 将长时间处于空闲状态的连接释放          /// </summary>          private void ManagePool()          {              while (true)              {                  lock (this.connections)                  {                      // 已删除连接个数                      int num = 0;                      if (this.total-this.inUseNum > minPoolSize)                      {                          // 空闲连接大于最小连接池大小                          // 将多余的空闲连接删除                          double mill = DateTime.Now.Subtract(this.startTime).TotalMilliseconds;                          for (int i = 0; i < this.connections.Count; i++)                          {                              double idle = this.idleTime[i];                              if (mill > idle + activeTime)                              {                                  int index = i - num;                                  MySqlConnection conn = this.connections[index];                                  lock (conn)                                  {                                      conn.Close();                                      this.connections.RemoveAt(index);                                      this.total--;                                      this.status.RemoveAt(index);                                      this.idleTime.Remove(i);                                  }                                  num++;                              }                          }                      }                  }                  Thread.Sleep(500);              }          }      }  }


来自: http://my.oschina.net/u/1995999/blog/592746