SQLHelper助手类【代码】

SqlHelper是一个基于.NET Framework的数据库操作组件，组件中包含数据库操作方法。SqlHelper用于简化你重复的去写那些数据库连接SqlConnection，SqlCommand,SqlDataReader等等。SqlHelper封装过后通常是只需要给方法传入一些参数如数据库连接字符串、SQL参数等，就可以访问数据库了，很方便。

    public class SQLHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;
        public SQLHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConn()
        {
            if (conn.State ==ConnectionState .Closed )
            {
                conn.Open();
            }          
            return conn;
        }

        /// <summary>
        /// 该方法执行不带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">增删改SQL语句或者存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText,CommandType ct)
        {
            int res ;
            try
            {
                cmd = new SqlCommand(cmdText , GetConn ());
                cmd.CommandType = ct;
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State ==ConnectionState .Open)
                {
                    conn.Close();
                }              
            }
            return res;
        }

        /// <summary>
        /// 执行带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">增删改SQL语句或存储过程</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText,SqlParameter [] paras,CommandType ct)
        {
            int res;
            using (cmd=new SqlCommand (cmdText ,GetConn ()))
            {
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);              
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }

        /// <summary>
        /// 执行查询SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">查询SQL语句或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdText,CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText , GetConn ());
            cmd.CommandType = ct;
            using (sdr=cmd .ExecuteReader (CommandBehavior .CloseConnection ))
            {
                dt.Load(sdr);
            }
            return dt;
        }

        /// <summary>
        /// 执行带参数的查询SQL语句或存储过程
        /// </summary>
        /// <param name="cmdText">查询SQL语句或存储过程</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(cmdText, GetConn());
            cmd.CommandType = ct;
            using (sdr=cmd.ExecuteReader (CommandBehavior .CloseConnection ))
            {
                dt.Load(sdr);
            }
            return dt;
        }

    }