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 该连接会被释放");
});
}
