/*****************************************************************************************************
*
* 作    者: 韓永亮
*
* 創(chuàng)建日期：2006-11-6
*
* 功能描述：數(shù)據(jù)層，與科目表相關(guān)的操作，含有向科目表中添加新記錄、顯示科目表中的所有信息、更新科目表中的信息等方法。
*
*
* 處理過(guò)程：AddnewWarrant：通過(guò)SqlHelper.ExecuteNonQuery方法，使用存儲(chǔ)過(guò)程“usps_proInsWarrant”來(lái)向數(shù)據(jù)庫(kù)中的“憑證表” 所有字段插入數(shù)據(jù)。
*			ListAllWarrant：通過(guò)SqlHelper.ExecuteNonQuery方法，使用存儲(chǔ)過(guò)程“usps_proSelectWarrant”來(lái)將數(shù)據(jù)庫(kù)中的“憑證表”所有字段選擇出來(lái)。
*			Update：調(diào)用SqlHelper.ExecuteDataset方法，使用存儲(chǔ)過(guò)程“usps_proUpdateWarrant”來(lái)更新數(shù)據(jù)庫(kù)中的“憑證表” 所有字段的數(shù)據(jù)。
*
* 調(diào)用說(shuō)明：實(shí)例化DataLayer.SqlWarrtAccess類(lèi)以后，便可調(diào)用其中的方法。
*			AddnewWarrant：傳入?yún)?shù)為objWarrant，返回值如果大于0則表示添加成功。
*			ListAllWarrant：無(wú)參數(shù)，返回值為DataSet。
*			Update：傳入?yún)?shù)為objWarrant，返回值為空。
*			
*************************************************************************************************/
using System;
using Microsoft.ApplicationBlocks.Data;//導(dǎo)入SqlHelper類(lèi)
using AbstractLayer;
using System.Data;
using System.Data.SqlClient;

namespace DataLayer
{
	/// <summary>
	/// SqlSubjectAccess 的摘要說(shuō)明。
	/// </summary>
	public class SqlSubjectAccess : IsubjectAccess
	{
		#region SqlSubjectAccess構(gòu)造函數(shù)
		/// <summary>
		/// 將接受的_connectionString賦給m_Connection_String
		/// </summary>
		/// <param name="string">_connectionString</param>
		/// <returns></returns>
		string m_Connection_String = "server=219.219.221.30; UID=sa; Password=; Database=test";
		public SqlSubjectAccess(string _connectionString)
		{
			m_Connection_String=_connectionString;
		}
		#endregion


		#region 用于向科目表中添加信息：
		/// <summary>
		/// 向科目表中添加新記錄
		/// </summary>
		/// <param name="AbstractLayer.baseSubject">objSubject</param>
		/// <returns>－1則表示添加失敗，1則表示添加成功</returns>

		public int AddnewSubject (AbstractLayer.baseSubject  objSubject )
		{
			//實(shí)例化事務(wù)
			SqlTransaction objTrans=null;
			//實(shí)例化鏈接字符串
			SqlConnection myConnection =  new SqlConnection(m_Connection_String);
			try
			{
				//打開(kāi)數(shù)據(jù)庫(kù)連接
				myConnection.Open();
				//開(kāi)始事務(wù)
				objTrans= myConnection.BeginTransaction();
				//定義SqlParameter數(shù)組,其大小為7,數(shù)組名為arrParam
				SqlParameter [] arrParam=new SqlParameter[7];
				//給arrParam參數(shù)賦值(參考SqlDataAccess類(lèi))
				arrParam[0] = new SqlParameter("@subjectID",objSubject . subjectID);
				arrParam[1] = new SqlParameter("@subjectName",objSubject .subjectName);
				arrParam[2] = new SqlParameter("@memId",objSubject . memId);
				arrParam[3] = new SqlParameter("@subjectCat",objSubject . subjectCat);
				arrParam[4] = new SqlParameter("@hasSubject ",objSubject . hasSubject);
				arrParam[5] = new SqlParameter("@balanceDirec ",objSubject . balanceDirec);
				arrParam[6] = new SqlParameter("@quantityUnit ",objSubject . quantityUnit);
													

				//調(diào)用SqlHelper.ExecuteNonQuery方法，并使用usps_proInsSubject 存儲(chǔ)過(guò)程
				SqlHelper.ExecuteNonQuery(m_Connection_String,CommandType.StoredProcedure,"usps_proInsSubject",arrParam);
	
			}
			catch(Exception Ex)
			{
				//參數(shù)失敗，則返回－1,同時(shí)回滾事務(wù)
				objTrans.Rollback();
				return -1;
								 
			}
			finally
			{
				//關(guān)閉連接
				myConnection.Close();

			}
			return 1;//成功的情況

		}
		#endregion


		#region 顯示科目表中的所有信息：
		/// <summary>
		/// 顯示科目表中的所有信息
		/// </summary>
		/// <param name=""></param>
		/// <returns>憑科目中所有信息的數(shù)據(jù)集</returns>

		public DataSet ListAllSubject  ()
		{
			DataSet dtStSubject ;
			//調(diào)用SqlHelper.ExecuteDataset方法，并使用usps_proUpdateSubject 存儲(chǔ)過(guò)程，并將數(shù)據(jù)集賦給dtStSubject ;
			dtStSubject = SqlHelper.ExecuteDataset("server=127.0.0.1; UID=sa; Password=; Database=test",CommandType.StoredProcedure,"usps_proSelectSubject");

			return dtStSubject ;
		}	
		#endregion

		
		#region 更新科目表中的信息：
		/// <summary>
		/// 更新科目表中的信息
		/// </summary>
		/// <param name="AbstractLayer.baseWarrant">objSubject</param>
		/// <returns></returns>

		public void Update(AbstractLayer.baseSubject  objSubject )
		{
			//實(shí)例化事務(wù)
			SqlTransaction objTrans=null;
			//實(shí)例化鏈接字符串
			SqlConnection myConnection =  new SqlConnection(m_Connection_String);
			try
			{
				//打開(kāi)數(shù)據(jù)庫(kù)連接
				myConnection.Open();
				//開(kāi)始事務(wù)
				objTrans= myConnection.BeginTransaction();
				//定義SqlParameter數(shù)組,其大小為7,數(shù)組名為prSubjectParms
				SqlParameter [] prAccountParms=new SqlParameter[7];
				//給prAccountParms參數(shù)賦值(參考SqlDataAccess類(lèi))
				prAccountParms[0] = new SqlParameter("@subjectID",objSubject . subjectID);
				prAccountParms[1] = new SqlParameter("@subjectName",objSubject .subjectName);
				prAccountParms[2] = new SqlParameter("@memId",objSubject . memId);
				prAccountParms[3] = new SqlParameter("@subjectCat",objSubject . subjectCat);
				prAccountParms[4] = new SqlParameter("@hasSubject ",objSubject . hasSubject);
				prAccountParms[5] = new SqlParameter("@balanceDirec ",objSubject . balanceDirec);
				prAccountParms[6] = new SqlParameter("@quantityUnit ",objSubject . quantityUnit);
				//調(diào)用SqlHelper.ExecuteNonQuery方法，并使用usps_proUpdateSubject 存儲(chǔ)過(guò)程
				SqlHelper.ExecuteNonQuery(m_Connection_String,CommandType.StoredProcedure,"usps_proUpdateSubject",prAccountParms);
			}
			catch(Exception Ex)
			{
				//回滾事務(wù)
				objTrans.Rollback();
				throw;
			}
			finally
			{
				//關(guān)閉連接
				myConnection.Close();
			}
		}

	}
	#endregion

}