topicdb.cs

计算机学院网站及管理系统

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

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for TopicDB.
	/// </summary>
	public class TopicDB
	{		
		public SqlDataReader GetTopics(int ChannelID)		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetTopics",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;
			
			//设置存储过程的参数
			SqlParameter parameterID=new SqlParameter("@ChannelID",SqlDbType.Int,4);
			parameterID.Value=ChannelID;
			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 SqlDataReader GetSingleTopic(int TopicID)		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleTopic",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;
			
			//设置存储过程的参数
			SqlParameter parameterID = new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value = TopicID;
			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 SqlDataReader GetChannelTopicName(int nChannelID,int nTopicID)		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetChannelTopicName",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;
			
			//设置存储过程的参数
			SqlParameter parameterChannelID= new SqlParameter("@ChannelID",SqlDbType.Int,4);
			parameterChannelID.Value = nChannelID;
			myCommand.Parameters.Add(parameterChannelID);

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

			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 void AddTopic(String sTopic,int nChannelID,int nOrderby)
		{

			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddTopic",myConnection);

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

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

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


			SqlParameter parameterOrderby = new SqlParameter("@Orderby",SqlDbType.Int,4);
			parameterOrderby.Value = nOrderby;
			myCommand.Parameters.Add(parameterOrderby);

			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();
				}
			}
		}
        
		public void UpdateTopicOrder( int nID, int nOrderby)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateTopicOrder",myConnection);

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

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

			SqlParameter parameterOrderby = new SqlParameter("@Orderby",SqlDbType.Int,4);
			parameterOrderby.Value = nOrderby;
			myCommand.Parameters.Add(parameterOrderby);

			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 UpdateTopic(int nID,String sTopic,int nChannelID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateTopic",myConnection);

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

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

			SqlParameter parameterTopic = new SqlParameter("@Topic",SqlDbType.VarChar,50);
			parameterTopic.Value = sTopic;
			myCommand.Parameters.Add(parameterTopic);

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

			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 DeleteTopic(int nID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteTopic",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("10002",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

//		public bool HasContent(int nTopicID)
//		{
//			ContentDB content = new ContentDB();
//			SqlDataReader dr;
//
//			dr = content.GetContentByTopic(nTopicID);
//			if (dr.Read())
//			{
//				dr.Close();
//				return true;
//			}
//			else
//			{
//				dr.Close();
//				return false;
//			}
//		}
	}
}