subtopicdb.cs

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

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

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

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

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

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

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

			SqlParameter parameterTopic_ID = new SqlParameter("@Topic_ID",SqlDbType.Int,4);
			parameterTopic_ID.Value = nTopic_ID;
			myCommand.Parameters.Add(parameterTopic_ID);

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

			return (int)parameterID.Value;
		}

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

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

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

			SqlParameter parameterSubTopic = new SqlParameter("@SubTopic",SqlDbType.VarChar,50);
			parameterSubTopic.Value = sSubTopic;
			myCommand.Parameters.Add(parameterSubTopic);

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