essaydao.cs

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

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

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

		//添加随笔
		public int Insert(EssayVO essayVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "insert into Essay([Subject],Content,[Time],[View],FeedBack,CategoryID,BlogID) values('"+essayVO.Subject+"','"+essayVO.Content+"','"+essayVO.Time+"',0,0,"+essayVO.CategoryID+","+essayVO.BlogID+")";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			if(flag>0)
			{
				BlogDAO bDAO = new BlogDAO();
				flag += bDAO.InsertEssay(essayVO.BlogID);
			}
			Connection.Close();
			return flag;
		}

		//修改随笔信息
		public int Update(EssayVO essayVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Essay set [Subject] = '"+essayVO.Subject+"',Content = '"+essayVO.Content+"',[Time] = '"+essayVO.Time+"' where EssayID = " + essayVO.EssayID;
			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 Essay set [View] = [View] + 1 where EssayID = " + 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 Essay set FeedBack = FeedBack + 1 where EssayID = " + 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 Essay set FeedBack = FeedBack - 1 where EssayID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除随笔信息
		public int Delete(int id)
		{
			EssayVO eVO = Load(id);
			int flag = 0;
			Connection.Open();
			string sql = "delete from Essay where EssayID = " + 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.DeleteEssay(eVO.BlogID,1);
				flag += bDAO.DeleteFeedBack(eVO.BlogID,fDAO.LoadCount(eVO.BlogID,eVO.CategoryID,id,"e",""));
				flag += fDAO.Delete(id,"e");
			}
			return flag;
		}

		//删除某分类下的随笔信息
		public int DeleteC(int CategoryID)
		{
			int flag = 0;
			Connection.Open();
			string sql = "delete from Essay 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 Essay 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)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select * from Essay 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 EssayID desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas,startPage,pageSize, "Essay");
			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 Essay 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;
		}

		//选取最新随笔列表
		public DataSet LoadTopList(int num)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select top " + num + " * from Essay order by EssayID desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "TopEssay" );
			Connection.Close();
			return Datas;
		}

		//选取浏览数最多的随笔列表
		public DataSet LoadTopViewList(int num){
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select top " + num + " * from Essay order by [view] desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "TopView" );
			Connection.Close();
			return Datas;
		}

		//选取评论数最多的随笔列表
		public DataSet LoadTopFeedBackList(int num)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select top " + num + " * from Essay order by feedback desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "TopFeedBack" );
			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 Essay 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, "EssayDoc" );
			Connection.Close();
			return Datas;
		}

		//选取随笔发表日期
		public int[] LoadDayArray(int BlogID,int intYear,int intMonth)
		{
			int[] intArray = new int[31]; 
			Connection.Open();
			string sql = "select Convert(datetime,[time]) as intDate from Essay where year(Convert(datetime,[time])) = " + intYear + " and month(Convert(datetime,[time])) = " + intMonth + " and BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			int i = 0;
			while(tmpReader.Read()) 
			{ 
				if( i==0 ) 
				{ 
					intArray[i] = tmpReader.GetDateTime(0).Day; 
					i++; 
				} 
				else if( tmpReader.GetDateTime(0).Day != intArray[i-1] ) 
				{ 
					intArray[i] = tmpReader.GetDateTime(0).Day; 
					i++; 
				} 
			} 
			Connection.Close();
			return intArray;
		}

		//选取随笔列表信息
		public DataView LoadList(int BlogID)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql1 = "select distinct(left([Time],10)) Times from Essay where BlogID = " + BlogID + " order by left([Time],10) desc";
			string sql = "select *,left([Time],10) Times from Essay where BlogID = " + BlogID + " and left(Time,10) in (select distinct(left([Time],10)) Times from Essay where BlogID = " + BlogID + " ) order by [Time] desc";
			SqlDataAdapter sqlDB = new SqlDataAdapter(sql1,Connection);
			sqlDB.Fill( Datas, "EssayTime");
			DataView dv=new DataView(Datas.Tables["EssayTime"]);

			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "Essay" );

			Datas.Tables["Essay"].Columns.Add("Username");
			MyBlog.Core.Blog.BlogDAO bDAO = new MyBlog.Core.Blog.BlogDAO();
			for(int i=0;i<Datas.Tables["Essay"].Rows.Count;i++){
				Datas.Tables["Essay"].Rows[i]["Username"] = bDAO.Load(BlogID).Username;
			}

			Datas.Relations.Add("myRelation",Datas.Tables["EssayTime"].Columns["Times"],Datas.Tables["Essay"].Columns["Times"]);

			Connection.Close();
			return dv;
		}
	}
}