articleaccessor.cs

本代码相互要实现试问系统的查询

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Common;
using Common.Entities;


namespace DAL.Accessor
{
    public class ArticleAccessor
    {
        //返回头条新闻
        public List<ArticleData> GetList(int num)
        {
            List<ArticleData> list = new List<ArticleData>();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetHeadLine", null);
            while (reader.Read())
            {
                ArticleData articleData = new ArticleData();
                articleData.ArticleId = int.Parse(reader["ARticleId"].ToString());
                articleData.Title = reader["Title"].ToString();
                articleData.DateTime = DateTime.Parse(reader["DateTime"].ToString());
                articleData.ClassName = reader["ClassName"].ToString();
                list.Add(articleData);
                if (list.Count >= num)
                {
                    break;
                }
            }
            reader.Dispose();
            return list;
        }
        //返回所有热点新闻
        public List<ArticleData> GetList(int pageIndex,int pageSize)
        {
            SqlParameter[] sqlParameters = {
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int)
            };
            sqlParameters[0].Value = pageIndex;
            sqlParameters[1].Value = pageSize;
            List<ArticleData> list = new List<ArticleData>();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetHeadLinePage", sqlParameters);
            while (reader.Read())
            {
                ArticleData articleData = new ArticleData();
                articleData.ArticleId = int.Parse(reader["ARticleId"].ToString());
                articleData.Title = reader["Title"].ToString();
                articleData.DateTime = DateTime.Parse(reader["DateTime"].ToString());
                articleData.ClassName = reader["ClassName"].ToString();
                list.Add(articleData);               
            }
            reader.Dispose();
            return list;
        }
        //返回单一新闻类型的所有新闻
        public List<ArticleData> GetList(int classid,int pageIndex, int pageSize)
        {
            SqlParameter[] sqlParameters = {
                new SqlParameter("@classid",SqlDbType.Int),
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int)
            };
            sqlParameters[0].Value = classid;
            sqlParameters[1].Value = pageIndex;
            sqlParameters[2].Value = pageSize;
            List<ArticleData> list = new List<ArticleData>();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_getClassNews", sqlParameters);
            while (reader.Read())
            {
                ArticleData articleData = new ArticleData();
                articleData.ArticleId = int.Parse(reader["ARticleId"].ToString());
                articleData.Title = reader["Title"].ToString();
                articleData.DateTime = DateTime.Parse(reader["DateTime"].ToString());
                articleData.ClassName = reader["ClassName"].ToString();
                list.Add(articleData);
            }
            reader.Dispose();
            return list;
        }
        //返回所有留言
        public SqlDataReader GetMessageList(int pageIndex, int pageSize)
        {
            SqlParameter[] sqlParameters = {
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int)
            };
            sqlParameters[0].Value = pageIndex;
            sqlParameters[1].Value = pageSize;

            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetMessage", sqlParameters);
            return reader;
        }
        //返回一条新闻 Article_getClassNews
        public ArticleData GetModel(int articleId)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@articleId",SqlDbType.Int,4)
            };
            sqlParameter[0].Value = articleId;
            ArticleData articleData = new ArticleData();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetModel", sqlParameter);
            reader.Read();
            articleData.Title = reader["Title"].ToString();
            articleData.Content = reader["Content"].ToString();
            articleData.DateTime = DateTime.Parse(reader["DateTime"].ToString());
            articleData.Hits = int.Parse(reader["Hits"].ToString());
            articleData.MemberName = reader["MemberName"].ToString();
            articleData.ClassName = reader["ClassName"].ToString();
            articleData.TitleImg = reader["TitleImg"].ToString();
            articleData.ImgNews = bool.Parse(reader["ImgNews"].ToString());
            articleData.HeadLine = bool.Parse(reader["HeadLine"].ToString());
            articleData.ClassId = int.Parse(reader["ClassId"].ToString());
            articleData.Topicid = int.Parse(reader["TopicId"].ToString());
            reader.Dispose();
            return articleData;
        }
        //添加新闻
        public void Add(ArticleData articleData)
        {
            SqlParameter[] sqlParameters = {
                new SqlParameter("@classId",SqlDbType.Int),
                new SqlParameter("@title",SqlDbType.NVarChar),
                new SqlParameter("@titleImg",SqlDbType.NVarChar),
                new SqlParameter("@imgNews",SqlDbType.Bit),
                new SqlParameter("@headLine",SqlDbType.Bit),
                new SqlParameter("@content",SqlDbType.NVarChar),
                new SqlParameter("@topicid",SqlDbType.Int),
                new SqlParameter("@MemberName",SqlDbType.NVarChar)
            };
            sqlParameters[0].Value = articleData.ClassId;
            sqlParameters[1].Value = articleData.Title;
            sqlParameters[2].Value = articleData.TitleImg;
            sqlParameters[3].Value = articleData.ImgNews;
            sqlParameters[4].Value = articleData.HeadLine;
            sqlParameters[5].Value = articleData.Content;
            sqlParameters[6].Value = articleData.Topicid;
            sqlParameters[7].Value = articleData.MemberName;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "Article_ADD", sqlParameters);
            }
        }
        //返回所有新闻
        public List<ArticleData> GetAll(int pageIndex, int pageSize)
        {
            SqlParameter[] sqlParameters = {
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int)
            };
            sqlParameters[0].Value = pageIndex;
            sqlParameters[1].Value = pageSize;
            List<ArticleData> list = new List<ArticleData>();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetAll", sqlParameters);
            while (reader.Read())
            {
                ArticleData articleData = new ArticleData();
                articleData.ArticleId = int.Parse(reader["ARticleId"].ToString());
                articleData.Title = reader["Title"].ToString();
                articleData.DateTime = DateTime.Parse(reader["DateTime"].ToString());
                articleData.ClassName = reader["ClassName"].ToString();
                articleData.ImgNews = bool.Parse(reader["ImgNews"].ToString());
                list.Add(articleData);
            }
            reader.Dispose();
            return list;
        }
        //修改新闻
        public void Update(ArticleData articleData)
        {
            SqlParameter[] sqlParameters = {                
                new SqlParameter("@classId",SqlDbType.Int),
                new SqlParameter("@title",SqlDbType.NVarChar),