使用?ConcurrentDictionary?和?ConcurrentBag?來管理數據庫連接

using Drv.Utilities;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;namespace Drv.AccessClient
{/// <summary>/// 連接池管理類/// </summary>public class MultipleConnectionPool{private ConcurrentDictionary<string, ConcurrentBag<PooledOleDbConnection>> _connectionPools;private int _maxConnectionCount;private Logger _logger;public MultipleConnectionPool(int maxConnectionCount = 5){_connectionPools = new ConcurrentDictionary<string, ConcurrentBag<PooledOleDbConnection>>();_logger = new Logger();_maxConnectionCount = maxConnectionCount;}/// <summary>/// 獲取連接/// </summary>/// <param name="connectionString"></param>/// <returns></returns>public PooledOleDbConnection GetConnection(string connectionString){if (!_connectionPools.ContainsKey(connectionString)){_connectionPools[connectionString] = new ConcurrentBag<PooledOleDbConnection>();}var pool = _connectionPools[connectionString];PooledOleDbConnection availableConnection = null;// 先找已有可用連接foreach (var connection in pool){if (!connection.IsBusy && IsConnectionValid(connection.Connection)){connection.IsBusy = true;availableConnection = connection;break;}}// 沒有可用連接，且連接數量小于最大連接數，則創建新連接if (availableConnection == null && pool.Count < _maxConnectionCount){try{var newConnection = new OleDbConnection(connectionString);newConnection.Open();var pooledConnection = new PooledOleDbConnection(newConnection, connectionString);pooledConnection.IsBusy = true;pool.Add(pooledConnection);availableConnection = pooledConnection;}catch (Exception ex){_logger.LogError($"創建數據庫連接失敗: {ex.Message}");}}else if (availableConnection == null){_logger.LogError("達到最大連接數，無法獲取新的連接");}return availableConnection;}/// <summary>/// 釋放連接/// </summary>/// <param name="connection"></param>public void ReleaseConnection(PooledOleDbConnection connection){if (connection != null){connection.IsBusy = false;// 可選擇在連接不再使用時關閉連接// connection.Connection.Close(); // 視具體需要而定}}// 檢查連接是否有效private bool IsConnectionValid(OleDbConnection connection){try{if (connection.State == System.Data.ConnectionState.Open){// 這里可以執行一個簡單的查詢來驗證連接// connection.CreateCommand().CommandText = "SELECT 1";// connection.CreateCommand().ExecuteScalar();return true;}}catch (Exception ex){_logger.LogError($"連接無效: {ex.Message}");}return false;}}public class PooledOleDbConnection{public OleDbConnection Connection { get; }public string ConnectionString { get; }public bool IsBusy { get; set; }public PooledOleDbConnection(OleDbConnection connection, string connectionString){Connection = connection;ConnectionString = connectionString;IsBusy = false;}}
}

using Drv.Utilities;
using System;
using System.Data;
using System.Data.OleDb;namespace Drv.AccessClient
{public class SimpleDbConnectionManager : IDisposable{private OleDbConnection _connection;private string _connectionString;private bool _disposed = false; // 用于標識是否已釋放資源private Logger _logger;public SimpleDbConnectionManager(string connectionString){if (string.IsNullOrWhiteSpace(connectionString)){_logger.LogError("連接字符串不能為空");}_connectionString = connectionString;_connection = new OleDbConnection(_connectionString);_logger = new Logger();}public void OpenConnection(){if (_connection.State != ConnectionState.Open){_connection.Open();}}public void CloseConnection(){if (_connection.State != ConnectionState.Closed){_connection.Close();}}public DataTable ExecuteQuery(string sql){OpenConnection();DataTable dataTable = new DataTable();try{using (OleDbCommand command = new OleDbCommand(sql, _connection)){using (OleDbDataAdapter adapter = new OleDbDataAdapter(command)){adapter.Fill(dataTable);}}}catch (Exception ex){_logger.LogError($"執行查詢時發生錯誤: {ex.Message}\n{ex.StackTrace}");throw;}finally{CloseConnection();}return dataTable;}public void Dispose(){Dispose(true);GC.SuppressFinalize(this);}protected virtual void Dispose(bool disposing){if (!_disposed){if (disposing){CloseConnection();_connection?.Dispose();}_disposed = true;}}~SimpleDbConnectionManager(){Dispose(false); // 確保析構時也能釋放資源}}
}