articledao.cs

ASP.net网站开发四“酷”全书：新闻、论坛、电子商城、博客_源码

using System;
using System.Data;
using System.Data.SqlClient;
using WesternByte.MyBlog.Core.Blog;
using WesternByte.MyBlog.Core.Category;
using WesternByte.MyBlog.Core.FeedBack;

namespace WesternByte.MyBlog.Core.Article
{
	/// <summary>
	/// ArticleDAO 的摘要说明。
	/// </summary>
	public class ArticleDAO : DbObject
	{
		//选取某个文章信息
		public ArticleVO Load(int id)
		{
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select * from Article where ArticleID = " + id,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			ArticleVO articleVO = new ArticleVO();
			if(tmpReader.Read())
			{
				articleVO.ArticleID = Convert.ToInt32(tmpReader["ArticleID"].ToString());
				articleVO.BlogID = Convert.ToInt32(tmpReader["BlogID"].ToString());
				articleVO.Subject = tmpReader["Subject"].ToString();
				articleVO.Content = tmpReader["Content"].ToString();
				articleVO.Time = tmpReader["Time"].ToString();
				articleVO.View = Convert.ToInt32(tmpReader["View"].ToString());
				articleVO.FeedBack = Convert.ToInt32(tmpReader["FeedBack"].ToString());
			}
			tmpReader.Close();
			Connection.Close();
			return articleVO;
		}

		//添加文章
		public int Insert(ArticleVO articleVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "insert into Article([Subject],Content,[Time],[View],FeedBack,CategoryID,BlogID) values('"+articleVO.Subject+"','"+articleVO.Content+"','"+articleVO.Time+"',0,0,"+articleVO.CategoryID+","+articleVO.BlogID+")";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			if(flag>0)
			{
				BlogDAO bDAO = new BlogDAO();
				flag += bDAO.InsertArticle(articleVO.BlogID);
			}
			return flag;
		}

		//修改文章信息
		public int Update(ArticleVO articleVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Article set [Subject] = '"+articleVO.Subject+"',Content = '"+articleVO.Content+"',[Time] = '"+articleVO.Time+"' where ArticleID = " + articleVO.ArticleID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//阅读文章信息
		public int View(int id)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Article set [View] = [View] + 1 where ArticleID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//评论文章信息
		public int FeedBack(int id)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Article set FeedBack = FeedBack + 1 where ArticleID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}


		//删除文章评论数
		public int DFeedBack(int id)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Article set FeedBack = FeedBack - 1 where ArticleID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除文章信息
		public int Delete(int id)
		{
			ArticleVO aVO = Load(id);
			int flag = 0;
			Connection.Open();
			string sql = "delete from Article where ArticleID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			if(flag>0)
			{
				BlogDAO bDAO = new BlogDAO();
				FeedBackDAO fDAO = new FeedBackDAO();
				flag += bDAO.DeleteArticle(aVO.BlogID,1);
				flag += bDAO.DeleteFeedBack(aVO.BlogID,fDAO.LoadCount(aVO.BlogID,aVO.CategoryID,id,"a",""));
				flag += fDAO.Delete(id,"a");
			}
			return flag;
		}

		//删除某分类下的文章信息
		public int DeleteC(int CategoryID){
			int flag = 0;
			Connection.Open();
			string sql = "delete from Article where CategoryID = " + CategoryID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除某Blog下的文章信息
		public int DeleteB(int BlogID)
		{
			int flag = 0;
			Connection.Open();
			string sql = "delete from Article where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//选取文章列表信息
		public DataSet LoadList(int BlogID,int CategoryID,int startPage,int pageSize,string key,string time)
		{
			Connection.Open();
			DataSet Datas=new DataSet();
			string sql = "select * from Article where 1=1";
			if(BlogID!=0)sql+= " and BlogID = " + BlogID;
			if(CategoryID!=0)sql+= " and CategoryID = " + CategoryID;
			if(key!="")sql += " and (Subject like '%" + key + "%' or Content like '%" + key + "%'";
			if(time!="")sql += " and [time] like '%" + time + "%'";
			sql += " order by ArticleID desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas,startPage,pageSize, "Article" );
			Connection.Close();
			return Datas;
		}

		//选取文章日期
		public DataSet LoadMonth(int BlogID)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select left([time],7) as [time],Count(*) as count from Article group by left([time],7) order by left([time],7) desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			SqlCommand command = new SqlCommand(sql,Connection);
			sqlDA.SelectCommand = command;
			sqlDA.Fill( Datas, "ArticleDoc" );
			Connection.Close();
			return Datas;
		}

		//选取文章总数
		public int LoadCount(int BlogID,int CategoryID,string key,string time)
		{
			int flag = 0;
			Connection.Open();
			string sql = "select count(*) as count from Article where 1=1";
			if(BlogID!=0)sql+= " and BlogID = " + BlogID;
			if(CategoryID!=0)sql+= " and CategoryID = " + CategoryID;
			if(key!="")sql += " and (Subject like '%" + key + "%' or Content like '%" + key + "%'";
			if(time!="")sql += " and [time] like '%" + time + "%'";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if(tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["Count"].ToString());
			}
			Connection.Close();
			return flag;
		}
	}
}