牛腩新闻发布系统（二）—SqlHelper完美篇

     

    相信我们对SqlHelper已不再陌生，SqlHelper被称为“数据库小助手”。我们可以在很多项目中看到它，但我们跟着牛腩老师做完牛腩系统后，品味他所教给我们的SqlHelper。可以用完美、简单来概括。

一、亮点

1.构造函数

     用构造函数定义公共的数据库连接对象。

2.重载

     用统一的方法，处理不同类型数据

3.概念小结

SqlConnection 类	到 SQL Server 数据库的打开的连接。 此类不能被继承。
SqlCommand类	对 SQL Server 数据库执行的一个 Transact-SQL 语句或存储过程。 此类不能被继承。
SqlDataReader类	提供一种从 SQL Server 数据库读取行的只进流的方式.
CommandBehavior 枚举	提供对查询结果和查询对数据库的影响的说明。

 

二、SqlHelper类

<span style="font-size:18px;">/*
 * 制作人：高晓青
 * 创建时间：2013年10月1日  13：46
 * 说明：数据库助手类
 * 版权所有：高晓青
 */
using System;
usingSystem.Collections.Generic;
using System.Linq;
using System.Text;
usingSystem.Threading.Tasks;
using System.Data;
usingSystem.Data.SqlClient;
usingSystem.Configuration;
 
namespace DAL
{
    public class SQLHelper
    {
        //通过构造函数实例化所需要的ADO对象
        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>
        /// <paramname="sql">增删改SQL语句或存储过程，传入参数数组</param>
        /// <returns>命令类型，影响行数</returns>
        public int ExecuteNonQuery(stringcmdText,CommandType ct)
        {
            //定义变量
            int res;
 
            //使用try…catch语句，使用完数据库后关闭数据库
            try
            {
                //将SQL语句与连接对象传入执行查询对象
                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>
        /// <paramname="sql">增删改SQL语句或存储过程</param>
        /// <paramname="paras">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(stringcmdText, SqlParameter[] paras, CommandType ct)
        {
            //定义返回影响函数变量
            int res;
 
            //using语句执行SQL语句
            using (cmd = newSqlCommand(cmdText, GetConn()))
           
            {
                //执行数据库的操作类别
                cmd.CommandType = ct;
 
                //加载参数
                cmd.Parameters.AddRange(paras);
 
                //执行查询并返回受影响的行数
                res = cmd.ExecuteNonQuery();
            }
 
            return res;
        }
 
        /// <summary>
        /// 该方法执行传入的SQL查询语句
        /// </summary>
        /// <paramname="sql">SQL查询语句或存储过程</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(stringcmdText, CommandType ct)
        {
            //实例化datatable对象
            DataTable dt = new DataTable();
 
            //传入需要执行的SQL语句与连接数据库对象 
            cmd = new SqlCommand(cmdText,GetConn());
 
            //定义执行的类型 
            cmd.CommandType = ct;
 
            using (sdr =cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                //将查询结果加载到dt中
                dt.Load(sdr);
            }
 
            return dt;
 
        }
 
        /// <summary>
        /// 该方法执行带参数的SQL查询语句
        /// </summary>
        /// <paramname="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(stringcmdText, SqlParameter[] paras, CommandType ct)
        {
            //实例化datatable对象
            DataTable dt = new DataTable();
 
            //传入需要执行的SQL语句与连接数据库对象
            cmd = new SqlCommand(cmdText,GetConn());
 
            //定义执行类型
            cmd.CommandType = ct;
 
            //参数加载
            cmd.Parameters.AddRange(paras);
           
            //将查询结果加载到dt
            using (sdr =cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
 
            //返回查询结果
            return dt;
 
        }
       
    }
 
}</span>

三、D层调用

 
<span style="font-size:18px;">1、通过构造函数实例化sqlhelper
 
        private SQLHelper sqlhelper;
        public NewsDAO()
        {
            sqlhelper = new SQLHelper();
        }
2、无参数的查询
 
#region选择全部新闻
        /// <summary>
        /// 选择全部新闻
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAll()
        {
            //定义dt用来存储返回数据
            DataTable dt = new DataTable();
 
            //sql语句
            string sql = "select * fromnews";
 
            //调用sqlhelper中封装好的方法
            dt = newSQLHelper().ExecuteQuery(sql, CommandType.Text);
 
            //返回受影响函数
            return dt;
        }
        #endregion
 
3、有参数的查询
 
        #region 根据类别ID取出该类别下的所有新闻
        /// <summary>
        /// 根据类别ID取出该类别下的所有新闻
        /// </summary>
        /// <paramname="id"></param>
        /// <returns></returns>
        public DataTable SelectByCaId(stringcaid)
        {
            //TODO:根据类别ID取出该类别下的所有新闻
            DataTable dt = new DataTable();
 
            //定义存储过程
            string procName ="News_SelectByCaId";
 
            //定义需要传入的参数
            SqlParameter[] paras = newSqlParameter[]{
                newSqlParameter("@caid",caid)};
 
            //调用封装好的方法
            dt =sqlhelper.ExecuteQuery(procName, paras, CommandType.StoredProcedure);
 
            //返回受影响的行数
            return dt;
        }
        #endregion
 
4、无参数的删除   
 
/// </summary> 
/// <paramname="Id">评论所属的新闻编号</param> 
///<returns>布尔值</returns> 
   public Boolean Delete(stringId) 
   { 
       Boolean flag = false; 
       string sql = "delete fromcomment where newsId='Id'"; 
       int res =sqlhelper.ExecuteNonQuery(sql, CommandType.Text); 
       if (res > 0) 
      { 
          flag = true; 
      } 
      return flag; 
   } 
 
5、有参数的删除
 
        /// <summary>
        /// 删除类别（联通其下的新闻及新闻评论一起删除）
        /// </summary>
        /// <paramname="id"></param>
        /// <returns></returns>
        public bool Delete(string id)
        {
            //定义bool变量并赋初值
            bool flag = false;
 
            //sql语句
            string sql = "delete fromcategory where id=@id";
 
            //传入参数
            SqlParameter[] paras = newSqlParameter[]{
            new SqlParameter("@id",id)};
 
            //调用方法
            int res =sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
 
            //连续按两下Tab可以显示代码段
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }</span>

四、SqldataReader扩展 

1、数据库连接 

（1）SqlDataReader未关闭前，数据库连接会一直保持打开状态，所以使用完毕应马上调用SqlDataReader.Close()关闭它。

（2）一个连接只能被一个SqlDataReader使用，应尽早关闭。

（3）使用完SqlDataReader后，关闭方法：

<1显示的调用数据库连接对象的Close方法关闭连接

<2调用Command对象的ExecuteReader方法时传递CommandBehavior.CloseConnection这个枚举变量，这样在调用SqlDataReader的Close方法时会自动关闭数据库连接。

（4）使用SqlDataReader获取多条记录时，如果没有访问到取出记录的末尾时想要关闭SqlDataReader,应该先调用Command对象的Cancel方法，然后再调用SqlDataReader的Close方法。Command对象的Cancel方法使得数据库不再把SqlDataReader中未访问的数据发送到调用端，如果不调用此方法直接关闭SqlDataReader，数据库会发送和SqlDataReader未访问数据等长的空数据流到调用端。

2、参数传递 

（1）使用SqlDataReader时尽量使用和数据库字段类型匹配的方法来取得相应的值，比如对于整形的字段使用GetInt32,对字符类型的字段使用GetString。这样会减少因为类型不一致而额外增加的类型转换操作。

（2）如果想通过SqlCommand的ExecuteReader方法获取存储过程的返回值或者输出参数，必须先调用SqlDataReader的Close方法后，才能获取输出参数的值或者返回值。

（3）如果使用SqlDataReader只返回一条记录，那么在调用Command的ExecuteReader方法时，指定CommandBehavior.SingleRow参数，这个参数的是否使用对SQL Server .NET Data Provider没有什么影响，但是当你使用OLEDB .NET Data Provider时，指定这个参数后，DataPrivider内部将使用IRow接口，而不是使用相对来说耗费资源的IRowSet接口。