questiondb.cs

用ASP.NET（C#）、SQL Server2000数据库开发在线考试系统源代码

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

namespace ExamineSystem
{
	/// <summary>
	/// QuestionKinds类主要是用来存储问题种类以及该种类试题的数量
	/// </summary>
	public class QuestionKinds
	{
		public int   KindID;
		public int   KindNum;
	}

	/// <summary>
	/// 试卷的包括题型的信息
	/// </summary>
	public class PaperKinds
	{
		private int    _kindID   = 0;
		private string _kindName = "";
		private int    _mark     = 0;
		private int    _quantity = 0;

		public int KindID
		{
			set
			{
				_kindID = value;
			}
			get
			{
				return(_kindID);
			}
		}
		public string KindName
		{
			set
			{
				_kindName = value;
			}
			get
			{
				return(_kindName);
			}
		}
		public int Mark
		{
			set
			{
				_mark = value;
			}
			get
			{
				return(_mark);
			}
		}
		public int Quantity
		{
			set
			{
				_quantity = value;
			}
			get
			{
				return(_quantity);
			}
		}
	}

	/// <summary>
	/// QuestionDetails类主要存储试题的信息，它主要实现前台的试题数据绑定功能。
	/// </summary>
	public class QuestionDetails
	{
		private int _questionID = 0;
		public int    QuestionID
		{
			set 
			{
				_questionID = value;
			}
			get
			{
				return(_questionID);
			}
		}
		public String Title;
		public String Body;
		public int    Mark;
		public int    Index;
		public int    Defficult;
	}

	/// <summary>
	/// Summary description for QuestionDB.
	/// </summary>
	public class QuestionDB
	{
		public static readonly string  paramQuestionID = "QuestionID";
		public static readonly string  paramQuestionKindID = "KindID";
		private const string paramAddQuestion              = "QuestionID_Title";
		private const string paramUpdateQuestion           = "QuestionID_Title_Body";

		public DataSet GetAllQuestionID()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlDataAdapter da = new SqlDataAdapter("Pr_GetAllQuestionID",myConnection);

			//定义访问数据库的方式为存储过程
			da.SelectCommand.CommandType = CommandType.StoredProcedure;			

			DataSet ds = new DataSet();

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				da.Fill(ds);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}

			//返回 ds
			return ds;
		}

		public SqlDataReader GetSingleQuestion(int nQuestionID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleQuestion",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramQuestionID);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@QuestionID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramQuestionID,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nQuestionID;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public SqlDataReader GetQuestions(int nQuestionKindID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetQuestions",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramQuestionKindID);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@KindID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramQuestionKindID,paramCache);
			}	
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nQuestionKindID;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public ArrayList GetQuestions(ArrayList QuestionList)
		{
			QuestionDB question = new QuestionDB();

			///设置问题容器的大小
			ArrayList NewQuestionList = new ArrayList();			
			NewQuestionList.Capacity = QuestionList.Capacity;
			for(int i = 0; i < QuestionList.Count; i++)
			{
				SqlDataReader recq = question.GetSingleQuestion(((QuestionDetails)QuestionList[i]).QuestionID);
				if(recq.Read())
				{
					QuestionDetails questionDetail = new QuestionDetails();
					questionDetail.Title      = recq["Title"].ToString();
					questionDetail.Body       = recq["Body"].ToString().Replace("\n","<br>");
					questionDetail.QuestionID = Int32.Parse(recq["QuestionID"].ToString());
					questionDetail.Mark       = Int32.Parse(recq["Mark"].ToString());
					questionDetail.Index      = i + 1;
					NewQuestionList.Add(questionDetail);
				}
				recq.Close();
			}

			///返回问题的详细信息
			return(NewQuestionList);			
		}

		public ArrayList GetQuestions(ArrayList QuestionList,int nKindID)
		{
			QuestionDB question = new QuestionDB();

			///设置问题容器的大小
			ArrayList NewQuestionList = new ArrayList();			
			NewQuestionList.Capacity = QuestionList.Capacity;
			for(int i = 0; i < QuestionList.Count; i++)
			{
				SqlDataReader recq = question.GetSingleQuestion(((QuestionDetails)QuestionList[i]).QuestionID);
				if(recq.Read())
				{
					if(nKindID.ToString() == recq["KindID"].ToString())
					{
						QuestionDetails questionDetail = new QuestionDetails();
						questionDetail.Title      = recq["Title"].ToString();
						questionDetail.Body       = recq["Body"].ToString().Replace("\n","<br>");
						questionDetail.QuestionID = Int32.Parse(recq["QuestionID"].ToString());
						questionDetail.Mark       = Int32.Parse(recq["Mark"].ToString());
						questionDetail.Index      = i + 1;
						NewQuestionList.Add(questionDetail);
					}
				}
				recq.Close();
			}

			///返回问题的详细信息
			return(NewQuestionList);			
		}

		public SqlDataReader GetQuestionKinds()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetQuestionKinds",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
//			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramQuestionID);
//			if(paramCache == null)
//			{
//				paramCache = new SqlParameter[]{
//												   new SqlParameter("@QuestionID",SqlDbType.Int,4)};
//				SQLHelper.CacheParameters(paramQuestionID,paramCache);
//			}	
//			SQLHelper.AddMyCommandParams(myCommand,paramCache);
//			paramCache[0].Value = nQuestionID;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public int AddQuestion(String sTitle,String sBody,int nDefficult,int nMark,
			int nQuestionKindID,int nPictureID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddQuestion",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddQuestion);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{												  
												   new SqlParameter("@Title",SqlDbType.VarChar),
												   new SqlParameter("@Body",SqlDbType.VarChar),
												   new SqlParameter("@Defficult",SqlDbType.Int,4),
												   new SqlParameter("@Mark",SqlDbType.Int,4),
												   new SqlParameter("@KindID",SqlDbType.Int,4),
												   new SqlParameter("@PictureID",SqlDbType.Int),
												   new SqlParameter("@QuestionID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramAddQuestion,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = sTitle;
			paramCache[1].Value = sBody;
			paramCache[2].Value = nDefficult;
			paramCache[3].Value = nMark;
			paramCache[4].Value = nQuestionKindID;
			paramCache[5].Value = nPictureID;
			paramCache[6].Direction = ParameterDirection.ReturnValue;

			try
			{
				//打开数据库的连接
				if(myConnection.State == ConnectionState.Closed)
				{
					myConnection.Open();
				}
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}

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

		public void UpdateQuestion(int nQuestionID,String sTitle,String sBody,int nDefficult,int nMark,
			int nQuestionKindID,int nPictureID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateQuestion",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;