数据访问层类示例

/*
 *制作人：蔺宜忠
 *创建时间：2010-3-14-9-14
 *说明：新闻表相关的方法
 *说明2：用于学习
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataSql
{
    /// <summary>
    /// 新闻表所用的方法
    /// </summary>
    public class NewsDAO
    {
        SQLHelper sqlHelper;

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public NewsDAO()
        {
            sqlHelper = new SQLHelper();
        }
        #endregion

        #region 查询所有新闻
        /// <summary>
        /// 查询所有新闻
        /// </summary>
        /// <returns>返回查询表</returns>
        public DataTable AllNews() 
        {
            return sqlHelper.ExecuteQuery("select * from news", CommandType.Text);
        }
        #endregion

        #region 选取最新五条新闻
        /// <summary>
        /// 选取最新五条新闻
        /// </summary>
        /// <returns>返回查询表</returns>
        public DataTable SelectNew5News()
        {
            return sqlHelper.ExecuteQuery("news_new5News", CommandType.StoredProcedure);
        }
        #endregion

        #region 选取最新五条热点新闻
        /// <summary>
        /// 选取最新五条热点新闻
        /// </summary>
        /// <returns>返回查询表</returns>
        public DataTable SelectNewHotNews()
        {
            return sqlHelper.ExecuteQuery("news_new5HotNews", CommandType.StoredProcedure);
        }
        #endregion

        #region 根据新闻类别ID取出该类别下所有新闻
        /// <summary>
        /// 根据类别ID取出该类别下所有新闻
        /// </summary>
        /// <param name="categoryID">新闻类别编号</param>
        /// <returns>返回取出新闻的列表</returns>
        public DataTable GetNewsByCaID(string categoryID)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parArry = { new SqlParameter("@caid", categoryID) };
            dt = sqlHelper.ExecuteQueryPara("news_getNewsByCaID", parArry, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 根据新闻ID取出该新闻
        /// <summary>
        /// 根据新闻ID取出该新闻
        /// </summary>
        /// <param name="ID">传入新闻的ID编号</param>
        /// <returns>返回该新闻</returns>
        public Model.News GetNewsByNewsID(string ID)
        {
            SqlParameter[] parArry = { new SqlParameter("@id", ID) };
            DataTable dt = sqlHelper.ExecuteQueryPara("news_getNewsByID", parArry, CommandType.StoredProcedure);
            Model.News news = new Model.News(dt.Rows[0]["id"].ToString(), dt.Rows[0]["title"].ToString(), dt.Rows[0]["content"].ToString(), dt.Rows[0]["createTime"].ToString(), dt.Rows[0]["caid"].ToString());
            return news;
        }
        #endregion

        #region 根据标题搜索新闻
        /// <summary>
        /// 根据标题搜索新闻
        /// </summary>
        /// <param name="title">查询标题</param>
        /// <returns>返回查询新闻表</returns>
        public DataTable GetNewsByTitle(string title)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parArry = { new SqlParameter("@title", title) };
            dt = sqlHelper.ExecuteQueryPara("news_getNewsByTitle", parArry, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 根据新闻内容搜索新闻
        /// <summary>
        /// 根据新闻内容搜索新闻
        /// </summary>
        /// <param name="content">内容片段</param>
        /// <returns>返回查询新闻表</returns>
        public DataTable GetNewsByContent(string content)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parArry = { new SqlParameter("@content", content) };
            dt = sqlHelper.ExecuteQueryPara("news_getNewsByContent", parArry, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 增加新闻
        /// <summary>
        /// 增加新闻
        /// </summary>
        /// <param name="news">新闻实体</param>
        /// <returns>添加是否成功</returns>
        public bool InsertNews(Model.News news)
        {
            bool flag = false;
            SqlParameter[] parArry = { new SqlParameter("@title", news.Title), new SqlParameter("@content", news.Content), new SqlParameter("@caid", news.CaID), };
            int count = sqlHelper.ExecuteNonQueryPara("news_insertNews", parArry, CommandType.StoredProcedure);
            if (count > 0)
            {
                flag = true;
            }
            return flag;
        }
        # endregion

        #region 修改新闻
        /// <summary>
        /// 修改新闻
        /// </summary>
        /// <param name="news">新闻实体</param>
        /// <returns>返回是否修改成功</returns>
        public bool UpdateNews(Model.News news)
        {
            bool flag = false;
            SqlParameter[] parArry = { new SqlParameter("@id", news.Id), new SqlParameter("@title", news.Title), new SqlParameter("@content", news.Content), new SqlParameter("@caID", news.CaID) };
            int count = sqlHelper.ExecuteNonQueryPara("news_updateNews", parArry, CommandType.StoredProcedure);
            if (count > 0)
            {
                flag = true;
            }
            return flag;
        }
        #endregion

        #region 删除新闻及其相关评论
        /// <summary>
        /// 删除新闻（及相关联评论）
        /// </summary>
        /// <param name="id">新闻编号</param>
        /// <returns>是否删除成功</returns>
        public bool Delete(string id)
        {
            bool flag = false;
            SqlParameter[] parArry = { new SqlParameter("@id", id) };
            int count = sqlHelper.ExecuteNonQueryPara("news_deleteNews", parArry, CommandType.StoredProcedure);
            if (count > 0)
            {
                flag = true;
            }
            return flag;
        }
        #endregion

    }
}