questiondb.cs

这是一个管理系统的源码

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

namespace Service
{
	/// <summary>
	/// Summary description for QuestionDB.
	/// </summary>	
	public class QuestionDetails
	{
		public int      QuestionID;
		public String   Title;
		public int      SoluteState;
		public int      OwnerID;
		public DateTime PubTime;
		public String   SoluteTime;
		public String   DisposeTime;
		public int      IsTypeError;
		public String   OwnerName;
	}

	public class QuestionDB
	{
		public  SqlDataReader GetQuestions()		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetQuestions",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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public SqlDataReader GetQuestionComment(int nQuestionID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleQuestion",myConnection);

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

			//创建访问数据库的参数			
			SqlParameter parameterQuestionID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterQuestionID.Value = nQuestionID;
			myCommand.Parameters.Add(parameterQuestionID);

			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public  SqlDataReader GetQuestionByDirection(int nDirectionID,int nState)		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetQuestionByDirection",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterDirectionID = new SqlParameter("@DirectionID",SqlDbType.Int,4);
			parameterDirectionID.Value = nDirectionID;
			myCommand.Parameters.Add(parameterDirectionID);

			SqlParameter parameterState = new SqlParameter("@SoluteState",SqlDbType.Int,4);
			parameterState.Value = nState;
			myCommand.Parameters.Add(parameterState);
			
			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

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

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nQuestionID;
			myCommand.Parameters.Add(parameterID);

			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public int AddQuestion(String sTitle,String sQuestion,int nPubID,int nDirectionID,int nChannelID,
			int nTopicID,int nSubTopicID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddQuestion",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterTitle = new SqlParameter("@Title",SqlDbType.VarChar,50);
			parameterTitle.Value = sTitle;
			myCommand.Parameters.Add(parameterTitle);	

			SqlParameter parameterQuestion = new SqlParameter("@Question",SqlDbType.Text);
			parameterQuestion.Value = sQuestion;
			myCommand.Parameters.Add(parameterQuestion);			

			SqlParameter parameterPubID = new SqlParameter("@PubID",SqlDbType.Int,4);
			parameterPubID.Value = nPubID;
			myCommand.Parameters.Add(parameterPubID);

			if(nDirectionID > 0)
			{
				SqlParameter parameterDirectionID = new SqlParameter("@DirectionID",SqlDbType.Int,4);
				parameterDirectionID.Value = nDirectionID;
				myCommand.Parameters.Add(parameterDirectionID);
			}

			if(nChannelID > 0 )
			{
				SqlParameter parameterChannelID = new SqlParameter("@ChannelID",SqlDbType.Int,4);
				parameterChannelID.Value = nChannelID;
				myCommand.Parameters.Add(parameterChannelID);
			}

			if(nTopicID > 0)
			{
				SqlParameter parameterTopicID = new SqlParameter("@TopicID",SqlDbType.Int,4);
				parameterTopicID.Value = nTopicID;
				myCommand.Parameters.Add(parameterTopicID);
			}

			if(nSubTopicID > 0)
			{
				SqlParameter parameterSubTopicID = new SqlParameter("@SubTopicID",SqlDbType.Int,4);
				parameterSubTopicID.Value = nSubTopicID;
				myCommand.Parameters.Add(parameterSubTopicID);
			}

			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Direction = ParameterDirection.ReturnValue;
			myCommand.Parameters.Add(parameterID);

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

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

			return (int)parameterID.Value;
		}

		public void UpdateQuestion(int nQuestionID,String sTitle,String sQuestion)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateQuestion",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nQuestionID;
			myCommand.Parameters.Add(parameterID);
			
			SqlParameter parameterTitle = new SqlParameter("@Title",SqlDbType.VarChar,50);
			parameterTitle.Value = sTitle;
			myCommand.Parameters.Add(parameterTitle);	

			SqlParameter parameterQuestion = new SqlParameter("@Question",SqlDbType.Text);
			parameterQuestion.Value = sQuestion;
			myCommand.Parameters.Add(parameterQuestion);

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

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

		public void UpdateQuestionState(int nQuestionID,int nState)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateQuesState",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@QuestionID",SqlDbType.Int,4);
			parameterID.Value = nQuestionID;
			myCommand.Parameters.Add(parameterID);	

			SqlParameter parameterState = new SqlParameter("@SoluteState",SqlDbType.Int,4);
			parameterState.Value = nState;
			myCommand.Parameters.Add(parameterState);

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

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

		public void DeleteQuestion(int nID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteQuestion",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = nID;
			myCommand.Parameters.Add(parameterID);


			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 int[] GetAllID(int nQuestionID)
		{
			int[] arrayID = new int[4];

			QuestionDB question = new QuestionDB();
			SqlDataReader recq = question.GetSingleQuestion(nQuestionID);

			while(recq.Read())
			{
				if(recq["DirectionID"].ToString() != "")
				{
					arrayID[0] = Int32.Parse(recq["DirectionID"].ToString());
					arrayID[1] = arrayID[2] = arrayID[3] = 0;
				}

				if(recq["ChannelID"].ToString() != "")
				{
					arrayID[1] = Int32.Parse(recq["ChannelID"].ToString());

					ChannelDB channel = new ChannelDB();
					SqlDataReader recc = channel.GetSingleChannel(arrayID[1]);

					while(recc.Read())
					{
						arrayID[0] = Int32.Parse(recc["DirectionID"].ToString());
					}
					recc.Close();

					arrayID[2] = arrayID[3] = 0;
				}

				if(recq["TopicID"].ToString() != "")
				{
					arrayID[2] = Int32.Parse(recq["TopicID"].ToString());

					TopicDB topic = new TopicDB();
					SqlDataReader rect = topic.GetSingleTopic(arrayID[2]);

					while(rect.Read())
					{
						arrayID[1] = Int32.Parse(rect["ChannelID"].ToString());
					}
					rect.Close();

					ChannelDB channel = new ChannelDB();
					SqlDataReader recc = channel.GetSingleChannel(arrayID[1]);

					while(recc.Read())
					{
						arrayID[0] = Int32.Parse(recc["DirectionID"].ToString());
					}
					recc.Close();

					arrayID[3] = 0;
				}

				if(recq["SubTopicID"].ToString() != "")
				{
					arrayID[3] = Int32.Parse(recq["SubTopicID"].ToString());

					SubTopicDB subTopic = new SubTopicDB();
					SqlDataReader recs = subTopic.GetSingleSubtopic(arrayID[3]);

					while(recs.Read())
					{
						arrayID[2] = Int32.Parse(recs["TopicID"].ToString());
					}
					recs.Close();

                    TopicDB topic = new TopicDB();
					SqlDataReader rect = topic.GetSingleTopic(arrayID[2]);

					while(rect.Read())
					{
						arrayID[1] = Int32.Parse(rect["ChannelID"].ToString());
					}
					rect.Close();

					ChannelDB channel = new ChannelDB();
					SqlDataReader recc = channel.GetSingleChannel(arrayID[1]);

					while(recc.Read())
					{
						arrayID[0] = Int32.Parse(recc["DirectionID"].ToString());
					}
					recc.Close();
				}
			}
			recq.Close();

			return(arrayID);
		}

		public SqlDataReader GetQuestionByUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetQuestionByUser",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)