重构SQLHelper

做新闻发布系统这个项目的时候又一次接触SQLHelper这个类，上次机房也遇到了这个类SQLHelper初见，这次与上次有些区别，对SQLHelper进行了重构。

using System.Data;using System.Data.SqlClient;using System.Configuration;namespace DAL{    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);        }        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>int</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="ct">命令类型</param>        /// <returns>int</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;            cmd.Parameters.AddRange(paras);            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))            {                dt.Load(sdr);            }            return dt;        }    }}

应用

using System.Data;using System.Data.SqlClient;using Model;namespace DAL{   /// <summary>   /// 新闻类别表操作   /// </summary>    public class categoryDAO    {        private SQLHelper sqlhelper = null;        public categoryDAO() //构造函数        {            sqlhelper = new SQLHelper();        }        //取出当前所有新闻分类        public DataTable SelectAll()        {            DataTable dt = new DataTable();            string sql = "select * from category";            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);            return dt;        }        /// <summary>        ///添加新闻类别方法        /// </summary>        /// <param name="caName">新闻类别名</param>        /// <returns>返回bool</returns>        public bool Insert(string caName)        {            bool flag = false;            string sql = "insert into category (name) values (@caName)";            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@caName", caName) };            int res = sqlhelper.ExecuteNonQuery(sql, paras,CommandType.Text );            if (res > 0)            {                flag = true;            }            return flag;        }//查询类别        public bool IsExists(string caName)        {            bool flag = false;            string sql = "select * from category where [name]='" + caName + "'";            DataTable dt = sqlhelper.ExecuteQuery(sql,CommandType .Text );            if (dt.Rows.Count > 0)            {                flag = true;            }            return flag;        }        /// <summary>        /// 修改类别（连同其下的新闻及评论）        /// </summary>        /// <param name="ca">实体类</param>        /// <returns>bool</returns>        public bool Update(categoryModel ca)        {            bool flag = false;            string sql = "update category set [name]=@caName where id=@id";            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@caName", ca.Name),            new SqlParameter ("@id",ca.ID)};            int res = sqlhelper.ExecuteNonQuery(sql, paras,CommandType.Text );            if (res > 0)            {                flag = true;            }            return flag;        }        //删除类别        public bool Delete(categoryModel ca)        {            bool flag = false;            string sql = "delete from category where id=@id";            SqlParameter[] paras = new SqlParameter[] {             new SqlParameter ("@id",ca.ID  )};            int res = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);            if (res > 0)            {                flag = true;            }            return flag;        }    }}

构造方法：又叫构造函数，其实就是对类进行初始化。构造方法与类同名，无返回值，也不需要void，在new时候调用。

所有类都有构造方法，如果你不编码则系统默认生成空的构造方法，若你有定义的构造方法，那么默认的构造方法就会生效。

上面就应用了构造方法，将一些重复的代码放在里面，等一实例化的时候构造方法就会生效，就减少了代码量，也提高了运行速率。

Using语句

SQLHelper类里面用到了using语句代替了try catch语句，这还是头一次使用，具体怎样用见下面这个链接：using语句

总结：知识就不不断在N上面加1的过程。