categorydao.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using WesternByte.MyBlog.Core.Blog;
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.Category
{
	/// <summary>
	/// CategoryDAO 的摘要说明。
	/// </summary>
	public class CategoryDAO : DbObject
	{
		public string path;

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

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

		//选取某个分类信息
		public CategoryVO Load(int id)
		{
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select * from Category where CategoryID = " + id,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			CategoryVO categoryVO = new CategoryVO();
			if(tmpReader.Read())
			{
				categoryVO.CategoryID = Convert.ToInt32(tmpReader["CategoryID"].ToString());
				categoryVO.BlogID = Convert.ToInt32(tmpReader["BlogID"].ToString());
				categoryVO.Name = tmpReader["Name"].ToString();
				categoryVO.Category = tmpReader["Type"].ToString();
				categoryVO.Description = tmpReader["Description"].ToString();
			}
			tmpReader.Close();
			Connection.Close();
			return categoryVO;
		}

		//检测分类是否存在
		private int Check(string name,int blogid,string category)
		{
			int flag = 0;
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select CategoryID from Category where [Name] = '" + name + "' and BlogID = " + blogid + " and Type = '" + category + "'",Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if (tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["CategoryID"].ToString());
			}
			tmpReader.Close();
			Connection.Close();
			return flag;
		}

		//检测分类是否存在2
		private bool Check(CategoryVO categoryVO)
		{
			bool flag = false;
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select CateogryID from Category where [Name] = '" + categoryVO.Name + "' and BlogID = " + categoryVO.BlogID + "  and Type = '" + categoryVO.Category + "' and CategoryID <> " + categoryVO.CategoryID,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			if (!tmpReader.Read())
			{
				flag = true;
			}
			tmpReader.Close();
			Connection.Close();
			return flag;
		}

		//添加分类信息
		public int Insert(CategoryVO categoryVO)
		{
			int flag = 0;
			if(Check(categoryVO.Name,categoryVO.BlogID,categoryVO.Category)==0)
			{
				Connection.Open();
				string sql = "insert into Category(BlogID,[Name],Type,Description) values(@BlogID,@Name,@Type,@Description)";
				SqlCommand sqlComm = new SqlCommand(sql,Connection);
				sqlComm.Parameters.Add(new SqlParameter("@BlogID",SqlDbType.Int, 4));
				sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Type",SqlDbType.NVarChar, 10));
				sqlComm.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar, 500));
				sqlComm.Parameters["@BlogID"].Value=categoryVO.BlogID;
				sqlComm.Parameters["@Name"].Value=categoryVO.Name;
				sqlComm.Parameters["@Type"].Value=categoryVO.Category;
				sqlComm.Parameters["@Description"].Value=categoryVO.Description;
				flag = sqlComm.ExecuteNonQuery();
				Connection.Close();
			}
			else{
				flag = -1;//名称重复
			}
			if(flag>0)
			{
				flag = Check(categoryVO.Name,categoryVO.BlogID,categoryVO.Category);
				if(categoryVO.Category == "p")
				{
					DirectoryInfo dif = new DirectoryInfo(path+"\\Images\\Pictures\\" + categoryVO.BlogID + "\\" + flag);
					dif.Create();
				}
			}
			return flag;
		}

		//修改分类信息
		public int Update(CategoryVO categoryVO)
		{
			int flag = 0;
			if(Check(categoryVO))
			{
				Connection.Open();
				string sql = "update Category set [Name] = @Name,Description = @Description where CategoryID = @CategoryID";
				SqlCommand sqlComm = new SqlCommand(sql,Connection);
				sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
				sqlComm.Parameters.Add(new SqlParameter("@Description",SqlDbType.NVarChar, 500));
				sqlComm.Parameters.Add(new SqlParameter("@CategoryID",SqlDbType.Int, 4));
				sqlComm.Parameters["@Name"].Value=categoryVO.Name;
				sqlComm.Parameters["@Description"].Value=categoryVO.Description;
				sqlComm.Parameters["@CategoryID"].Value=categoryVO.CategoryID;
				flag = sqlComm.ExecuteNonQuery();
				Connection.Close();
			}
			else
			{
				flag = -1;//名称重复
			}
			return flag;
		}

		//删除分类信息
		public int Delete(int id)
		{
			CategoryVO cVO = Load(id);
			int flag = 0;
			Connection.Open();
			string sql = "delete from Category where CategoryID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			if(flag>0)
			{
				int count = 0;
				BlogDAO bDAO = new BlogDAO();
				FeedBackDAO fDAO = new FeedBackDAO();
				switch(cVO.Category)
				{
					case "h":
						HrefDAO hDAO = new HrefDAO();
						flag += hDAO.DeleteC(id);
						break;
					case "p":
						PictureDAO pDAO = new PictureDAO();
						flag += pDAO.DeleteC(id);
						if(flag>0)
						{
							DirectoryInfo dif = new DirectoryInfo(path+"\\Images\\Pictures\\" + cVO.BlogID + "\\" + id);
							dif.Delete(true);
						}
						break;
					case "a":
						ArticleDAO aDAO = new ArticleDAO();
						count = aDAO.LoadCount(0,id,"","");
						flag += bDAO.DeleteFeedBack(cVO.BlogID,count);
						flag += aDAO.DeleteC(id);
						count = fDAO.LoadCount(0,id,0,"a","");
						flag += bDAO.DeleteFeedBack(cVO.BlogID,count);
						flag += fDAO.DeleteC(id);
						break;
					case "e":
						EssayDAO eDAO = new EssayDAO();
						count = eDAO.LoadCount(0,id,"","");
						flag += bDAO.DeleteFeedBack(cVO.BlogID,count);
						flag += eDAO.DeleteC(id);
						count = fDAO.LoadCount(0,id,0,"e","");
						flag += bDAO.DeleteFeedBack(cVO.BlogID,count);
						flag += fDAO.DeleteC(id);
						break;
				}
			}
			return flag;
		}

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

		//选取分类列表信息
		public DataSet LoadList(int BlogID,string Category)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select * from Category where BlogID = " + BlogID + " and Type = '" + Category + "' order by CategoryID";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "Category" );
			Connection.Close();
			return Datas;
		}

		//选取分类列表信息
		public DataSet LoadList(int BlogID,string Category,int startPage,int pageSize)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select * from Category where BlogID = " + BlogID + " and Type = '" + Category + "' order by CategoryID";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas,startPage,pageSize ,"Category" );
			Connection.Close();
			return Datas;
		}

		//选取分类总数信息
		public int LoadCount(int BlogID,string Category)
		{
			int flag = 0;
			Connection.Open();
			string sql = "select Count(*) as count from Category where BlogID = " + BlogID + " and Type = '" + Category + "'";
			SqlCommand command = new SqlCommand(sql,Connection);
			SqlDataReader dr = command.ExecuteReader();
			if(dr.Read()){
				flag = Convert.ToInt32(dr["count"].ToString());
			}
			Connection.Close();
			return flag;
		}
	}
}