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架构,展示层，数据访问层,业务处理层
       

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-706897/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-706897/