contentdb.cs

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

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

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for ContentDB.
	/// </summary>
	public class ContentDB
	{
		public  SqlDataReader GetContents()		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetContents",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 GetContentByColumn(int nColumnID)		
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetContentByColumn",myConnection);

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

			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 GetContentByTab(int nTabID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetContentByTab",myConnection);
		
			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Value = nTabID;
			myCommand.Parameters.Add(parameterTabID);

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

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

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

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

			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 DataSet GetSearchResult(String sContent)		
		{
			DataSet ds = new DataSet();

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

			//定义访问数据库的方式为存储过程
			da.SelectCommand.CommandType = CommandType.StoredProcedure;
			
			//设置存储过程的参数
			SqlParameter parameterContent = new SqlParameter("@Content",SqlDbType.VarChar,100);
			parameterContent.Value = sContent;
			da.SelectCommand.Parameters.Add(parameterContent);

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

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

			//返回 ds
			return ds;
		}

		public int AddContent(String sTitle,String sBody,int nUserID,int nTabID,int nModuleID,int nColumnID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddContent",myConnection);

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

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

			SqlParameter parameterBody = new SqlParameter("@Body",SqlDbType.Text);
			parameterBody.Value = sBody;
			myCommand.Parameters.Add(parameterBody);			

			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			if(nTabID !=0)
			{
				SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
				parameterTabID.Value = nTabID;			
				myCommand.Parameters.Add(parameterTabID);
			}

			if(nModuleID !=0)
			{
				SqlParameter parameterModuleID = new SqlParameter("@ModuleID",SqlDbType.Int,4);
				parameterModuleID.Value = nModuleID;			
				myCommand.Parameters.Add(parameterModuleID);
			}

			if(nColumnID !=0)
			{
				SqlParameter parameterColumnContentID = new SqlParameter("@ColumnContentID",SqlDbType.Int,4);
				parameterColumnContentID.Value = nColumnID;			
				myCommand.Parameters.Add(parameterColumnContentID);
			}		

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

			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)parameterContentID.Value;
		}

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

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

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

			SqlParameter parameterBody = new SqlParameter("@Body",SqlDbType.Text);
			parameterBody.Value = sBody;
			myCommand.Parameters.Add(parameterBody);			
		
			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 UpdateFileFlag(int nContentID,int nFileFlag)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateFileFlag",myConnection);

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

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

			SqlParameter parameterFileFlag = new SqlParameter("@FileFlag",SqlDbType.Int,4);
			parameterFileFlag.Value = nFileFlag;
			myCommand.Parameters.Add(parameterFileFlag);

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

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

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

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