articleaccessor.cs

入门级asp.netC＃网站三层系统开发

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 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),
                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),
                new SqlParameter("@articleId",SqlDbType.Int)          
            };            
            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;
            sqlParameters[8].Value = articleData.ArticleId;        

            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "Article_Update", sqlParameters);
            }
        }
        //删除新闻
        public void Delete(int id)
        {
            SqlParameter[] sqlParameters = { 
                new SqlParameter("@articleId",SqlDbType.Int)
            };
            sqlParameters[0].Value = id;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "Article_Delete", sqlParameters);
            }
        }
        //返回图片新闻
        public List<ArticleData> GetImgNews(int num)
        {            
            List<ArticleData> list = new List<ArticleData>();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_GetImgNews", 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();
                articleData.TitleImg = reader["TitleImg"].ToString();
                articleData.ImgNews = bool.Parse(reader["ImgNews"].ToString());
                list.Add(articleData);
                if (list.Count >= num)
                {
                    break;
                }
            }
            reader.Dispose();
            return list;
        }
        public List<ArticleData> GetImgNews(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_GetImgNewsPage", 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.TitleImg = reader["TitleImg"].ToString();
                articleData.ImgNews = bool.Parse(reader["ImgNews"].ToString());
                list.Add(articleData);                
            }
            reader.Dispose();
            return list;
        }
        //返回新闻总数
        public int GetCount()
        {
            using(SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                int count = int.Parse(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "Article_GetCount", null).ToString());
                return count;           
            }
           
        }
        //返回图片新闻数
        public int GetImgNewsCount()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                int count = int.Parse(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "Article_GetImgCount", null).ToString());
                return count;    
            }
           
        }
        //返回头条新闻总数
        public int GetHeadNewsCount()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                int count = int.Parse(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "Article_GetHeadCount", null).ToString());
                return count;
            }

        }
        //shujun 返回新闻标题列表-->存储过程Article_List 
        public List<ArticleData> GetNewList(int newClassID)
        {
            List<ArticleData> list = new List<ArticleData>();

            SqlParameter[] sqlParamenter = { 
                new SqlParameter("@ClassID",SqlDbType.Int,4)
            };
            sqlParamenter[0].Value = newClassID;

            SqlDataReader read = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Article_List", sqlParamenter);

            while (read.Read())
            {
                ArticleData article = new ArticleData();

                article.ArticleId = int.Parse(read["ArticleId"].ToString());
                article.ClassId = int.Parse(read["ClassId"].ToString());
                article.Title = read["Title"].ToString();
                article.DateTime = DateTime.Parse(read["DateTime"].ToString());

                list.Add(article);
            }


            return list;

        }
    }
}