数据库访问通用类(第四版)

///<summary>
///版本：SQLHelper 2.0
///修改版本：SQLHelper 1.3
///模块编号：1001
///作用：数据库访问通用类
///作者：邓政军
///修改日期：2012-1-5
///新增存储过程
///新增显示连接关闭操作
///</summary>

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

namespace PIMgr.DAL.DBHelper
{
    public class SQLHelper
    {
        #region 私有字段
        SqlConnection conn;
        SqlCommand cmd;
        string connStr = ConfigurationManager.ConnectionStrings["PIMgrConnectionString"].ConnectionString;
        #endregion

        /// <summary>
        /// 打开连接
        /// </summary>
        public void ConnectionOpen()
        {
            if (conn == null)
            {
                conn = new SqlConnection(this.connStr);//建立一个连接
                conn.Open();
            }
            if (conn.State.Equals(ConnectionState.Closed))//如果连接状态是关闭的
            {
                conn.Open();
            }
        }


        /// <summary>
        /// 关闭连接
        /// </summary>
        public void ConnectionClose()
        {
            if (conn != null && conn.State == ConnectionState.Open)//如果连接存在
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行查询并将结果返回至DataTable中
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <param name="parameters">可能带的参数</param>
        /// <returns>返回一张查询结果表</returns>
        public DataTable ExecuteDataTable(string strSql, params SqlParameter[] parameters)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                DataSet ds = new DataSet();
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    ConnectionClose();
                    return dt;
                }
            }
        }

        /// <summary>
        /// (重载)执行查询并将结果返回至DataTable中
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>返回一张查询结果表</returns>
        public DataTable ExecuteDataTable(string strSql)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                DataSet ds = new DataSet();
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    ConnectionClose();
                    return dt;
                }
            }
        }


        /// <summary>
        /// 执行对数据的增删改操作
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQuery(string strSql, params SqlParameter[] parameters)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                int affectedRows = cmd.ExecuteNonQuery();
                ConnectionClose();
                return affectedRows;
            }
        }

        /// <summary>
        /// (重载)执行对数据的增删改操作
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns>返回影响行数</returns>
        public int ExecuteNonQuery(string strSql)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                int affectedRows = cmd.ExecuteNonQuery();
                ConnectionClose();
                return affectedRows;
            }
        }

        /// <summary>
        /// 执行查询并返回结果集中第一行第一列的值
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string strSql, params SqlParameter[] parameters)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                object result = cmd.ExecuteScalar();
                ConnectionClose();
                return result;
            }
        }

        /// <summary>
        /// (重载)执行查询并返回结果集中第一行第一列的值
        /// </summary>
        /// <param name="strSql">查询语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string strSql)
        {
            ConnectionOpen();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                object result = cmd.ExecuteScalar();
                ConnectionClose();
                return result;
            }
        }

        //

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="pars">参数</param>
        /// <returns>影响行数</returns>
        public int ProcedureExecute(string procName, SqlParameter[] pars)
        {
            //TODO:使用事务机制
            ConnectionOpen();
            using (cmd = new SqlCommand(procName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(pars);
                int affectedRows = cmd.ExecuteNonQuery();
                ConnectionClose();
                return affectedRows;
            }
        }

        #region 通过存储过程读取数据返回SqlDataReader,带参数
        /// <summary>
        /// 通过存储过程返回SqlDataReader,带参数
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="pars">参数数组</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader GetDataReader(string procName, SqlParameter[] pars)
        {
            ConnectionOpen();
            using (cmd = new SqlCommand(procName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(pars);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion


    }
}