blogdao.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using WesternByte.MyBlog.Core.Category;
using WesternByte.MyBlog.Core.Article;
using WesternByte.MyBlog.Core.Essay;
using WesternByte.MyBlog.Core.FeedBack;
using WesternByte.MyBlog.Core.Href;
using WesternByte.MyBlog.Core.Picture;
using System.IO;

namespace WesternByte.MyBlog.Core.Blog
{
	/// <summary>
	/// BlogDAO 的摘要说明。
	/// </summary>
	public class BlogDAO:DbObject
	{
		public string path;

		//构造函数
		public BlogDAO (){}

		public BlogDAO(string webpath){
			this.path = webpath;
		}

		//选取某个Blog信息
		public BlogVO Load(int id)
		{
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select * from Blog where BlogID = " + id,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			BlogVO blogVO = new BlogVO();
			if(tmpReader.Read()){
				blogVO.BlogID = Convert.ToInt32(tmpReader["BlogID"].ToString());
				blogVO.Username = tmpReader["Username"].ToString();
				blogVO.Password = tmpReader["Password"].ToString();
				blogVO.Name = tmpReader["Name"].ToString();
				blogVO.Email = tmpReader["Email"].ToString();
				blogVO.Subject = tmpReader["Subject"].ToString();
				blogVO.Description = tmpReader["Description"].ToString();
				blogVO.EntryNum = Convert.ToInt32(tmpReader["EntryNum"].ToString());
				blogVO.Role = Convert.ToInt32(tmpReader["Role"].ToString());
				blogVO.EssayCount = Convert.ToInt32(tmpReader["EssayCount"].ToString());
				blogVO.ArticleCount = Convert.ToInt32(tmpReader["ArticleCount"].ToString());
				blogVO.FeedBackCount = Convert.ToInt32(tmpReader["FeedBackCount"].ToString());
			}
			tmpReader.Close();
			Connection.Close();
			return blogVO;
		}

		//检测博客是否存在
		private int Check(string username)
		{
			int flag = 0;
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select BlogID from Blog where Username = '" + username + "'",Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if (tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["BlogID"].ToString());
			}
			tmpReader.Close();
			Connection.Close();
			return flag;
		}

		//检测博客是否存在2
		private bool Check(string username,int id)
		{
			bool flag = false;
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select BlogID from Blog where Username = '" + username + "' and BlogID <> " + id,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if (!tmpReader.Read())
			{
				flag = true;
			}
			tmpReader.Close();
			Connection.Close();
			return flag;
		}

		//检测博客登录
		public int Check(string username,string password)
		{
			int flag = 0;
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select BlogID from Blog where Username = @Username and Password = @Password",Connection);
			sqlComm.Parameters.Add(new SqlParameter("@Username",SqlDbType.NVarChar, 50));
			sqlComm.Parameters.Add(new SqlParameter("@Password",SqlDbType.NVarChar, 50));
			sqlComm.Parameters["@UserName"].Value=username;
			sqlComm.Parameters["@Password"].Value=password;
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if (tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["BlogID"].ToString());
			}
			tmpReader.Close();
			Connection.Close();
			return flag;
		}

		//新博客注册
		public int Insert(BlogVO blogVO){
			int flag = 0;
			if(Check(blogVO.Name)==0)
			{
				Connection.Open();
				string sql = "insert into Blog(Username,Password,[Name],Email,[Subject],Description,EntryNum,Role,EssayCount,ArticleCount,FeedBackCount) values(@Username,@Password,@Name,@Email,@Subject,@Description,10,1,0,0,0)";
				SqlCommand sqlComm = new SqlCommand(sql,Connection);
				sqlComm.Parameters.Add(new SqlParameter("@Username",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Password",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Email",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Subject",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar, 100));
				sqlComm.Parameters["@UserName"].Value=blogVO.Username;
				sqlComm.Parameters["@Password"].Value=blogVO.Password;
				sqlComm.Parameters["@Name"].Value=blogVO.Name;
				sqlComm.Parameters["@Email"].Value=blogVO.Email;
				sqlComm.Parameters["@Subject"].Value=blogVO.Subject;
				sqlComm.Parameters["@Description"].Value=blogVO.Description;
				flag = sqlComm.ExecuteNonQuery();
				Connection.Close();
			}
			else{
				flag = -1; //账户名重复
			}
			if(flag>0)
			{
				flag = Check(blogVO.Username);
				DirectoryInfo dif = new DirectoryInfo(path+"\\Images\\Pictures\\" + flag);
				dif.Create();
			}
			return flag;
		}

		//修改博客信息
		public int Update(BlogVO blogVO){
			int flag = 0;
			if(Check(blogVO.Username,blogVO.BlogID))
			{
				Connection.Open();
				string sql = "update Blog set Username = @Username,Name = @Name,Email = @Email,Subject = @Subject,Description = @Description where BlogID = @BlogID";
				SqlCommand sqlComm = new SqlCommand(sql,Connection);
				sqlComm.Parameters.Add(new SqlParameter("@Username",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Email",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Subject",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar, 100));
				sqlComm.Parameters.Add(new SqlParameter("@BlogID",SqlDbType.Int, 4));
				sqlComm.Parameters["@UserName"].Value=blogVO.Username;
				sqlComm.Parameters["@Name"].Value=blogVO.Name;
				sqlComm.Parameters["@Email"].Value=blogVO.Email;
				sqlComm.Parameters["@Subject"].Value=blogVO.Subject;
				sqlComm.Parameters["@Description"].Value=blogVO.Description;
				sqlComm.Parameters["@BlogID"].Value=blogVO.EntryNum;
				flag = sqlComm.ExecuteNonQuery();
				Connection.Close();
			}
			else{
				flag = -1; //账户名重复
			}
			return flag;
		}

		//修改博客密码
		public int UpdatePwd(string password,int id){
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set Password = '" + password + "' where BlogID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除博客信息
		public int Delete(int id){
			int flag = 0;
			Connection.Open();
			string sql = "delete from Blog where BlogID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			if(flag>0)
			{
				CategoryDAO cDAO = new CategoryDAO();
				ArticleDAO aDAO = new ArticleDAO();
				EssayDAO eDAO = new EssayDAO();
				FeedBackDAO fDAO = new FeedBackDAO();
				HrefDAO hDAO = new HrefDAO();
				PictureDAO pDAO = new PictureDAO();
				flag += cDAO.DeleteB(id);
				flag += aDAO.DeleteB(id);
				flag += eDAO.DeleteB(id);
				flag += fDAO.DeleteB(id);
				flag += hDAO.DeleteB(id);
				flag += pDAO.DeleteB(id);
			}
			if(flag>0)
			{
				DirectoryInfo dif = new DirectoryInfo(path+"\\Images\\Pictures\\" + id);
				dif.Delete(true);
			}
			return flag;
		}

		//添加随笔数
		public int InsertEssay(int BlogID){
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set essayCount = essayCount + 1 where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//减少随笔数
		public int DeleteEssay(int BlogID,int num)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set essayCount = essayCount - " + num + " where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//添加文章数
		public int InsertArticle(int BlogID)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set articleCount = articleCount + 1 where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//减少文章数
		public int DeleteArticle(int BlogID,int num)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set articleCount = articleCount - " + num + " where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//添加评论数
		public int InsertFeedBack(int BlogID)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set feedBackCount = feedBackCount + 1 where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//减少评论数
		public int DeleteFeedBack(int BlogID,int num)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Blog set feedBackCount = feedBackCount - " + num + " where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//选取博客列表信息
		public DataSet LoadList()
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select * from Blog where role=1 order by BlogID";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "Blog" );
			Connection.Close();
			return Datas;
		}

		//选取博客２０名排行列表信息
		public DataSet LoadTopList(int num)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select top " + num + " * from Blog where role=1 order by EssayCount desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			SqlCommand command = new SqlCommand(sql,Connection);
			sqlDA.SelectCommand = command;
			sqlDA.Fill( Datas, "Blog" );
			Connection.Close();
			return Datas;
		}


		//选取Blog总数
		public int LoadCount()
		{
			int flag = 0;
			Connection.Open();
			string sql = "select count(*) as count from Blog where role=1";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if(tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["Count"].ToString());
			}
			return flag;
		}
	}
}