SqlServer数据库连接池溢出问题解决

今天解决了一个大Bug——数据库连接池溢出问题，问题在于，DAL层的DbHelper类中的ExecuteReader()方法创建了大量未及时关闭的SqlConnection，致使网站在小规模负载压力下崩溃。

当初的DbHelper类是根据网上给出的参考设计（51aspx.net）的，没想到这个例子也有漏洞，源代码如下：

        public static SqlDataReader ExecuteReader(String sqlText)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception e)
            {
                conn.Close();
                throw e;
                return null;
            }
        }

这样做的好处是：BLL与DAL只依靠sqlstring耦合，BLL中数据库操作的语句更少。但是，以上代码并没有及时关闭创建的数据库连接

修改如下：

        public static SqlDataReader ExecuteReader(String sqlText, SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = sqlText;
                SqlDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception e)
            {
                
                conn.Close();
                throw e;
               // return null;
            }
        }

BLL层使用代码（语句已高亮）：

       /// <summary>
        /// 获得未回答的问题
        /// </summary>
        /// <param name="tagId">0表示获取所有分类</param>
        /// <param name="pageNum"></param>
        /// <param name="totalPageNum"></param>
        /// <param name="queryType">0-default.aspx,1-list</param>
        /// <returns></returns>
        public List<Question> GetUnansweredQuestion(int tagId,int pageNum,ref int totalPageNum,int queryType)
        {
            List<Question> list = new List<Question>();
            String sql;
            if (queryType == 0)
                sql = "SELECT TOP 10 Question.* FROM [Question] WHERE replies = 0";
            else if(tagId>0)
                sql = String.Format("SELECT TOP {0} * FROM (SELECT Question.*,ROW_NUMBER() OVER (ORDER BY created DESC) AS RowNumber FROM [Question],[QuestionTagAssociation] WHERE Question.replies = 0 AND Question.qid = QuestionTagAssociation.qid AND QuestionTagAssociation.tid = {2}) A WHERE RowNumber > {0} * ({1}-1)", pageSize, pageNum, tagId);
            else
                sql = String.Format("SELECT TOP {0} * FROM (SELECT Question.*,ROW_NUMBER() OVER (ORDER BY created DESC) AS RowNumber FROM [Question] WHERE Question.replies = 0) A WHERE RowNumber > {0} * ({1}-1)", pageSize, pageNum);

            SqlConnection conn = DbHelper.getConnection();

            SqlDataReader reader = DbHelper.ExecuteReader(sql, conn);
            fillQuestionList(list, reader);

            reader.Close();
            conn.Close();

            if (queryType != 0)
            {
                if(tagId>0)
                    sql = String.Format("SELECT count(*) FROM [Question],[QuestionTagAssociation] WHERE Question.replies = 0 AND Question.tid = QuestionTagAssociation.tid AND QuestionTagAssociation.tid = {0}", tagId);
                else
                    sql = "SELECT count(*) FROM [Question] WHERE Question.replies = 0";
                totalPageNum = (int)Math.Floor((double)DbHelper.getSingleInt(sql)/pageSize);
                
            }
    
            return list;
        }

修改后，系统在小规模负载压力下正常运行。