topics.cs

wrox c#高级编程

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


namespace Wrox.WebModules.Forums.Data
{
	public class TopicDetails
	{
		public int CategoryID;
		public int ForumID;
		public int TopicID;
		public string TopicKey;
		public string Subject;
		public string Message;
		public DateTime AddedDate;
		public int Replies;
		public DateTime LastReplyDate;
		public DateTime LastPostDate;
		public int MemberID;
		public string MemberIP;
		public string MemberName;
		public string Email;
		public bool ShowEmail;
		public string Signature;
		public string AvatarUrl;
		public string Homepage;
	}

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

		// return all the topics for the specified forum
		public DataSet GetTopics(int forumID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ForumID", SqlDbType.Int, 4) };
			parameters[0].Value = forumID;

			return RunProcedure("sp_Forums_GetTopics", parameters, "Topics");
		}

		// return the topics for the specified forum and in the specified virtual page
		// there is also an output param to return the total number of topics for the forum
		public DataSet GetTopics(int forumID, int pageNumber, int pageSize)
		{
			// create the parameter
			SqlParameter[] parameters = {
				new SqlParameter("@ForumID", SqlDbType.Int, 4),
				new SqlParameter("@PageNumber", SqlDbType.Int, 4),
				new SqlParameter("@PageSize", SqlDbType.Int, 4)
			};
			parameters[0].Value = forumID;
			parameters[1].Value = pageNumber;
			parameters[2].Value = pageSize;

			return RunProcedure("sp_Forums_GetTopicsByPage", parameters, "Topics");
		}	

		// return only the record with the specified ID
		public TopicDetails GetDetails(int topicID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@TopicID", SqlDbType.Int, 4) };
			parameters[0].Value = topicID;
			
			using(DataSet topics = RunProcedure("sp_Forums_GetTopicDetails", parameters, "Topics"))
			{
				TopicDetails details = new TopicDetails();
				// if the record was found, set the properties of the class instance
				if (topics.Tables[0].Rows.Count > 0)
				{
					DataRow rowTopic = topics.Tables[0].Rows[0];
					details.CategoryID = (int)rowTopic["CategoryID"];
					details.ForumID = (int)rowTopic["ForumID"];
					details.TopicID = (int)rowTopic["TopicID"];
					details.TopicKey = rowTopic["TopicKey"].ToString();
					details.Subject = rowTopic["Subject"].ToString();
					details.Message = rowTopic["Message"].ToString();
					details.AddedDate = Convert.ToDateTime(rowTopic["AddedDate"]);
					details.Replies = (int)rowTopic["TopicReplies"];
					details.LastReplyDate = (rowTopic["TopicLastReplyDate"]==DBNull.Value ? 
						new DateTime() : Convert.ToDateTime(rowTopic["TopicLastReplyDate"]));
					details.LastPostDate = (rowTopic["TopicLastPostDate"]==DBNull.Value ? 
						new DateTime() : Convert.ToDateTime(rowTopic["TopicLastPostDate"]));
					details.MemberID = (int)rowTopic["MemberID"];
					details.MemberIP = rowTopic["MemberIP"].ToString();
					details.MemberName = rowTopic["MemberName"].ToString();
					details.Email = rowTopic["Email"].ToString();
					details.ShowEmail = Convert.ToBoolean(rowTopic["ShowEmail"]);
					details.Signature = rowTopic["Signature"].ToString();
					details.AvatarUrl = rowTopic["AvatarUrl"].ToString();
					details.Homepage = rowTopic["Homepage"].ToString();
				}
				else
					details.TopicID = -1;

				return details;
			}
		}

		// return only the record with the specified ID in row format
		public DataRow GetDetailsRow(int topicID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@TopicID", SqlDbType.Int, 4) };
			parameters[0].Value = topicID;
			
			using(DataSet topics = RunProcedure("sp_Forums_GetTopicDetails", parameters, "Topics"))
			{
				// return the first row, which is the only one
				return topics.Tables[0].Rows[0];
			}
		}


		// retrieve the ID of the topic with the specified key
		public int GetTopicID(string topicKey)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@TopicKey", SqlDbType.VarChar, 10),
				new SqlParameter("@TopicID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = topicKey;
			parameters[1].Direction = ParameterDirection.Output;

			// run the procedure
			RunProcedure("sp_Forums_GetTopicID", parameters, out numAffected);

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


		// add a new record
		public int Add(int forumID, string topicKey, string subject, string message, int memberID, string memberIP)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@ForumID", SqlDbType.Int, 4),
				new SqlParameter("@TopicKey", SqlDbType.VarChar, 15),
				new SqlParameter("@Subject", SqlDbType.VarChar, 100),
				new SqlParameter("@Message", SqlDbType.Text),
				new SqlParameter("@MemberID", SqlDbType.Int, 4),
				new SqlParameter("@MemberIP", SqlDbType.VarChar, 15),
				new SqlParameter("@TopicID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = forumID;
			parameters[1].Value = topicKey.Trim();
			parameters[2].Value = subject.Trim();
			parameters[3].Value = message.Trim();
			parameters[4].Value = memberID;
			parameters[5].Value = memberIP.Trim();
			parameters[6].Direction = ParameterDirection.Output;

			// run the procedure
			RunProcedure("sp_Forums_InsertTopic", parameters, out numAffected);

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


		// update all the fields of the specified category
		public bool Update(int topicID, string topicKey, string subject, string message)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@TopicID", SqlDbType.Int, 4),
				new SqlParameter("@TopicKey", SqlDbType.VarChar, 15),
				new SqlParameter("@Subject", SqlDbType.VarChar, 100),
				new SqlParameter("@Message", SqlDbType.Text),
			};
			
			// set the values
			parameters[0].Value = topicID;
			parameters[1].Value = topicKey.Trim();
			parameters[2].Value = subject.Trim();
			parameters[3].Value = message.Trim();

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

			return (numAffected == 1);
		}


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

			return (numAffected == 1);
		}

	}
}