questiondb.cs

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

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

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


	/// <summary>
	/// Summary description for QuestionKindDB
	/// </summary>
	public class QuestionKindDB
	{
		private const string paramQuestionKindID                = "QuestionKindID";
		private const string paramAddQuestionKind               = "QuestionKindID_KindName_KindOrder";
		private const string paramUpdateQuestionKind            = "QuestionKindID_KindName";
		private const string paramUpdateQuestionKindOrder       = "QuestionKindID_KindOder";

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

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

			//添加储存过程的参数

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

			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 void UpdateQuesKindName(int nKindID,String sKindName)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateQuestionKind",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateQuestionKind);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@KindID",SqlDbType.Int),											  
												   new SqlParameter("@KindName",SqlDbType.VarChar)};
				SQLHelper.CacheParameters(paramUpdateQuestionKind,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nKindID;
			paramCache[1].Value = sKindName;
			

			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 int AddQuesKind(String sKindName,int nKindOrder)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddQuestionKind",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramAddQuestionKind);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{												  
												   new SqlParameter("@KindName",SqlDbType.VarChar),
												   new SqlParameter("@KindOrder",SqlDbType.Int,4),
												   new SqlParameter("@KindID",SqlDbType.Int)};
				SQLHelper.CacheParameters(paramAddQuestionKind,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = sKindName;
			paramCache[1].Value = nKindOrder;
			paramCache[2].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[2].Value;
		}

		public void UpdateQuesKindOrder(int nKindID,String sMoveFlag)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLHelper.DBCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateKindOrder",myConnection);

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

			//创建访问数据库的参数
			SqlParameter[] paramCache = SQLHelper.GetCachedParameters(paramUpdateQuestionKindOrder);
			if(paramCache == null)
			{
				paramCache = new SqlParameter[]{
												   new SqlParameter("@KindID",SqlDbType.Int),											  
												   new SqlParameter("@MoveFlag",SqlDbType.VarChar)};
				SQLHelper.CacheParameters(paramUpdateQuestionKindOrder,paramCache);
			}
			SQLHelper.AddMyCommandParams(myCommand,paramCache);
			paramCache[0].Value = nKindID;
			paramCache[1].Value = sMoveFlag;
			

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

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