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

namespace Example_11_1
{
	/// <summary>
	/// Summary description for FileDB.
	/// </summary>
	public class FileDB
	{
		public static string SQLCONNECTIONSTRING = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();

		public SqlDataReader GetFiles()
		{
			//定義數據庫的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetFiles",myConnection);

			//定義訪問數據庫的方式為存儲過程
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlDataReader dr = null;

			try
			{
				//打開數據庫的連接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("數據庫連接失敗!",ex);
			}

			try 
			{
				//執行數據庫的存儲過程（訪問數據庫）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			
			return(dr);
		}

		public int AddFile(String sFileName,String sFileUrl,String sFileKind)
		{
			//定義數據庫的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddFile",myConnection);

			//定義訪問數據庫的方式為存儲過程
			myCommand.CommandType = CommandType.StoredProcedure;

			//創建訪問數據庫的參數	
			SqlParameter parameterFileName = new SqlParameter("@FileName",SqlDbType.VarChar,100);
			parameterFileName.Value = sFileName;
			myCommand.Parameters.Add(parameterFileName);

			SqlParameter parameterFileUrl = new SqlParameter("@FileUrl",SqlDbType.VarChar,200);
			parameterFileUrl.Value = sFileUrl;
			myCommand.Parameters.Add(parameterFileUrl);

			SqlParameter parameterFileKind = new SqlParameter("@FileKind",SqlDbType.VarChar,100);
			parameterFileKind.Value = sFileKind;
			myCommand.Parameters.Add(parameterFileKind);

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

			try
			{
				//打開數據庫的連接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("數據庫連接失敗!",ex);
			}

			try 
			{
				//執行數據庫的存儲過程（訪問數據庫）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//關閉數據庫的連接
					myConnection.Close();
				}
			}

			return (int)parameterFileID.Value;
		}
	}
}