C# OracleTransaction.Commit方法代码示例

 /// <summary>
 /// 执行一个OracleCommand(返回一个结果OracleDataReader)
 /// </summary>   
 /// <param name="connection">有效的 OracleConnection</param>
 /// <param name="transaction">有效的 OracleTransaction, or 'null'</param>
 /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
 /// <param name="commandText">存储过程名称或 PL/SQL</param> 
 /// <param name="commandParameters">以一个数组的形式返回OracleParameters </param>
 /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param>
 /// <returns></returns>
 private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
 {
     OracleCommand cmd = new OracleCommand();
     PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
     OracleDataReader dr = null;
     try
     {
         if (connectionOwnership == OracleConnectionOwnership.External)
         {
             dr = cmd.ExecuteReader();
         }
         else
         {
             //  dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
             dr = cmd.ExecuteReader();
         }
         if (transaction != null)
         {
             transaction.Commit();
         }
         return (OracleDataReader)dr;
     }
     catch
     {
         if (transaction != null)
         {
             transaction.Rollback();
         }
         throw;
     }
 }

 /// <summary>
 ///执行一个命令，没有返回数据集，只返回影响记录数 
 /// </summary>
 /// <remarks>
 /// 例如：  
 ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
 /// </remarks>
 /// <param name="transaction">有效的 OracleTransaction</param>
 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 /// <param name="commandText">存储过程名称或 PL/SQL</param>  
 /// <param name="commandParameters">OracleParameter[]</param>
 /// <returns>影响记录数</returns>
 public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
 {
     OracleCommand cmd = new OracleCommand();
     PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
     try
     {
         int returnInt = cmd.ExecuteNonQuery();
         if (transaction != null)
         {
             transaction.Commit();
         }
         return returnInt;
     }
     catch
     {
         if (transaction != null)
         {
             transaction.Rollback();
         }
         return 0;
     }
 }

        /// <summary>
        ///返回结果集的第一行第一列
     
        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的 OracleTransaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程T-OleDb的名称或命令</param>
        /// <param name="commandParameters">params OracleParameter[]</param>
        /// <returns>返回结果集的第一行第一列</returns>
        public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            try
            {
                object obj = cmd.ExecuteScalar();
                if (transaction != null)
                {
                    transaction.Commit();
                }
                return obj;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                return null;
            }

        }

        /// <summary>
        /// 执行一个OracleCommand命令并返回结果集

        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  DataTable ds = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的 OracleTransaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名称或 PL/SQL</param> 
        /// <param name="commandParameters">params OracleParameter[]</param>
        /// <returns>返回DataSet</returns>
        public static DataTable ExecuteTable(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            OracleDataAdapter da;
            DataSet ds = new DataSet();
            try
            {
                da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                if (transaction != null)
                {
                    transaction.Commit();
                }
            }
            catch
            {
                if (transaction != null)
                {
                    // Rollback the transaction
                    transaction.Rollback();
                }
            }

            //return the dataset
            return ds.Tables[0];
        }

        private void vUpdateStaff(OracleCommand command, OracleTransaction transaction)
        {
            string strUpdate = "update BASE_OPERATOR set OPERATORNO = :OPERATORNO, OPERATORNAME = :OPERATORNAME, "
                             + "FASTCODE = :FASTCODE, SEX = :SEX, BIRTHDAY = :BIRTHDAY, EMAIL = :EMAIL, CONTACTADDRESS = :CONTACTADDRESS, "
                             + "TELEPHONE = :TELEPHONE, MOBILETELEPHONE = :MOBILETELEPHONE, DEPARTID = :DEPARTID "
                             + "where OPERATORID = '" + m_sStaff.strOPERATORID + "'";

            command.CommandText = strUpdate;
            command.Parameters.Add(new OracleParameter("OPERATORNAME", OracleType.VarChar)).Value = tbName.Text;
            command.Parameters.Add(new OracleParameter("OPERATORNO", OracleType.VarChar)).Value = tbStaffNum.Text;
            command.Parameters.Add(new OracleParameter("FASTCODE", OracleType.VarChar)).Value = tbFastCode.Text;
            command.Parameters.Add(new OracleParameter("SEX", OracleType.VarChar)).Value = cbGender.Text;
            command.Parameters.Add(new OracleParameter("BIRTHDAY", OracleType.DateTime)).Value = dateTimePicker1.Value.ToString();
            command.Parameters.Add(new OracleParameter("EMAIL", OracleType.VarChar)).Value = tbEmail.Text;
            command.Parameters.Add(new OracleParameter("CONTACTADDRESS", OracleType.VarChar)).Value = tbAddress.Text;
            command.Parameters.Add(new OracleParameter("TELEPHONE", OracleType.VarChar)).Value = tbTel.Text;
            command.Parameters.Add(new OracleParameter("MOBILETELEPHONE", OracleType.VarChar)).Value = tbMobile.Text;
            command.Parameters.Add(new OracleParameter("DEPARTID", OracleType.VarChar)).Value = sleDepart.EditValue ?? "";

            command.ExecuteNonQuery();
            transaction.Commit();
            MessageBox.Show("修改成功！");
        }

        public static string GetLotNo(string userID, double productLOID, OracleTransaction zTrans)
        {
            bool LetClose = false;
            string code = "";
            OracleConnection zConn = null;
            if (zTrans == null)
            {
                LetClose = true;
                zConn = OracleDB.GetConnection();
                zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
            }

            ProductBarcodeDAL _bDAL = new ProductBarcodeDAL();
            if (_bDAL.GetDataByLOID(productLOID, zTrans))
            {
                ProductMasterDAL _dal = new ProductMasterDAL();
                if (_dal.GetDataByLOID(_bDAL.PRODUCTMASTER, zTrans))
                {
                    if (_dal.YEAR == (DateTime.Now.Year + 543).ToString().Substring(2))
                    {
                        if (_dal.RUNNING == "9")
                        {
                            _dal.RUNNING = "1";
                            switch (_dal.LOTNO)
                            {
                                case "A": _dal.LOTNO = "B"; break;
                                case "B": _dal.LOTNO = "C"; break;
                                case "C": _dal.LOTNO = "D"; break;
                                case "D": _dal.LOTNO = "E"; break;
                                case "E": _dal.LOTNO = "F"; break;
                                case "F": _dal.LOTNO = "G"; break;
                                case "G": _dal.LOTNO = "H"; break;
                                case "H": _dal.LOTNO = "I"; break;
                                case "I": _dal.LOTNO = "J"; break;
                                case "J": _dal.LOTNO = "K"; break;
                                case "K": _dal.LOTNO = "L"; break;
                                case "L": _dal.LOTNO = "M"; break;
                                case "M": _dal.LOTNO = "N"; break;
                                case "N": _dal.LOTNO = "O"; break;
                                case "O": _dal.LOTNO = "P"; break;
                                case "P": _dal.LOTNO = "Q"; break;
                                case "Q": _dal.LOTNO = "R"; break;
                                case "R": _dal.LOTNO = "S"; break;
                                case "S": _dal.LOTNO = "T"; break;
                                case "T": _dal.LOTNO = "U"; break;
                                case "U": _dal.LOTNO = "V"; break;
                                case "V": _dal.LOTNO = "W"; break;
                                case "W": _dal.LOTNO = "X"; break;
                                case "X": _dal.LOTNO = "Y"; break;
                                case "Y": _dal.LOTNO = "Z"; break;
                                case "Z": _dal.LOTNO = "A"; break;
                                default: _dal.LOTNO = "A"; break;
                            }
                        }
                        else
                        {
                            _dal.RUNNING = (Convert.ToInt32(_dal.RUNNING == "" ? "0" : _dal.RUNNING) + 1).ToString().Trim();
                        }
                    }
                    else
                    {
                        _dal.LOTNO = "A";
                        _dal.RUNNING = "1";
                        _dal.YEAR = (DateTime.Now.Year + 543).ToString().Substring(2);
                    }

                    if (!_dal.UpdateCurrentData(userID, zTrans))
                    {
                        if (LetClose)
                        {
                            zTrans.Commit();
                            zConn.Close();
                        }
                        throw new ApplicationException(_dal.ErrorMessage);
                    }
                    else
                    {
                        code = (_dal.CODE.Length > 3 ? _dal.CODE.Substring(0, 3) : _dal.CODE) + " " + _dal.LOTNO + _dal.RUNNING + _dal.YEAR;
                    }
                }
            }


            if (LetClose)
            {
                zTrans.Commit();
                zConn.Close();
            }

            return code.ToUpper();
        }

        /// <summary>
        /// 执行一个OracleCommand命令并返回结果集;执行完成没有关闭OracleConnection连接,需要手动关闭

        /// </summary>
        /// <remarks>
        /// 例如：  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的 OracleTransaction</param>
        /// <param name="commandType">命令类型:SQL语句或存储过程</param>
        /// <param name="commandText">存储过程名称或 PL/SQL</param> 
        /// <param name="commandParameters">params OracleParameter[]</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            OracleDataAdapter da;
            DataSet ds = new DataSet();
            try
            {
                da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                if (transaction != null)
                {
                    transaction.Commit();
                }
            }
            catch (System.Exception e)
            {
                if (transaction != null)
                {
                    // Rollback the transaction
                    transaction.Rollback();
                }
                throw new System.Exception(e.Message, e);
            }

            //return the dataset
            return ds;
        }

        private void vAddStaff(OracleCommand command, OracleTransaction transaction)
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "BASE_OPERATOR_INSERT";

            command.Parameters.Add("ls_OPERATORNO", OracleType.VarChar).Value = tbStaffNum.Text;
            command.Parameters.Add("ls_OPERATORNAME", OracleType.VarChar).Value = tbName.Text;
            command.Parameters.Add("ls_FASTCODE", OracleType.VarChar).Value = tbFastCode.Text;
            command.Parameters.Add("ls_SEX", OracleType.VarChar).Value = cbGender.Text;
            command.Parameters.Add("ls_BIRTHDAY", OracleType.VarChar).Value = dateTimePicker1.Value.ToString();
            command.Parameters.Add("ls_EMAIL", OracleType.VarChar).Value = tbEmail.Text;
            command.Parameters.Add("ls_CONTACTADDRESS", OracleType.VarChar).Value = tbAddress.Text;
            command.Parameters.Add("ls_TELEPHONE", OracleType.VarChar).Value = tbTel.Text;
            command.Parameters.Add("ls_MOBILETELEPHONE", OracleType.VarChar).Value = tbMobile.Text;
            command.Parameters.Add("ls_DEPARTID", OracleType.VarChar).Value = sleDepart.EditValue ?? "";

            command.Parameters.Add("descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
            command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
            command.Parameters.Add("ls_OPTID", OracleType.VarChar, 255).Direction = ParameterDirection.Output;

            command.ExecuteNonQuery();

            transaction.Commit();
            MessageBox.Show(command.Parameters["Message"].Value.ToString());
        }

        private void vUpdateDepart(OracleCommand command, OracleTransaction transaction)
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "jt_j_dwxx_update";

            command.Parameters.Add("ls_DWID", OracleType.VarChar).Value = m_sDWXX.strDWID;
            command.Parameters.Add("ls_DWBH", OracleType.VarChar).Value = teDWBH.Text;
            command.Parameters.Add("ls_DWMC", OracleType.VarChar).Value = teDWMC.Text;
            command.Parameters.Add("ls_DWJC", OracleType.VarChar).Value = teDWJC.Text;
            command.Parameters.Add("ls_ZJM", OracleType.VarChar).Value = teZJM.Text;
            command.Parameters.Add("ls_SJDWID", OracleType.VarChar).Value = sleDWXX.EditValue ?? "";
            command.Parameters.Add("ls_DWSX", OracleType.VarChar).Value = cbDWSX.SelectedValue;
            command.Parameters.Add("ls_BMLXID", OracleType.VarChar).Value = cbBMLX.SelectedValue;
            command.Parameters.Add("ls_GYSLXID", OracleType.VarChar).Value = cbGYSLX.SelectedValue;
            command.Parameters.Add("ls_KHLXID", OracleType.VarChar).Value = cbKHLX.SelectedValue;
            command.Parameters.Add("ls_KFLXID", OracleType.VarChar).Value = cbKFLX.SelectedValue;
            command.Parameters.Add("ls_YINSDWLXID", OracleType.VarChar).Value = cbYSCLX.SelectedValue;
            command.Parameters.Add("ls_CBSLXID", OracleType.VarChar).Value = cbCBSLX.SelectedValue;
            command.Parameters.Add("ls_YSDWLXID", OracleType.VarChar).Value = cbYSDWLX.SelectedValue;
            command.Parameters.Add("ls_BMZTID", OracleType.VarChar).Value = sleZTBM.EditValue ?? "";
            command.Parameters.Add("ls_GZLID", OracleType.VarChar).Value = "";
            command.Parameters.Add("ls_DWJB", OracleType.Number).Value = teDWJB.EditValue ?? 0;
            command.Parameters.Add("ls_DWFR", OracleType.VarChar).Value = teDWFR.Text;
            command.Parameters.Add("ls_SFID", OracleType.VarChar).Value = sleSFBM.EditValue ?? "";
            command.Parameters.Add("ls_DQID", OracleType.VarChar).Value = sleDQ.EditValue ?? "";
            command.Parameters.Add("ls_YZBM", OracleType.VarChar).Value = teYZBM.Text;
            command.Parameters.Add("ls_TXDZ", OracleType.VarChar).Value = teTXDZ.Text;
            command.Parameters.Add("ls_DH", OracleType.VarChar).Value = teDH.Text;
            command.Parameters.Add("ls_CZ", OracleType.VarChar).Value = teCZ.Text;
            command.Parameters.Add("ls_LXR", OracleType.VarChar).Value = teLXR.Text;
            command.Parameters.Add("ls_KHYH", OracleType.VarChar).Value = teKHYH.Text;
            command.Parameters.Add("ls_ZH", OracleType.VarChar).Value = teZH.Text;
            command.Parameters.Add("ls_SH", OracleType.VarChar).Value = teSH.Text;
            command.Parameters.Add("ls_EMAIL", OracleType.VarChar).Value = teEMAIL.Text;
            command.Parameters.Add("ls_WZ", OracleType.VarChar).Value = teWZ.Text;
            command.Parameters.Add("ls_JSFSID", OracleType.VarChar).Value = teJSFS.Text;
            command.Parameters.Add("ls_TSSX", OracleType.Number).Value = teTSSX.EditValue ?? 0;
            command.Parameters.Add("ls_TSXX", OracleType.Number).Value = teTSXX.EditValue ?? 0;
            command.Parameters.Add("ls_SDSX", OracleType.Number).Value = teSDSX.EditValue ?? 0;
            command.Parameters.Add("ls_SDXX", OracleType.Number).Value = teSDXX.EditValue ?? 0;
            command.Parameters.Add("ls_ZKSX", OracleType.Number).Value = teZKSX.EditValue ?? 0;
            command.Parameters.Add("ls_ZKXX", OracleType.Number).Value = teZKXX.EditValue ?? 0;
            command.Parameters.Add("ls_JSDWID", OracleType.VarChar).Value = sleJSDW.EditValue ?? "";
            command.Parameters.Add("ls_DJSDBZ", OracleType.VarChar).Value = cbeDJSDBZ.Text;
            command.Parameters.Add("ls_TSJSDBZ", OracleType.VarChar).Value = cbeTSJSDBZ.Text;
            command.Parameters.Add("ls_KHZZYFBZ", OracleType.Number).Value = teZZYFBZ.EditValue ?? 0;
            command.Parameters.Add("ls_KHZZBZFBZ", OracleType.Number).Value = teZZBZFBZ.EditValue ?? 0;
            command.Parameters.Add("ls_KHZZQTFYBZ", OracleType.Number).Value = teZZQTFYBZ.EditValue ?? 0;
            command.Parameters.Add("ls_CGJSZQ", OracleType.Number).Value = teCGJSZQ.EditValue ?? 0;
            command.Parameters.Add("ls_TSJSZQ", OracleType.Number).Value = teXSJSZQ.EditValue ?? 0;
            command.Parameters.Add("ls_SHDZ", OracleType.VarChar).Value = teSHDZ.Text;
            command.Parameters.Add("ls_KJDZ", OracleType.VarChar).Value = teKJDZ.Text;
            command.Parameters.Add("ls_MJDZ", OracleType.VarChar).Value = teMJDZ.Text;
            command.Parameters.Add("ls_YSFSID", OracleType.VarChar).Value = cbYSFS.SelectedValue;
            command.Parameters.Add("ls_YSFXID", OracleType.VarChar).Value = teYSFX.Text;
            command.Parameters.Add("ls_ZZDBZ", OracleType.VarChar).Value = teZZDBZ.Text;
            command.Parameters.Add("ls_YZDBH", OracleType.VarChar).Value = teYZDBH.Text;
            command.Parameters.Add("ls_EZDBH", OracleType.VarChar).Value = teEZDBH.Text;
            command.Parameters.Add("ls_KFDZ", OracleType.VarChar).Value = teKFDZ.Text;
            command.Parameters.Add("ls_TDYZS", OracleType.Number).Value = teTTDYZS.EditValue ?? 0;
            command.Parameters.Add("ls_DWTJBH", OracleType.VarChar).Value = teTJBH.Text;
            command.Parameters.Add("ls_CGJSYXJB", OracleType.Number).Value = teCGJSYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_XSJSYXJB", OracleType.Number).Value = teXSJSYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_PHYXJB", OracleType.Number).Value = tePHYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_XTYXJB", OracleType.Number).Value = teXTYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_JTYXJB", OracleType.Number).Value = teJTYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_CGYXJB", OracleType.Number).Value = teCGYXJ.EditValue ?? 0;
            command.Parameters.Add("ls_WEBYH", OracleType.VarChar).Value = teWEBYH.Text;
            command.Parameters.Add("ls_WEBMM", OracleType.VarChar).Value = teWEBMM.Text;
            command.Parameters.Add("ls_ZT", OracleType.VarChar).Value = cbeZT.Text;
            command.Parameters.Add("ls_CJR", OracleType.VarChar).Value = FrmLogin.getUserName;
            command.Parameters.Add("ls_TYR", OracleType.VarChar).Value = "";
            command.Parameters.Add("ls_BZ", OracleType.VarChar).Value = teBZ.Text;
            command.Parameters.Add("ls_SFZT", OracleType.VarChar).Value = cbSFZT.SelectedValue;
            command.Parameters.Add("ls_CWXTID", OracleType.VarChar).Value = "";

            command.Parameters.Add("descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
            command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;

            command.ExecuteNonQuery();

            transaction.Commit();
            MessageBox.Show(command.Parameters["Message"].Value.ToString());
        }

 /// <summary>
 /// 提交事务
 /// </summary>
 /// <param name="transaction">事务对象</param>
 /// <param name="connection">连接对象</param>
 public static void Commit(OracleTransaction transaction, OracleConnection connection)
 {
     transaction.Commit();
     connection.Close();
 }

        /// <summary>
        /// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的 OracleTransaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名称或 PL/SQL</param> 
        /// <param name="commandParameters">params OracleParameter[]</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            //create a command and prepare it for execution
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            //create the DataAdapter & DataSet
            OracleDataAdapter da;
            DataSet ds = new DataSet();
            try
            {
                da = new OracleDataAdapter(cmd);
                //fill the DataSet using default values for DataTable names, etc.
                da.Fill(ds);
                if (transaction != null)
                {
                    transaction.Commit();
                }
            }
            catch
            {
                if (transaction != null)
                {
                    // Rollback the transaction
                    transaction.Rollback();
                }
            }

            //return the dataset
            return ds;
        }

        /// <summary>
        ///返回结果集的第一行第一列
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
        /// </remarks>
        /// <param name="transaction">有效的 OracleTransaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-OleDb command</param>
        /// <param name="commandParameters">params OracleParameter[]</param>
        /// <returns>返回结果集的第一行第一列</returns>
        public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            //create a command and prepare it for execution
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            try
            {
                //execute the command & return the results
                object obj = cmd.ExecuteScalar();
                if (transaction != null)
                {
                    transaction.Commit();
                }
                return obj;
            }
            catch
            {
                if (transaction != null)
                {
                    // Rollback the transaction
                    transaction.Rollback();
                }
                return null;
            }

        }

        public static double GetLOID(string tablename, OracleTransaction zTrans)
        {
            bool LetClose = false;
            OracleConnection zConn = null;
            if (zTrans == null)
            {
                LetClose = true;
                zConn = GetConnection();
                zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
            }

            string lastid;
            string sqlz = "SELECT SQ" + tablename.ToUpper() + ".NEXTVAL AS RUNNINGNUMBER FROM DUAL";

            try
            {
                lastid = ExecSingleCmd(sqlz, zTrans).ToString();
            }
            catch (Exception ex)
            {
                if (LetClose)
                {
                    zTrans.Commit();
                    zConn.Close();
                }
                throw new ApplicationException(ex.Message);
            }

            if (LetClose)
            {
                zTrans.Commit();
                zConn.Close();
            }
            return Convert.ToDouble(lastid);
        }

        public static string GetRunningCode(DateTime docDate, string RunningItem, OracleTransaction zTrans)
        {
            string tablename = "RUNNINGDAY";
            string code = "";
            string day = docDate.ToString("yyMMdd");
            bool LetClose = false;
            OracleConnection zConn = null;
            if (zTrans == null)
            {
                LetClose = true;
                zConn = GetConnection();
                zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            string loid = "";
            string lastValue = "";
            string sqlz = "SELECT LOID, RUNNING, DAY, VALUE FROM " + tablename + " WHERE DAY = '" + day + "' AND RUNNING = '" + RunningItem + "' ";
            OracleDataReader zRd = ExecQueryCmd(sqlz, zTrans);
            if (zRd.Read())
            {
                loid = zRd["LOID"].ToString();
                lastValue = zRd["VALUE"].ToString();
                lastValue = (Convert.ToDouble(lastValue) + 1).ToString("000");
                zRd.Close();

                sqlz = "UPDATE " + tablename + " SET VALUE = '" + lastValue + "' WHERE LOID = " + loid;
                ExecNonQueryCmd(sqlz, zTrans);
            }
            else
            {
                lastValue = "001";
                loid = GetLOID("RUNNINGDAY", zTrans).ToString();
                sqlz = "INSERT INTO " + tablename + " (LOID, DAY, VALUE, RUNNING) VALUES (" + loid + ", '" + day + "', '" + lastValue + "', '" + RunningItem + "') ";
                ExecNonQueryCmd(sqlz, zTrans);
            }
            code = day + lastValue;

            if (LetClose)
            {
                zTrans.Commit();
                zConn.Close();
            }
            return code;
        }