uploadfiledb.cs

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

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

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for UploadFileDB.
	/// </summary>
	public class UploadFileDB
	{
		public int AddUploadFile(String sFilePath,String sFileInfo,int nAuthor_ID,int nChannel_ID,int nTopic_ID,int nSubTopic_ID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddUploadFile",myConnection);

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

			//设置存储过程的参数
			SqlParameter parameterFilePath=new SqlParameter("@FilePath",SqlDbType.VarChar,100);
			parameterFilePath.Value=sFilePath;
			myCommand.Parameters.Add(parameterFilePath);

			SqlParameter parameterFileInfo=new SqlParameter("@FileInfo",SqlDbType.VarChar,100);
			parameterFileInfo.Value=sFileInfo;
			myCommand.Parameters.Add(parameterFileInfo);

			SqlParameter parameterAuthor_ID=new SqlParameter("@Author_ID",SqlDbType.Int,4);
			parameterAuthor_ID.Value=nAuthor_ID;
			myCommand.Parameters.Add(parameterAuthor_ID);

			if(nChannel_ID !=0)
			{
				SqlParameter parameterChannel_ID = new SqlParameter("@Channel_ID",SqlDbType.Int,4);
				parameterChannel_ID.Value = nChannel_ID;			
				myCommand.Parameters.Add(parameterChannel_ID);
			}

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

			if(nSubTopic_ID !=0)
			{
				SqlParameter parameterSubTopic_ID = new SqlParameter("@SubTopic_ID",SqlDbType.Int,4);
				parameterSubTopic_ID.Value = nSubTopic_ID;			
				myCommand.Parameters.Add(parameterSubTopic_ID);
			}


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

			SqlDataReader dr = null;

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

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

			return (int)parameterID.Value;
		}

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

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

			//设置存储过程的参数
			SqlParameter parameterID=new SqlParameter("@ID",SqlDbType.Int,4);
			parameterID.Value=nUploadFileID;
			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 GetFileByChannel(int nChannel_ID)		
		{
			//定义数据库的Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetFileByChannel",myConnection);

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

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

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

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

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

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

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

			SqlParameter parameterLinkAddress = new SqlParameter("@FilePath",SqlDbType.VarChar,100);
			parameterLinkAddress.Value = sLinkAddress;
			myCommand.Parameters.Add(parameterLinkAddress);	

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