C#中DBHelper终极

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace StusMis.DAL.SqlServer
{
    public static class DBHelper
    {

        private static SqlConnection connection;
        /// <summary>
        /// 获取Connection对象
        /// </summary>
        private static SqlConnection Connection
        {
            get
            {
                //读取配置文件
                string connectionString = ConfigurationManager.ConnectionStrings["sqlserver"].ToString();
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
        /// <summary>
        /// 获取Command对象
        /// </summary>
        /// <param name="sql">[SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="sqlParameter">参数可为null</param>
        /// <returns>返回创建好的Command对象</returns>
        private static SqlCommand ExecuteCommand(string sql, CommandType commandType, SqlParameter[] sqlParameter)
        {
            SqlCommand sqlCommand = new SqlCommand(sql, Connection);
            if (sqlParameter != null && sqlParameter.Length > 0)
            {
                sqlCommand.Parameters.AddRange(sqlParameter);
            }
            sqlCommand.CommandType = commandType;
            return sqlCommand;
        }
        /// <summary>
        /// 执行更新操作：增/删/改
        /// </summary>
        /// <param name="sql">[增/删/改SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="sqlParameter">参数可为null</param>
        /// <returns>返回true 则更新成功 否则 更新失败</returns>
        public static bool ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] sqlParameter)
        {
            SqlCommand sqlCommand = ExecuteCommand(sql, commandType, sqlParameter);
            int result = sqlCommand.ExecuteNonQuery();
            return result!=0?true:false;
        }
        /// <summary>
        /// 执行登录操作
        /// </summary>
        /// <param name="sql">[查询SQL语句-select count(*)...] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="sqlParameter">参数可为null</param>
        /// <returns>返回true 则登录成功 否则 登录失败</returns>
        public static bool ExecuteScalar(string sql, CommandType commandType, SqlParameter[] sqlParameter)
        {
            SqlCommand sqlCommand = ExecuteCommand(sql, commandType, sqlParameter);
            int result = Convert.ToInt32(sqlCommand.ExecuteScalar());
            return result!=0?true:false;
        }
        /// <summary>
        /// 获取DataReader对象
        /// </summary>
        /// <param name="sql">[查询SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="sqlParameter">参数可为null</param>
        /// <returns>返回满足条件的DataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] sqlParameter)
        {
            SqlCommand sqlCommand = ExecuteCommand(sql, commandType, sqlParameter);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 获取DataTable对象
        /// </summary>
        /// <param name="sql">[查询SQL语句] 或 [存储过程名]</param>
        /// <param name="commandType">CommandType设置为：StoredProcedure 则执行存储过程\nCommandType设置为：Text 则执行SQL语句</param>
        /// <param name="sqlParameter">参数可为null</param>
        /// <returns>返回满足条件的DataTable对象</returns>
        public static DataTable ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] sqlParameter)
        {
            DataSet ds = new DataSet();
            SqlCommand sqlCommand = ExecuteCommand(sql, commandType, sqlParameter);
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}