protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer(SQLConnectionInfo.getConnectionString(), options => options.EnableRetryOnFailure()); } }
static void Main(string[] args) { SQLConnectionPool.min = 1; SQLConnectionPool.max = 2; SQLConnectionPool.connectionStr = "server=127.0.0.1;port=3306;user=testdb;password=123;database=testdb;"; //并发测试 for (int i = 0; i < 100; i++) { Task.Run(() => { SQLConnectionInfo info = pool.GetConnection(); MySqlCommand mySqlCommand = new MySqlCommand("SELECT * FROM testtbl", info.conn); int re = (int)mySqlCommand.ExecuteScalar(); info.isUse = false; Console.WriteLine(re); }); } //测试五十次,因为间隔为1秒钟 之前多线程创建的多余连接 会被自行回收 for (int i = 0; i < 50; i++) { SQLConnectionInfo info = pool.GetConnection(); MySqlCommand mySqlCommand = new MySqlCommand("SELECT * FROM testtbl", info.conn); int re = (int)mySqlCommand.ExecuteScalar(); info.isUse = false; Console.WriteLine(re); Thread.Sleep(1000); } //测试五十次,设置连接池最小值为1 所以当连接池剩一个连接时 即使低于每秒钟平均次也不会被释放 for (int i = 0; i < 50; i++) { SQLConnectionInfo info = pool.GetConnection(); MySqlCommand mySqlCommand = new MySqlCommand("SELECT * FROM testtbl", info.conn); int re = (int)mySqlCommand.ExecuteScalar(); info.isUse = false; Console.WriteLine(re); Thread.Sleep(5000); } Console.ReadKey(); }
/// <summary> /// 从数据库连接池里获取连接 如果获取不到则 进入等待 (如果必要请自行写入超时) /// </summary> /// <returns></returns> public SQLConnectionInfo GetConnection() { lock (Monitor) { SQLConnectionInfo connInfo = null; bool canConn = false; ReleaseConnection(); if (sQLConnectionInfos.Count <= max) { while (!canConn) { connInfo = sQLConnectionInfos.Find(s => !s.isUse); if (connInfo != null) { if (connInfo.conn.TestConnection()) { connInfo.isUse = true; connInfo.connCount++; canConn = true; } else { sQLConnectionInfos.Remove(connInfo); } } else if (sQLConnectionInfos.Count < max) { SQLConnectionInfo newSQLConnectionInfo = new SQLConnectionInfo { isUse = false, conn = new MySqlConnection(connectionStr) }; newSQLConnectionInfo.conn.Open(); if (newSQLConnectionInfo.conn.TestConnection()) { newSQLConnectionInfo.isUse = true; newSQLConnectionInfo.connCount++; sQLConnectionInfos.Add(newSQLConnectionInfo); connInfo = newSQLConnectionInfo; canConn = true; } } } } return(connInfo); } }
/// <summary> /// 释放 每秒平均次数 低于1的连接 如果该连接处于连接状态则不释放 /// </summary> private void ReleaseConnection() { Console.WriteLine($"--------------------------------目前连接池还剩余:{sQLConnectionInfos.Count}--------------------------------"); SQLConnectionInfo sQLConnectionInfo = sQLConnectionInfos.Find(s => s.isExpired && sQLConnectionInfos.Count > min && !s.isUse); sQLConnectionInfos.Remove(sQLConnectionInfo); sQLConnectionInfos.ForEach(s => { double time = (double)(DateTime.Now - s.time).TotalSeconds; Console.WriteLine($"该连接已被使用次数:{s.connCount} 是否被连接占用:{s.isUse} " + $"次({s.connCount})/ 秒({time}) = 每秒钟平均次( {(double)s.connCount / time}) 每秒平均次低于 1 该连接会被释放"); }); }