picturedb.cs

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

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

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for PictureDB.
	/// </summary>
	public class PictureDB
	{
		public SqlDataReader GetPictures()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetPictures",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 GetPictureByCont(int nContentID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetPictureByCont",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 SqlDataReader GetFileByCont(int nContentID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetPictureByCont",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 SqlDataReader GetSinglePicture(int nPictureID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSinglePicture",myConnection);

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

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

			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 AddPicture(String sTitle,byte[] bPicture,int nContentID,String sPictureType,String sFilePath)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddPicture",myConnection);

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

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

			SqlParameter parameterContentID = new SqlParameter("@ContentID",SqlDbType.Int,4);
			parameterContentID.Value = nContentID;
			myCommand.Parameters.Add(parameterContentID);

			SqlParameter parameterPictureType = new SqlParameter("@PictureType",SqlDbType.VarChar,20);
			parameterPictureType.Value = sPictureType;
			myCommand.Parameters.Add(parameterPictureType);

			SqlParameter parameterPicture = new SqlParameter("@PictureData",SqlDbType.Image);
			parameterPicture.Value = bPicture;
			myCommand.Parameters.Add(parameterPicture);

			SqlParameter parameterFilePath = new SqlParameter("@FilePath",SqlDbType.VarChar);
			parameterFilePath.Value = sFilePath;
			myCommand.Parameters.Add(parameterFilePath);

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

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

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

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

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

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

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

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

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

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