c# 之tcbs在winform介面如何以mvc方法呼叫oracle函式

1,在c#前端介面呼叫oracle函式
 
//**************************************************************************//
        //****************   獲取檔案載入的最大次數     ***************************//
        //**************************************************************************//
        public void GetDeductSchedCount(long DeductSchedNbr, long SubmNbr, ref long LoadCount)
        {


            try
            {

                OracleCommand comm = new OracleCommand("OSIBANK.PACK_FILELOAD_PAYR.FUNC_GETMAXLOADNBR", base.Connection);
                comm.CommandType = CommandType.StoredProcedure;
                comm.BindByName = true;

                comm.Parameters.Add("in_DEDUCTSCHEDNBR", OracleDbType.Int64, DeductSchedNbr, ParameterDirection.Input);
                comm.Parameters.Add("in_SUBMNBR", OracleDbType.Int64, SubmNbr, ParameterDirection.Input);
                comm.Parameters.Add("LoadCount", OracleDbType.Int64, LoadCount, ParameterDirection.ReturnValue);
                int i = comm.ExecuteNonQuery();
                LoadCount = (comm.Parameters["LoadCount"].Value == DBNull.Value) ? 0 : Convert.ToInt64(comm.Parameters["LoadCount"].Value);


            }
            catch (OracleException se)
            {
                throw new DataAccessException(se.Number.ToString(), se, base.Id);
            }


 2,  以上方法在tcbs的資料訪問層 OraCoreDataAccess類中,此類繼承於OracleDataAccess, ICoreDataAccess




3,tcbs窗體介面 frmDedSchdSubmDtlNew會透過GetDeductSchedCount類來呼叫上述的GetDeductSchedCount方法


   GetDeductSchedCount類的呼叫如下所述:
 //獲取最大Load數，填充到一個combox上。這是個是一個最大值，要透過迴圈自己自１－Load數,並讓combox是預設值為Load數

            GetDeductSchedCount bodsc = GetDeductSchedCount.GGetGetDeductSchedCount(_DeductSchedNbr, _SubmNbr, new object[] { });


　　
　
4,oracle函式如下所述：

CREATE OR REPLACE PACKAGE BODY "PACK_FILELOAD_PAYR" AS
FUNCTION FUNC_GETMAXLOADNBR (in_DEDUCTSCHEDNBR IN DeductschedsubmPayrollList.DeductSchedNbr%TYPE,--合同號
                             in_SUBMNBR        IN DeductschedsubmPayrollList.SubmNbr%TYPE       )--批次號

RETURN DeductschedsubmPayrollList.FILELOADSEQNBR%TYPE IS
/********************************************************************************/
/*****                                                                      *****/
/*****      FUNCTION NAME:  FUNC_GETMAXLOADNBR                              *****/
/*****      DESCRIPTION:    找到這一批次的最大[LOAD次數]                    *****/
/*****                                                                      *****/
/*****      CALLED FROM:                                                    *****/
/*****                                                                      *****/
/*****      MODIFICATION HISTORY:                                           *****/
/*****      MODIFICATION HISTORY:-zxy, 2010/07/29, 初始建立              *****/
/*****                                                                      *****/
/********************************************************************************/

/********************************************************************************/
/*****                              變數定義                                *****/
/********************************************************************************/

lvnMaxLoadNbr        DeductschedsubmPayrollList.FILELOADSEQNBR%TYPE;--最大[LOAD次數]

/********************************************************************************/
/*****                              主處理開始                              *****/
/********************************************************************************/
BEGIN

     SELECT MAX(NVL(DP.FileLoadSeqNbr,0))
       INTO lvnMaxLoadNbr
       FROM DeductschedsubmPayrollList DP
      WHERE DP.DeductSchedNbr = in_DEDUCTSCHEDNBR
        AND DP.SubmNbr = in_SUBMNBR;

     IF lvnMaxLoadNbr IS NULL THEN
        lvnMaxLoadNbr:=0;
     END IF;

     RETURN lvnMaxLoadNbr;

END FUNC_GETMAXLOADNBR;





5,GetDeductSchedCount類呼叫OSIBANK.PACK_FILELOAD_PAYR.FUNC_GETMAXLOADNBR的實現如下所述

public class GetDeductSchedCount : CoreBusinessBase
    {
        private long _LoadCount;
        public long LoadCount
        {
            get { return this._LoadCount; }
            set
            {
                base.MarkDirty();
                this._LoadCount = value;
            }
        }
        internal GetDeductSchedCount()
        { }
       
         //20110908 zxy tcbs特有之私有方法
        private static GetDeductSchedCount GetGetDeductSchedCount()
        {
            return new GetDeductSchedCount();
        }
      
       
         //20110908 zxy下述靜態方法GGetGetDeductSchedCount為tcbs窗體frmDedSchdSubmDtlNew直接呼叫的方法
        public static GetDeductSchedCount GGetGetDeductSchedCount(long DeductSchedNbr,long SubmNbr, params object[] fetchOptions)
        {
             //20110908zxy 例項化GetDeductSchedCount本身類物件
            GetDeductSchedCount glt = new GetDeductSchedCount();
            try
            {
                 //呼叫本身類的內部巢狀類Criteria,
                Criteria criteria = new Criteria(DeductSchedNbr,SubmNbr, fetchOptions);
               
                //20110908zxy 透過fetch方法（其方法引數為上述criteria物件)
                glt = (GetDeductSchedCount)CoreBusinessBase.Director.Fetch(criteria);
            }
            catch (Exception ex)
            {
                string a = ex.Message.ToString();
            }
            return glt;//20110908zxy 返回glt
          
        }
         //svrfetch方法對映上述靜態方法 GGetGetDeductSchedCount之glt = (GetDeductSchedCount)CoreBusinessBase.Director.Fetch(criteria)中的fetch方法
        protected virtual void SvrFetch(DataAccessManager dam, object criteriaObj) //dataaccessmanager為tcbs管理資料訪問的類
        {
          
            Criteria criteria = (Criteria)criteriaObj;//search condition,
             //透過dataacessmanager類之getdataaccessprovider方法的getdeductschedcount方法真正實現呼叫oracle 之PACK_FILELOAD_PAYR包中的函式FUNC_GETMAXLOADNBR
             //關於lcoredataaccess介面，在下面會講到
            ((ICoreDataAccess)dam.GetDataAccessProvider(CoreUser.ProductNbr, "CORE")).GetDeductSchedCount(criteria.DeductSchedNbr,criteria.SubmNbr,ref this._LoadCount);
        

        }     
        protected override void Director_Fetch(object criteria)
        {
            if (CoreUser.IsOnline || ((CriteriaBase)criteria).IsForcedOnlineRequest)
            {
                try
                {
                    using (DataAccessManager dam = new DataAccessManager())
                    {
                        this.SvrFetch(dam, criteria);
                    }
                }
                catch (DataAccessException daExcp)
                {
                    throw new CoreBusinessException(99999L, daExcp, "An exception occurred while attempting to retrieve {0} information from the database. Key: {1}", new string[] { "GetDeductSchedCount", this.Key });
                }
            }
            else
            {
                this.SvrFetchOffline(criteria);
            }
        }
 
       [Serializable]
      
       //20110908zxy 被呼叫內部巢狀類的定義
    public class Criteria : CriteriaBase
    {
        private long _DeducthSchedNbr;
        public long DeductSchedNbr
        {
            get { return _DeducthSchedNbr; }
        }
        private long _SubmNbr;
        public long SubmNbr
        {
            get { return _SubmNbr; }
        }
     
         //建構函式
        public Criteria(long DeductSchedNbr,long SubmNbr, params object[] fetchOptions)
            : base(fetchOptions)
        {
            this._DeducthSchedNbr = DeductSchedNbr;
            this._SubmNbr = SubmNbr;
        }
       
    }
    --其它類中成員略去
}


6,ICoreDataAccess介面，定義GetDeductSchedCount方法的虛實現;摘錄相關程式碼如下：


     ///

       /// 獲得代發次數
     ///

     ///
     ///
     ///
      void GetDeductSchedCount(long DeductSchedNbr, long SubmNbr, ref long LoadCount);

      ///

      /// 在驗證後更改相關表的狀態
      ///

      ///
      ///
      ///
      ///
      ///
      ///
      ///
      ///
      ///

7,oracoredataacess類繼隨於ICoreDataAccess介面,定義了GetDeductSchedCount方法的實現
 
  public partial class OraCoreDataAccess : OracleDataAccess, ICoreDataAccess//看看，oracoredataaccess類繼承於介面ICoreDataAccess
  {
    //**************************************************************************//
        //****************   獲取檔案載入的最大次數      ***************************//
        //**************************************************************************//
        public void GetDeductSchedCount(long DeductSchedNbr, long SubmNbr, ref long LoadCount)
        {


            try
            {
                  //透過oraclecommand類呼叫oracle的函式
                OracleCommand comm = new OracleCommand("OSIBANK.PACK_FILELOAD_PAYR.FUNC_GETMAXLOADNBR", base.Connection);
                comm.CommandType = CommandType.StoredProcedure;
                comm.BindByName = true;
                  //透過oraclecommand的parameters集合之add方法建立c#的類方法的引數與oracle函式的輸入輸出或者輸入輸出引數的對應關係
                comm.Parameters.Add("in_DEDUCTSCHEDNBR", OracleDbType.Int64, DeductSchedNbr, ParameterDirection.Input);//input表明是輸入引數
                comm.Parameters.Add("in_SUBMNBR", OracleDbType.Int64, SubmNbr, ParameterDirection.Input);
                comm.Parameters.Add("LoadCount", OracleDbType.Int64, LoadCount, ParameterDirection.ReturnValue);//parameterdirection.returnvalue表明此引數是函式返回值
                int i = comm.ExecuteNonQuery();//執行函式
               
                //透過方法loadcount接受函式的返回值
                //dbnull表明是空值
                //? :是二重邏輯判斷,和c一樣
                LoadCount = (comm.Parameters["LoadCount"].Value == DBNull.Value) ? 0 : Convert.ToInt64(comm.Parameters["LoadCount"].Value);


            }
           
            //catch抓取oracleexception的異常
            catch (OracleException se)
            {
                  //throw丟擲異常
                throw new DataAccessException(se.Number.ToString(), se, base.Id);
            }

        }
  }
 



8,小結：
       tcbs實現了mvc架構,展示層，資料訪問層,業務處理層