重构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的过程。