ConnectionPool.cs 5.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183
  1. 
  2. using System;
  3. using System.Collections;
  4. using System.Collections.Generic;
  5. using System.Linq;
  6. using System.Text;
  7. using System.Threading.Tasks;
  8. using MySql.Data.MySqlClient;
  9. namespace Microsoft.Data
  10. {
  11. /// <summary>
  12. /// 连接池
  13. /// </summary>
  14. public class ConnectionPool
  15. {
  16. private static ConnectionPool cpool = null;//池管理对象
  17. private static Object objlock = typeof(ConnectionPool);//池管理对象实例
  18. private int size = 2;//池中连接数
  19. private int useCount = 0;//已经使用的连接数
  20. private ArrayList pool = null;//连接保存的集合
  21. private String ConnectionStr = "";//连接字符串
  22. public ConnectionPool()
  23. {
  24. //数据库连接字符串
  25. ConnectionStr = "server=localhost;User ID=root;Password=123456;database=myschool;";
  26. //创建可用连接的集合
  27. pool = new ArrayList();
  28. }
  29. #region 创建获取连接池对象
  30. public static ConnectionPool getPool()
  31. {
  32. lock (objlock)
  33. {
  34. if (cpool == null)
  35. {
  36. cpool = new ConnectionPool();
  37. }
  38. return cpool;
  39. }
  40. }
  41. #endregion
  42. #region 获取池中的连接
  43. public MySqlConnection getConnection()
  44. {
  45. lock (pool)
  46. {
  47. MySqlConnection tmp = null;
  48. //可用连接数量大于0
  49. if (pool.Count > 0)
  50. {
  51. //取第一个可用连接
  52. tmp = (MySqlConnection)pool[0];
  53. //在可用连接中移除此链接
  54. pool.RemoveAt(0);
  55. //不成功
  56. if (!isUserful(tmp))
  57. {
  58. //可用的连接数据已去掉一个
  59. useCount--;
  60. tmp = getConnection();
  61. }
  62. }
  63. else
  64. {
  65. //可使用的连接小于连接数量
  66. if (useCount <= size)
  67. {
  68. try
  69. {
  70. //创建连接
  71. tmp = CreateConnection(tmp);
  72. }
  73. catch (Exception ex)
  74. {
  75. throw new Exception(ex.Message);
  76. }
  77. }
  78. }
  79. //连接为null
  80. if (tmp == null)
  81. {
  82. //达到最大连接数递归调用获取连接否则创建新连接
  83. if (useCount <= size)
  84. {
  85. tmp = getConnection();
  86. }
  87. else
  88. {
  89. tmp = CreateConnection(tmp);
  90. }
  91. }
  92. return tmp;
  93. }
  94. }
  95. #endregion
  96. #region 创建连接
  97. private MySqlConnection CreateConnection(MySqlConnection tmp)
  98. {
  99. //创建连接
  100. MySqlConnection conn = new MySqlConnection(ConnectionStr);
  101. conn.Open();
  102. //可用的连接数加上一个
  103. useCount++;
  104. tmp = conn;
  105. return tmp;
  106. }
  107. #endregion
  108. #region 关闭连接,加连接回到池中
  109. public void closeConnection(MySqlConnection con)
  110. {
  111. lock (pool)
  112. {
  113. if (con != null)
  114. {
  115. //将连接添加在连接池中
  116. pool.Add(con);
  117. }
  118. }
  119. }
  120. #endregion
  121. #region 目的保证所创连接成功,测试池中连接
  122. private bool isUserful(MySqlConnection con)
  123. {
  124. //主要用于不同用户
  125. bool result = true;
  126. if (con != null)
  127. {
  128. string sql = "select 1";//随便执行对数据库操作
  129. MySqlCommand cmd = new MySqlCommand(sql, con);
  130. try
  131. {
  132. cmd.ExecuteScalar().ToString();
  133. }
  134. catch
  135. {
  136. result = false;
  137. }
  138. }
  139. return result;
  140. }
  141. #endregion
  142. }
  143. public class test
  144. {
  145. //调用
  146. //MySqlConnection conn = null;
  147. //for (int i = 1; i <= 100000; ++i)
  148. //{
  149. // //获取连接
  150. // conn = ConnectionPool.getPool().getConnection();
  151. // try
  152. // {
  153. // //数据操作
  154. // MySqlCommand cmd = new MySqlCommand("Select * from zhy_testLianJie", conn);
  155. // MySqlDataReader dr = cmd.ExecuteReader();
  156. // while (dr.Read())
  157. // {
  158. // Console.WriteLine("ID:" + i + ",姓名:" + dr[1]);
  159. // }
  160. // dr.Close();
  161. // //将连接添加回连接池中
  162. // ConnectionPool.getPool().closeConnection(conn);
  163. // }
  164. // catch (Exception ex)
  165. // {
  166. // Console.WriteLine("\n异常信息:\n{0}", ex.Message);
  167. // break;
  168. // }
  169. //}
  170. }
  171. }