dbconn.cs

一个实用的数据库连接池源码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Common;

namespace ConnectionPool
{
	public class DBConn
	{
        private bool isUsed = false;  //indicate this connection is used or not.
        private bool isOutOfPool = false; //indicate this connection is in pool or  not.
        private SqlConnection _conn = null;  //sqlconnection instance.
        private int count = -1;  //insert,update,delete affected rows count.
        private string _connectString = null; //connection string.

        /*
         * constructor
         */
        public DBConn()
        {           
        }

        /**
         *  set connection string value.
         */
        public string ConnectString
        {
            set { _connectString = value; }
        }

        /*
         * property.
         */
        public int AffectCount
        {
            get { return count; }
        }

        /**
         *  return sqlconnection object.
         */
        public SqlConnection Conn
        {
            get { return _conn; }
        }

        /*
         * create in pool or create
         * by user.
         */
        public bool IsOutOfPool
        {
            get { return this.isOutOfPool; }
            set { this.isOutOfPool = value; }
        }

        /*
         * connection is using or not.
         * 
         */
        public bool IsUsed
        {
            get { return this.isUsed; }
            set { this.isUsed = value; }
        }
        /*
         * create a connection object with arguments
         * 
         */
        public void Open(string server, string db)
        {
            string strConn = "Persist Security Info=False;Integrated Security=SSPI;server=" + server + ";database=" + db ;
            if (_connectString != null)
                strConn = _connectString;
            try
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection(strConn);
                    _conn.Open();
                }
            }
            catch (Exception)
            {
                _conn = null;
            }
        }

        /*
         * if this connection created by user
         * close it after useless.
         */
        public void Close()
        {
            if(isOutOfPool && !isUsed && _conn !=null) {
                _conn.Close();
                _conn = null;
            }
        }

        /*
         * destroy a connection object.
         */
        public void Destroy()
        {
            if (_conn != null)
            {
                _conn.Dispose();
                _conn = null;
            }
        }

        /*
         * select context from db by sql string.
         * 
         */
        public DataSet Selector(string selectString)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                {
                    _conn.Open();
                }

                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
                SqlCommand selectCommand = new SqlCommand(selectString, _conn);
                selectCommand.CommandType = CommandType.Text;
                mySqlDataAdapter.SelectCommand = selectCommand;
                DataSet myDS = new DataSet();
                mySqlDataAdapter.Fill(myDS);
                return myDS;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /*
         *  update context to db by sql string.
         * 
         */
        public bool Updator(string updateString)
        {
            return BaseExecute(updateString);
        }

        /*
         *  delete context to db by sql string.
         * 
         */
        public bool Deletor(string deleteString)
        {
            return BaseExecute(deleteString);
        }

        /*
         *  insert context to db by sql string.
         * 
         */
        public bool Insertor(string insertString)
        {
            return BaseExecute(insertString);
        }

        /**
         *  base sql command interface.
         */
        private bool BaseExecute(string sqlString)
        {
            try
            {
                if (_conn.State == ConnectionState.Closed)
                {
                    _conn.Open();
                }
                SqlCommand cmd = new SqlCommand(sqlString, _conn);
                cmd.CommandType = CommandType.Text;
                count = cmd.ExecuteNonQuery();
                return !(count < 1);
            }
            catch (Exception)
            {                
                return false;
            }
        }

	}
}