news.cs

wrox c#高级编程

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

namespace Wrox.WebModules.NewsManager.Data
{
	public class NewsDetails
	{
		public int NewsID;
		public int CategoryID;
		public string Title;
		public string Body;
		public DateTime ReleaseDate;
		public DateTime ExpireDate;
		public DateTime AddedDate;
		public bool Approved;
		public int UserID;
		public string UserName;
		public string UserEmail;
	}

	public class News : Wrox.WebModules.Data.DbObject
	{
		public News(string newConnectionString) : base(newConnectionString)
		{	}

		// return all the News of the specified category
		public DataSet GetNews(int categoryID, bool currentApprovedOnly, int abstractLength)
		{
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
				new SqlParameter("@CurrentApprovedOnly", SqlDbType.Bit, 1),
				new SqlParameter("@AbstractLength", SqlDbType.Int, 4)
			};
			
			// set the values
			parameters[0].Value = categoryID;
			parameters[1].Value = currentApprovedOnly;
			parameters[2].Value = abstractLength;
			
			return RunProcedure("sp_News_GetNews", parameters, "News");
		}

		// return the headlines for the current and approved News
		public DataSet GetHeadlines(int categoryID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@CategoryID", SqlDbType.Int, 4) };
			parameters[0].Value = categoryID;
			
			return RunProcedure("sp_News_GetHeadlines", parameters, "Headlines");
		}


		// return only the record with the specified ID
		public NewsDetails GetDetails(int newsID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsID", SqlDbType.Int, 4) };
			parameters[0].Value = newsID;
			
			using(DataSet news = RunProcedure("sp_News_GetNewsDetails", parameters, "News"))
			{
				NewsDetails details = new NewsDetails();
				// if the record was found, set the properties of the class instance
				if (news.Tables[0].Rows.Count > 0)
				{
					DataRow rowNews = news.Tables[0].Rows[0];
					details.NewsID = (int)rowNews["NewsID"];
					details.CategoryID = (int)rowNews["CategoryID"];
					details.Title = rowNews["Title"].ToString();
					details.Body = rowNews["Body"].ToString();
					details.ReleaseDate = Convert.ToDateTime(rowNews["ReleaseDate"]);
					details.ExpireDate = Convert.ToDateTime(rowNews["ExpireDate"]);
					details.AddedDate = Convert.ToDateTime(rowNews["AddedDate"]);
					details.Approved = Convert.ToBoolean(rowNews["Approved"]);
					details.UserID = (rowNews["UserID"]==DBNull.Value ? -1 : (int)rowNews["UserID"]);
					details.UserName = rowNews["UserName"].ToString();
					details.UserEmail = rowNews["UserEmail"].ToString();
				}
				else
					details.NewsID = -1;

				return details;
			}
		}

		// return only the record with the specified ID
		public DataRow GetDetailsRow(int newsID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsID", SqlDbType.Int, 4) };
			parameters[0].Value = newsID;
			
			using(DataSet news = RunProcedure("sp_News_GetNewsDetails", parameters, "News"))
			{
				return news.Tables[0].Rows[0];
			}
		}


		// delete the record identified by the specified ID
		public bool Delete(int newsID)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsID", SqlDbType.Int, 4) };
			parameters[0].Value = newsID;
			
			RunProcedure("sp_News_DeleteNews", parameters, out numAffected);

			return (numAffected == 1);
		}


		// update the news identified by the specified ID
		public bool Update(int newsID, string title, string body, 
			DateTime releaseDate, DateTime expireDate, bool approved)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@NewsID", SqlDbType.Int, 4),
				new SqlParameter("@Title", SqlDbType.VarChar, 50),
				new SqlParameter("@Body", SqlDbType.Text),
				new SqlParameter("@ReleaseDate", SqlDbType.DateTime),
				new SqlParameter("@ExpireDate", SqlDbType.DateTime),
				new SqlParameter("@Approved", SqlDbType.Bit, 1)
			};
			
			// set the values
			parameters[0].Value = newsID;
			parameters[1].Value = title.Trim();
			parameters[2].Value = body.Trim();
			parameters[3].Value = releaseDate;
			parameters[4].Value = expireDate;
			parameters[5].Value = approved;

			RunProcedure("sp_News_UpdateNews", parameters, out numAffected);

			return (numAffected == 1);
		}

		
		// add a news
		public int Add(int categoryID, string title, string body, 
			DateTime releaseDate, DateTime expireDate, bool approved, int userID)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
				new SqlParameter("@Title", SqlDbType.VarChar, 50),
				new SqlParameter("@Body", SqlDbType.Text),
				new SqlParameter("@ReleaseDate", SqlDbType.DateTime),
				new SqlParameter("@ExpireDate", SqlDbType.DateTime),
				new SqlParameter("@Approved", SqlDbType.Bit, 1),
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@NewsID", SqlDbType.Int, 4)
			};
			
			// set the values
			parameters[0].Value = categoryID;
			parameters[1].Value = title.Trim();
			parameters[2].Value = body.Trim();
			parameters[3].Value = releaseDate;
			parameters[4].Value = expireDate;
			parameters[5].Value = approved;
			parameters[6].Value = userID;
			parameters[7].Direction = ParameterDirection.Output;

			RunProcedure("sp_News_InsertNews", parameters, out numAffected);

			return (int)parameters[7].Value;
		}

		
		// set the Approved state of a news
		public bool SetApproved(int newsID, bool approved)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@NewsID", SqlDbType.Int, 4),
				new SqlParameter("@Approved", SqlDbType.Bit, 1)
			};

			// set the values
			parameters[0].Value = newsID;
			parameters[1].Value = approved;

			RunProcedure("sp_News_SetNewsApproved", parameters, out numAffected);

			return (numAffected == 1);
		}

	}
}