answerdb.cs

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

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

namespace ExamineSystem
{
	/// <summary>
	/// Summary description for AnswerDB.
	/// </summary>
	public class AnswerDB
	{
		public static readonly string  paramQuestionID     = "QuestionID";
		public static readonly string  paramAnswerID       = "AnswerID";
		private const string paramAddAnswer                = "AnswerID_Title";
		private const string paramUpdateAnswer             = "AnswerID_Title_Body";

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

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

			//添加储存过程的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(QuestionDB.paramQuestionID);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@QuestionID",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(QuestionDB.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 GetSingleAnswer(int nAnswerID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleAnswer",myConnection);

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

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

			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 AddAnswer(String sTitle,String sBody,int nQuestionID,int nPictureID,int nRorW)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddAnswer",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddAnswer);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{												  
												   new SqlParameter("@Title",SqlDbType.VarChar),
												   new SqlParameter("@Body",SqlDbType.VarChar),
												   new SqlParameter("@QuestionID",SqlDbType.Int,4),
												   new SqlParameter("@PictureID",SqlDbType.Int),
												   new SqlParameter("@AnswerID",SqlDbType.Int,4),
												   new SqlParameter("@RorW",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramAddAnswer,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = sTitle;
			paramCache[1].Value = sBody;
			paramCache[2].Value = nQuestionID;
			paramCache[3].Value = nPictureID;
			paramCache[4].Value = nRorW;
			paramCache[5].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[5].Value;
		}

		public void UpdateAnswer(int nAnswerID,String sTitle,String sBody,int nRorW)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateAnswer",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateAnswer);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{	
												   new SqlParameter("@AnswerID",SqlDbType.Int,4),
												   new SqlParameter("@Title",SqlDbType.VarChar),
												   new SqlParameter("@Body",SqlDbType.VarChar),
												   new SqlParameter("@RorW",SqlDbType.Int,4)};
				SQLHelper.CacheParameters(paramUpdateAnswer,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nAnswerID;
			paramCache[1].Value = sTitle;
			paramCache[2].Value = sBody;
			paramCache[3].Value = nRorW;

			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();
				}
			}		
		}

		public void DeleteAnswer(int nAnswerID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteAnswer",myConnection);

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

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

			try
			{
				//打开数据库的连接
				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();
				}
			}		
		}

		public void DeleteAnswerByQuestion(int nQuestionID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteAnswerByQuestion",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;

			try
			{
				//打开数据库的连接
				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();
				}
			}		
		}
	}
}