questions.cs

wrox c#高级编程

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


namespace Wrox.WebModules.Polls.Data
{
	public class QuestionDetails
	{
		public int QuestionID;
		public string QuestionText;
		public bool IsCurrentQuestion;
		public bool Archived;
		public DateTime AddedDate;
		public int TotalVotes;
	}

	public class Questions : Wrox.WebModules.Data.DbObject
	{

		public Questions(string newConnectionString) : base(newConnectionString)
		{	}
		
		// return all the questions
		public DataSet GetQuestions()
		{
			// call the overloaded method and specify that we want to get all the
			// questions, not only the archived once
			return GetQuestions(false);
		}

		// return all the [Archived] questions
		public DataSet GetQuestions(bool archivedOnly)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ArchivedOnly", SqlDbType.Bit, 1) };
			parameters[0].Value = archivedOnly;
			
			return RunProcedure("sp_Polls_GetQuestions", parameters, "Questions");
		}


		// return only the record with the specified ID
		public QuestionDetails GetDetails(int questionID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@QuestionID", SqlDbType.Int, 4) };
			parameters[0].Value = questionID;
			
			using(DataSet questions = RunProcedure("sp_Polls_GetQuestionDetails", parameters, "Questions"))
			{
				QuestionDetails details = new QuestionDetails();
				// if the record was found, set the properties of the class instance
				if (questions.Tables[0].Rows.Count > 0)
				{
					DataRow rowQuestion = questions.Tables[0].Rows[0];
					details.QuestionID = (int)rowQuestion["QuestionID"];
					details.QuestionText = rowQuestion["QuestionText"].ToString();
					details.IsCurrentQuestion = Convert.ToBoolean(rowQuestion["IsCurrentQuestion"]);
					details.Archived = Convert.ToBoolean(rowQuestion["Archived"]);
					details.AddedDate = Convert.ToDateTime(rowQuestion["AddedDate"]);
					details.TotalVotes = (int)rowQuestion["TotalVotes"];
				}
				else
					details.QuestionID = -1;

				return details;
			}
		}

		// return only the record with the specified ID
		public DataRow GetDetailsRow(int questionID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@QuestionID", SqlDbType.Int, 4) };
			parameters[0].Value = questionID;
			
			using(DataSet questions = RunProcedure("sp_Polls_GetQuestionDetails", parameters, "Questions"))
			{
				return questions.Tables[0].Rows[0];
			}
		}

		// return the current question's ID
		public int GetCurrentID()
		{		
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { new SqlParameter("@QuestionID", SqlDbType.Int, 4) };	
			parameters[0].Direction = ParameterDirection.Output;

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

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


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

			return (numAffected == 1);
		}


		// update the properties of the specified question
		public bool Update(int questionID, string questionText, bool isCurrentQuestion, bool archived)
		{
			int numAffected;

			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@QuestionID", SqlDbType.Int, 4),
				new SqlParameter("@QuestionText", SqlDbType.VarChar, 150),
				new SqlParameter("@IsCurrentQuestion", SqlDbType.Bit, 1),
				new SqlParameter("@Archived", SqlDbType.Bit, 1)								
			};	

			// set the values
			parameters[0].Value = questionID;
			parameters[1].Value = questionText.Trim();
			parameters[2].Value = isCurrentQuestion;
			parameters[3].Value = archived;

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

			return (numAffected == 1);
		}

	
		// add a new question
		public int Add(string questionText, bool isCurrentQuestion, bool archived)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@QuestionText", SqlDbType.VarChar, 150),
				new SqlParameter("@IsCurrentQuestion", SqlDbType.Bit, 1),
				new SqlParameter("@Archived", SqlDbType.Bit, 1),
				new SqlParameter("@QuestionID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = questionText.Trim();
			parameters[1].Value = isCurrentQuestion;
			parameters[2].Value = archived;
			parameters[3].Direction = ParameterDirection.Output;
			
			// run the procedure
			RunProcedure("sp_Polls_InsertQuestion", parameters, out numAffected);

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


		// check or uncheck the Archived status of the specified question
		public bool SetArchived(int questionID, bool archived)
		{
			int numAffected;

			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@QuestionID", SqlDbType.Int, 4),
				new SqlParameter("@Archived", SqlDbType.Bit, 1)								
			};	

			// set the values
			parameters[0].Value = questionID;
			parameters[1].Value = archived;

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

			return (numAffected == 1);
		}

	}
}