Access数据库连接池

Access数据库没有数据库连接池，每次打开和关闭数据库时都非常耗时，自己编写了一个简易的数据库连接池，添加数据库类型还可支持其他没有数据库连接池的数据库类型。

该数据库连接池要求必须更改web.config中的数据库连接字符串，添加Max Pool Size及Connect Timeout两个属性，如：<add name="AccessConnectString" connectionString="”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:"db.accdb;Max Pool Size=200;Connect Timeout=300"/>;，以获取需要最大的连接数量及连接对象生存期。

该数据库连接池是在关闭数据库连接，检验其他连接对象的生存期，这样在没有发出关闭数据库连接的请求时，一些已经超过生存期连接对象仍然在内存中，还无法做到与其他数据库连接池同样的效果。

数据库连接池代码如下：

    /// <summary>

    /// 连接对象。

    /// </summary>

    public class Pool

    {

        /// <summary>

        /// 初始化。

        /// </summary>

        /// <param name="id">代码。</param>

        /// <param name="connection">数据库连接对象。</param>

        /// <param name="isUse">使用标志。</param>

        /// <param name="openTime">打开时间。</param>

        public Pool(Guid id, IDbConnection connection, bool isUse, DateTime openTime)

        {

            this.id = id;

            this.connection = connection;

            IsUse = isUse;

            OpenTime = openTime;

        }

 

        private Guid id;

        /// <summary>

        /// 获取连接对象的代码。

        /// </summary>

        public Guid Id { get { return id; } }

 

        private IDbConnection connection;

        /// <summary>

        /// 获取数据库连接对象。

        /// </summary>

        public IDbConnection Connection { get { return connection; } }

 

        /// <summary>

        /// 获取或设置一个值，该值指示连接对象是否已经使用，true-已经使用，否则没有使用。

        /// </summary>

        public bool IsUse { get; set; }

        /// <summary>

        /// 获取或设置连接对象打开数据库的时间。

        /// </summary>

        public DateTime OpenTime { get; set; }

    }

 

    /// <summary>

    /// 连接池管理类。

    /// </summary>

    public static class ConnectionPool

    {

        private static List<Pool> pools = new List<Pool>();

        private static int poolTimeout = 300;

        private static int maxPoolSize = 200;

        private static string connectString = "";

        private static bool getParam = false;

 

        static ConnectionPool()

        {

        }

 

        /// <summary>

        /// 获取参数。

        /// </summary>

        /// <param name="connectionString">配置文件中的数据库连接字符串。</param>

        private static void GetParams(string connectionString)

        {

            string[] connectStrings = connectionString.Split(';');

            StringBuilder newConnectString = new StringBuilder();

            foreach (string subConnectString in connectStrings)

            {

                if (subConnectString.IndexOf("Provider", StringComparison.InvariantCultureIgnoreCase) != -1 ||

                    subConnectString.IndexOf("Data Source", StringComparison.InvariantCultureIgnoreCase) != -1)

                {

                    newConnectString.Append(subConnectString);

                    newConnectString.Append(";");

                }

                if (subConnectString.IndexOf("Max Pool Size", StringComparison.InvariantCultureIgnoreCase) != -1)

                {

                    string[] poolSizeses = subConnectString.Split('=');

                    maxPoolSize = int.Parse(poolSizeses[1]);

                }

                if (subConnectString.IndexOf("Connect Timeout", StringComparison.InvariantCultureIgnoreCase) != -1)

                {

                    string[] timeouts = subConnectString.Split('=');

                    poolTimeout = int.Parse(timeouts[1]);

                }

            }

            connectString = newConnectString.ToString();

            getParam = true;

        }

 

        /// <summary>

        /// 根据数据库类型创建数据库。

        /// </summary>

        /// <param name="connectType">数据库连接类型。</param>

        /// <returns>指定数据库连接类型的数据库连接对象。</returns>

        private static IDbConnection CreateConnection(ConnectionType connectType)

        {

            switch (connectType)

            {

                case ConnectionType.OleConnectionType:

                    return new OleDbConnection(connectString);

                default:

                    throw new Exception("此数据库类型不能使用此连接池。");

            }

       }

 

        /// <summary>

        /// 根据数据库连接字符串及数据库类型创建连接对象。

        /// </summary>

        /// <param name="connectionString">数据库连接字符串。</param>

        /// <param name="connectType">数据库类型。</param>

        /// <returns>连接对象。</returns>

        public static Pool Open(string connectionString, ConnectionType connectType)

        {

            lock (typeof(ConnectionPool))

            {

                if (!getParam)

                {

                    GetParams(connectionString);

                }

                foreach (Pool pool in pools)

                {

                    if (!pool.IsUse)

                    {

                        pool.IsUse = true;

                        pool.OpenTime = DateTime.Now;

                        return pool;

                    }

                }

                if (pools.Count >= maxPoolSize)

                {

                    throw new Exception("连接池的连接数已经超过最大值，不能再提供数据库连接。");

                }

                Pool newPool = new Pool(Guid.NewGuid(), CreateConnection(connectType), true, DateTime.Now);

                pools.Add(newPool);

                newPool.Connection.Open();

                return newPool;

            }

        }

 

        /// <summary>

        /// 关闭数据库连接。

        /// </summary>

        /// <param name="closePool">需要关闭的连接池对象。</param>

        public static void Close(Pool closePool)

        {

            for (int num = pools.Count - 1; num >= 0; num--)

            {

                if (pools[num].Id == closePool.Id)

                {

                    pools[num].IsUse = false;

                }

                else

                {

                    if (pools[num].IsUse)

                    {

                        continue;

                    }

                    TimeSpan time = DateTime.Now - pools[num].OpenTime;

                    if (time.TotalSeconds > poolTimeout)

                    {

                        pools[num].Connection.Close();

                        pools.Remove(pools[num]);

                    }

                }

            }

        }

    }

 

调用代码：

private connectionString = ConfigurationManager.ConnectionStrings[“AccessConnectString”].ToString();

//获取连接对象

private Pool pool=ConnectionPool.Open(connectionString, ConnectionType.OleConnectionType);

//获取数据库连接对象

IDbConnection dbConnection= pool.Connection;

数据库操作

//关闭数据库连接

ConnectionPool.Close(pool);

转载于:https://www.cnblogs.com/mm8413/archive/2008/09/10/1288400.html