hrefdao.cs

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

using System;
using System.Data;
using System.Data.SqlClient;

namespace WesternByte.MyBlog.Core.Href
{
	/// <summary>
	/// HrefDAO 的摘要说明。
	/// </summary>
	public class HrefDAO : DbObject
	{
		//选取某个链接信息
		public HrefVO Load(int id)
		{
			Connection.Open();
			SqlCommand sqlComm = new SqlCommand("select * from Href where HrefID = " + id,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			HrefVO hrefVO = new HrefVO();
			if(tmpReader.Read())
			{
				hrefVO.HrefID = Convert.ToInt32(tmpReader["HrefID"].ToString());
				hrefVO.BlogID = Convert.ToInt32(tmpReader["BlogID"].ToString());
				hrefVO.CategoryID = Convert.ToInt32(tmpReader["CategoryID"].ToString());
				hrefVO.Name = tmpReader["Name"].ToString();
				hrefVO.Url = tmpReader["Url"].ToString();
			}
			tmpReader.Close();
			Connection.Close();
			return hrefVO;
		}

		//添加链接
		public int Insert(HrefVO hrefVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "insert into Href(Name,Url,BlogID,CategoryID) values(@Name,@Url,@BlogID,@CategoryID)";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
			sqlComm.Parameters.Add(new SqlParameter("@Url",SqlDbType.NVarChar, 200));
			sqlComm.Parameters.Add(new SqlParameter("@BlogID",SqlDbType.Int, 4));
			sqlComm.Parameters.Add(new SqlParameter("@CategoryID",SqlDbType.Int, 4));
			sqlComm.Parameters["@Name"].Value=hrefVO.Name;
			sqlComm.Parameters["@Url"].Value=hrefVO.Url;
			sqlComm.Parameters["@BlogID"].Value=hrefVO.BlogID;
			sqlComm.Parameters["@CategoryID"].Value=hrefVO.CategoryID;
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//修改链接信息
		public int Update(HrefVO hrefVO)
		{
			int flag = 0;
			Connection.Open();
			string sql = "update Href set Name = @Name,Url = @Url where HrefID = @HrefID";
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			sqlComm.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar, 50));
			sqlComm.Parameters.Add(new SqlParameter("@Url",SqlDbType.NVarChar, 200));
			sqlComm.Parameters.Add(new SqlParameter("@HrefID",SqlDbType.Int, 4));
			sqlComm.Parameters["@Name"].Value=hrefVO.Name;
			sqlComm.Parameters["@Url"].Value=hrefVO.Url;
			sqlComm.Parameters["@HrefID"].Value=hrefVO.HrefID;
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除链接信息
		public int Delete(int id)
		{
			int flag = 0;
			Connection.Open();
			string sql = "delete from Href where HrefID = " + id;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//删除某个分类下的链接信息
		public int DeleteC(int CategoryID)
		{
			int flag = 0;
			Connection.Open();
			string sql = "delete from Href 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 Href where BlogID = " + BlogID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			flag = sqlComm.ExecuteNonQuery();
			Connection.Close();
			return flag;
		}

		//选取链接列表信息
		public DataSet LoadList(int BlogID,int CategoryID)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql = "select * from Href where 1=1";
			if(BlogID!=0)sql+= " and BlogID = " + BlogID;
			if(CategoryID!=0)sql+= " and CategoryID = " + CategoryID;
			sql += " order by HrefID desc";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( Datas, "Href" );
			Connection.Close();
			return Datas;
		}

		//选取链接列表信息
		public DataView LoadList(int BlogID)
		{
			DataSet Datas=new DataSet();
			Connection.Open();
			string sql1 = "select CategoryID,Name as Cname from Category where BlogID = " + BlogID + " and Type = 'h' order by CategoryID";
			string sql = "select * from Href where BlogID = " + BlogID + " and CategoryID in (select CategoryID from Category where BlogID = " + BlogID + " and Type = 'h') order by HrefID";
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql1,Connection);
			sqlDA.Fill( Datas, "Category");
			DataView dv=new DataView(Datas.Tables["Category"]);

			SqlDataAdapter sqlDB = new SqlDataAdapter(sql,Connection);
			sqlDB.Fill( Datas, "Href" );

			Datas.Relations.Add("hrefRelation",Datas.Tables["Category"].Columns["CategoryID"],Datas.Tables["Href"].Columns["CategoryID"]);

			Connection.Close();
			return dv;
		}

		//选取链接列表信息
		public DataSet LoadList(int BlogID,int CategoryID,int startPage,int pageSize)
		{
			DataSet ds=new DataSet();
			Connection.Open();
			string sql = "select * from Href where BlogID = " + BlogID;
			if(CategoryID!=0) sql += " and CategoryID = " + CategoryID;
			SqlDataAdapter sqlDA = new SqlDataAdapter(sql,Connection);
			sqlDA.Fill( ds,startPage,pageSize, "Href" );
			Connection.Close();
			return ds;
		}

		//选取链接总数信息
		public int LoadCount(int BlogID,int CategoryID)
		{
			Connection.Open();
			string sql = "select count(*) as count from Href where BlogID = " + BlogID;
			if(CategoryID!=0) sql += " and CategoryID = " + CategoryID;
			SqlCommand sqlComm = new SqlCommand(sql,Connection);
			SqlDataReader tmpReader = sqlComm.ExecuteReader();
			int flag = 0;
			if(tmpReader.Read())
			{
				flag = Convert.ToInt32(tmpReader["Count"].ToString());
			}
			Connection.Close();
			return flag;
		}
	}
}