c#封装数据库连接池

以下是一个简单的C#数据库连接池的封装示例：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConnectionPoolDemo
{
    public class ConnectionPool
    {
        private readonly string _connectionString;
        private readonly Stack<IDbConnection> _pool;

        public ConnectionPool(string connectionString, int poolSize)
        {
            _connectionString = connectionString;
            _pool = new Stack<IDbConnection>(poolSize);
            for (int i = 0; i < poolSize; i++)
            {
                IDbConnection connection = new SqlConnection(_connectionString);
                connection.Open();
                _pool.Push(connection);
            }
        }

        public IDbConnection GetConnection()
        {
            if (_pool.Count > 0)
            {
                return _pool.Pop();
            }
            else
            {
                IDbConnection connection = new SqlConnection(_connectionString);
                connection.Open();
                return connection;
            }
        }

        public void ReleaseConnection(IDbConnection connection)
        {
            if (connection != null)
            {
                _pool.Push(connection);
            }
        }
    }

    public class DataAccess
    {
        private readonly ConnectionPool _connectionPool;

        public DataAccess(string connectionString, int poolSize)
        {
            _connectionPool = new ConnectionPool(connectionString, poolSize);
        }

        public void ExecuteNonQuery(string commandText, IDictionary<string, object> parameters)
        {
            using (IDbConnection connection = _connectionPool.GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (KeyValuePair<string, object> parameter in parameters)
                    {
                        IDbDataParameter dataParameter = command.CreateParameter();
                        dataParameter.ParameterName = parameter.Key;
                        dataParameter.Value = parameter.Value;
                        command.Parameters.Add(dataParameter);
                    }
                    command.ExecuteNonQuery();
                }
            }
        }

        public object ExecuteScalar(string commandText, IDictionary<string, object> parameters)
        {
            using (IDbConnection connection = _connectionPool.GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (KeyValuePair<string, object> parameter in parameters)
                    {
                        IDbDataParameter dataParameter = command.CreateParameter();
                        dataParameter.ParameterName = parameter.Key;
                        dataParameter.Value = parameter.Value;
                        command.Parameters.Add(dataParameter);
                    }
                    return command.ExecuteScalar();
                }
            }
        }

        public DataTable ExecuteDataTable(string commandText, IDictionary<string, object> parameters)
        {
            using (IDbConnection connection = _connectionPool.GetConnection())
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    foreach (KeyValuePair<string, object> parameter in parameters)
                    {
                        IDbDataParameter dataParameter = command.CreateParameter();
                        dataParameter.ParameterName = parameter.Key;
                        dataParameter.Value = parameter.Value;
                        command.Parameters.Add(dataParameter);
                    }
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        DataTable dataTable = new DataTable();
                        dataTable.Load(reader);
                        return dataTable;
                    }
                }
            }
        }
    }
}

使用示例：

using System;
using System.Collections.Generic;
using System.Data;

namespace ConnectionPoolDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=(local);Initial Catalog=TestDB;Integrated Security=True";
            int poolSize = 10;
            DataAccess dataAccess = new DataAccess(connectionString, poolSize);

            // 执行查询
            string query = "SELECT * FROM Users WHERE Age > @age";
            IDictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@age", 18);
            DataTable dataTable = dataAccess.ExecuteDataTable(query, parameters);
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine("{0} - {1}", row["Name"], row["Age"]);
            }

            // 执行插入
            string insert = "INSERT INTO Users (Name, Age) VALUES (@name, @age)";
            parameters = new Dictionary<string, object>();
            parameters.Add("@name", "Tom");
            parameters.Add("@age", 20);
            dataAccess.ExecuteNonQuery(insert, parameters);

            // 执行更新
            string update = "UPDATE Users SET Age = @age WHERE Name = @name";
            parameters = new Dictionary<string, object>();
            parameters.Add("@name", "Tom");
            parameters.Add("@age", 21);
            dataAccess.ExecuteNonQuery(update, parameters);

            // 执行删除
            string delete = "DELETE FROM Users WHERE Name = @name";
            parameters = new Dictionary<string, object>();
            parameters.Add("@name", "Tom");
            dataAccess.ExecuteNonQuery(delete, parameters);

            // 释放连接
            IDbConnection connection = dataAccess.GetConnection();
            dataAccess.ReleaseConnection(connection);
        }
    }
}