超级大怪事

Question

我用数据库连接池进行修改程序时出现错误：如下
打开连接：
 public void openConnection()
{
            //从池中借用连接，该方法中数据库已经打开
            conn = pool.BorrowDBConnection();}
 /// <summary>
/// 执行Sql查询语句，并返回第一行的第一条记录,返回值为object，使用时需要拆箱操作 -> Unbox
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>object 返回值</returns>
public static object ExecuteScalar(string sqlstr)
        {
            object obj = new object();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
          
                obj = comm.ExecuteScalar();//一执行该语句，就提示数据库连接断开
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            return obj;
}我没用连接池之前，普通的连接  本段代码没有问题。 请教为什么？

Answer 1

你conn 有赋值给Command.Connection吗?

Answer 2

整得这么高深，为什么要用连接池来操作呢？还没有用过连接池呢。
con和comm关联了吗？
你每次使用前才取连接呢
try
{
comm.Connection=pool.BorrowDBConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;obj = comm.ExecuteScalar();//一执行该语句，就提示数据库连接断开
}

Answer 3

在一个static的方法中使用comm，可见它一定返回一个固定的对象，这能是什么好事？这一看就知道是个大bug。真正的连接池程序人家最起码是线程安全的，而你这个程序根本不懂连接池干什么，就奢谈连接池概念去啦！

Answer 4

只有在这个方法里我看到类似var comm=createNewCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
..... 
这类代码才会相信这确实是在实际开发中有用的代码。

Answer 5

先理解数据库连接池
using (SqlConnection conn = new SqlConnection(""))  
       {}

Answer 6

ok 搞定了！  贴出我的使用连接池的代码？虽然代码通过！但我想有很多不足的地方！请大家指教！感谢大家的批评！
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
using System.Windows.Forms;
using DateBaseConnectionPool;namespace LOG2
{
    public class DbWriter
    {
        static ConnectionPool c = new ConnectionPool("Data Source=ORACLE;User Id=*****;Password=****;", ConnTypeEnum.OracleClient);
        static OracleConnection conn =null;
        private static OracleCommand comm = new OracleCommand();
        
        static object o;         #region 属性
        //日志记录开始时间
        private static string t1;
        public static string T1
        {
            get { return DbWriter.t1; }
        }
        //最后一条日志时间
        private static string t2;
        public static string T2
        {
            get { return DbWriter.t2; }
        }
        //日志类别总数
        private static string catgorySum;
        public static string CatgorySum
        {
            get { return DbWriter.catgorySum; }
        }
        //日志级别总数
        private static string levelSum;
        public static string LevelSum
        {
            get { return DbWriter.levelSum; }
        }
        //日志总数
        private static string sum;
        public static string Sum
        {
            get { return DbWriter.sum; }
        }        #endregion 属性        #region 构造方法
        /// <summary>
        /// 构造方法
        /// </summary>
        static  DbWriter()
        {
            
            c.MinConnection = 10;
            c.MaxConnection = 200;
            c.MaxRepeatDegree = 2;
            c.KeepRealConnection = 2;
            c.SeepConnection = 5;
            c.StartServices();
        }
        #endregion 构造方法        #region 数据库操作
        /// <summary>
        /// 获取连接
        /// </summary>
        public static void getConnection()
        {
            o = new object(); 
            conn = (OracleConnection)c.GetConnectionFormPool(o, ConnLevel.ReadOnly);
            comm = conn.CreateCommand();
           
        }        /// <summary>
        /// 释放连接
        /// </summary>
        public  void disposeConnection()
        {
            comm.Dispose(); 
            c.DisposeConnection(o);
        }
        /// <summary>
        /// 执行Sql查询语句，并返回第一行的第一条记录,返回值为object，使用时需要拆箱操作 -> Unbox
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>object 返回值</returns>
        public  object ExecuteScalar1(string sqlstr)
        {
            object obj = new object();
            try
            {
                getConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                obj=comm.ExecuteScalar();
   
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                disposeConnection();
            }
            return obj;
        }        /// <summary>
        /// 返回指定Sql语句的DataTable
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public  DataTable dataTable(string sqlstr)
        {
            OracleDataAdapter da = new OracleDataAdapter();
            DataTable datatable = new DataTable();
            try
            {
                getConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.SelectCommand.Connection=conn;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                disposeConnection();
            }
            return datatable;
        }        #endregion 数据库操作        #region 读数据库 获取数据
        /// <summary>
        /// 向Form的ListView中加载系统事件数据
        /// </summary>
        /// <returns></returns>
        /// 
        public  List<ListViewItem> LoadAllLog()
        {
            List<ListViewItem> _ListViewItem = new List<ListViewItem>();
            try
            {
                DataTable dt = new DataTable();
                dt = dataTable("select * from SUPER.LOG2");
                foreach (DataRow dr in dt.Rows)
                {
                    ListViewItem item = new ListViewItem(dr["LOG_DATE"].ToString());
                    item.SubItems.Add(dr["THREAD"].ToString());
                    item.SubItems.Add(dr["LOG_LEVEL"].ToString());
                    item.SubItems.Add(dr["LOGGER"].ToString());
                    item.SubItems.Add(dr["MESSAGE"].ToString());
                    item.SubItems.Add(dr["EXCEPTION"].ToString());
                    item.SubItems.Add(dr["LOG_IP"].ToString());
                    item.SubItems.Add(dr["MACHINENAME"].ToString());
                    item.SubItems.Add(dr["COURSENAME"].ToString());
                    _ListViewItem.Add(item);
                }
                return _ListViewItem;
            }
            catch (Exception ex)
            {                System.Windows.Forms.MessageBox.Show("数据库连接失败!错误代码:" + ex.Message);
                return _ListViewItem;
            }
        }        /// <summary>
        /// 获取表信息
        /// </summary>
        public  void GetLogInfo()
        {
            sum = ExecuteScalar1("select count(*) from  SUPER.LOG2").ToString();
            t1 = ExecuteScalar1("select LOG_DATE FROM SUPER.LOG2 where ID= (select MIN(ID) FROM SUPER.LOG2)").ToString();
            t2 = ExecuteScalar1("select LOG_DATE FROM SUPER.LOG2 where ID= (select MAX(ID) FROM SUPER.LOG2)").ToString();
            catgorySum = ExecuteScalar1("select count(distinct  LOGGER)from  SUPER.LOG2").ToString();
            levelSum = ExecuteScalar1("select count(distinct LOG_LEVEL) from  SUPER.LOG2").ToString();
        }        public  List<ListViewItem> QueryLog(string _logger, string _logLevel, string _thread, string st, string et)
        {
            string str = "select * from SUPER.LOG2" + " where LOGGER like '" + _logger + 
                "' and LOG_LEVEL like '" + _logLevel +"' and THREAD like '" + _thread +
               "' and LOG_DATE >=to_date('" + st + "','YYYY-MM-DD HH24-MI-SS')" +
               " and LOG_DATE <=to_date('" + et + "','YYYY-MM-DD HH24-MI-SS')";
            List<ListViewItem> _ListViewItem = new List<ListViewItem>();            DataTable dt = new DataTable();
            dt = dataTable(str);
            foreach (DataRow dr in dt.Rows)
            {
                ListViewItem item = new ListViewItem(dr["LOG_DATE"].ToString());
                item.SubItems.Add(dr["THREAD"].ToString());
                item.SubItems.Add(dr["LOG_LEVEL"].ToString());
                item.SubItems.Add(dr["LOGGER"].ToString());
                item.SubItems.Add(dr["MESSAGE"].ToString());
                item.SubItems.Add(dr["EXCEPTION"].ToString());
                item.SubItems.Add(dr["LOG_IP"].ToString());
                item.SubItems.Add(dr["MACHINENAME"].ToString());
                item.SubItems.Add(dr["COURSENAME"].ToString());
                _ListViewItem.Add(item);
            }
            return _ListViewItem;
        }        /// <summary>
        /// 加载treeview
        /// </summar
        public  List<ListViewItem> LoadTreeView(string str)
        {
            DataTable dt = new DataTable();
            dt = dataTable("select LOG_DATE,THREAD,LOG_LEVEL,LOGGER,MESSAGE,EXCEPTION,LOG_IP,MACHINENAME,COURSENAME from SUPER.LOG2 Where LOG_LEVEL='" + str + "'");
            List<ListViewItem> _ListViewItem = new List<ListViewItem>();
            foreach (DataRow dr in dt.Rows)
            {
                ListViewItem item = new ListViewItem(dr["LOG_DATE"].ToString());
                item.SubItems.Add(dr["THREAD"].ToString());
                item.SubItems.Add(dr["LOG_LEVEL"].ToString());
                item.SubItems.Add(dr["LOGGER"].ToString());
                item.SubItems.Add(dr["MESSAGE"].ToString());
                item.SubItems.Add(dr["EXCEPTION"].ToString());
                item.SubItems.Add(dr["LOG_IP"].ToString());
                item.SubItems.Add(dr["MACHINENAME"].ToString());
                item.SubItems.Add(dr["COURSENAME"].ToString());
                _ListViewItem.Add(item);
            }
            return _ListViewItem;        }
        #endregion 读数据库 获取数据
    }
}