DBHelper类的关闭问题

来源：互联网发布：阿里云服务器国际版编辑：程序博客网时间：2024/05/22 00:46

DBHelper.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
namespace BookShopSQLDAL
{
    static class DBHelper
    {
        static readonly string _connectionString;
        static DBHelper()
        {
            _connectionString =
                ConfigurationManager.ConnectionStrings["conn"].
                ConnectionString;
        }
        public static int ExecuteCommand(string sql, int type)//type = 1表示命令为存储过程
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            if(type==1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result;
        }
        public static int ExecuteCommand(string sql, int type,params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddRange(paras);
            if (type == 1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result;
        }
        public static SqlDataReader ExecuteReader(string sql, int type,ref SqlConnection conn)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            if (type == 1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            SqlDataReader result = cmd.ExecuteReader();
            conn = con;
            return result;
        }
        public static SqlDataReader ExecuteReader(string sql, int type, ref SqlConnection conn, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddRange(paras);
            if (type == 1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            SqlDataReader result = cmd.ExecuteReader();
            conn = con;
            return result;
        }
        public static object ExecuteScalar(string sql, int type)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            if (type == 1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            object result = cmd.ExecuteScalar();
            con.Close();
            return result;
        }
        public static object ExecuteScalar(string sql, int type, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(_connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddRange(paras);
            if (type == 1)
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            object result = cmd.ExecuteScalar();
            con.Close();
            return result;
        }
    }
}

在返回SqlDataReader 时，数据库是没有关闭的，

但其他页面调用DBHelper的ExecuteReader的这个方法是，没法关闭connection

所以在ExecuteReader方法中传了一个sqlconnection过去，在调用方法是，在接受这个

connection，就能关闭connection了。

不关闭连接，会报连接池的错误