数据库连接池使用

在Web服务中需要频繁访问数据库操作，因此会创建很多数据库连接对象，但数量过多会影响性能，可以通过数据库连接池来控制连接数量

这里提供一个数据库连接池类，实现如下：

 

    /// <summary>
    /// 数据库连接对象池 继承自对象池类
    /// </summary>
    public sealed class DBConnectionSingleton:ObjectPool
    {
        //private DBConnectionSingleton() { }
        public static readonly DBConnectionSingleton Instance = new DBConnectionSingleton();
        private static string m_connectionString;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get { return m_connectionString; }
            set { m_connectionString = value; }
        }

        /// <summary>
        /// 创建数据库连接对象
        /// </summary>
        /// <returns></returns>
        protected override object Create()
        {
            SqlConnection con = new SqlConnection(m_connectionString);
            con.Open();
            return con;
        }

        /// <summary>
        /// 判断数据库连接对象是否有效
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        protected override bool Validate(object obj)
        {
            try
            {
                SqlConnection con = (SqlConnection)obj;
                return !(con.State.Equals(System.Data.ConnectionState.Closed));
            }
            catch (SqlException )
            {
                return false;
            }
        }

        /// <summary>
        /// 销毁数据库连接对象
        /// </summary>
        /// <param name="obj"></param>
        protected override void Expire(object obj)
        {
            try
            {
                ((SqlConnection)obj).Close();
            }
            catch { }
        }

        /// <summary>
        /// 从数据库连接对象池中获取一个对象
        /// </summary>
        /// <returns></returns>
        public SqlConnection BorrowDBConnection()
        {
            try
            {
                return (SqlConnection)base.GetObjectFromPool();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 将对象还给数据库连接对象池
        /// </summary>
        /// <param name="con"></param>
        public void ReturnDBConnection(SqlConnection con)
        {
            base.ReturnObjectToPool(con);
        }

   }

    数据库连接池的使用：

        /// <summary>
        /// 数据库连接对象池类的使用示例
        /// </summary>
        private void Using()
        {
            DBConnectionSingleton pool;
            pool = DBConnectionSingleton.Instance;
            DBConnectionSingleton.ConnectionString = "Data Source=192.168.2.10,1250;Initial Catalog=AnyImage_NDYFY20150320;Persist Security Info=True;User ID=grid;Password=qd@2004grid";
            SqlConnection con = pool.BorrowDBConnection();
            //在这里使用数据库连接进行数据库操作
            SqlCommand command = new SqlCommand("delete from table", con);
            command.ExecuteNonQuery();
            //操作完后将数据库连接对象返回给对象池
            pool.ReturnDBConnection(con);
        }
    }