做新闻发布系统这个项目的时候又一次接触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的过程。
