/// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command.
        /// </summary>
        /// <param name="command">the OracleCommand to be prepared</param>
        /// <param name="connection">a valid OracleConnection, on which to execute this command</param>
        /// <param name="transaction">a valid OracleTransaction, or 'null'</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param> 
        /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
        private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            command.Connection = connection;

            //set the command text (stored procedure name or Oracle statement)
            command.CommandText = commandText;
            command.CommandTimeout = 200000;
            //if we were provided a transaction, assign it.
            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            //set the command type
            command.CommandType = commandType;

            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }

            return;
        }

 public bool UpdateQCResult(double loid, DateTime duedate, double qcqty1, double qcqty2, double qcqty3, string qcresult, string qcremark, string userID, string status, OracleTransaction zTrans)
 {
     string sql = "UPDATE PDPRODUCT SET QCRESULT = '" + qcresult + "', ";
     sql += "QCQTY1 = " + qcqty1 + ", ";
     sql += "QCQTY2 = " + qcqty2 + ", ";
     sql += "QCQTY3 = " + qcqty3 + ", ";
     sql += "QCREMARK = '" + qcremark + "', ";
     sql += "PRODSTATUS = '" + status + "', ";
     sql += "QCDUEDATE = " + OracleDB.QRDateTime(duedate) + ", ";
     sql += "UPDATEBY = '" + userID + "', ";
     sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
     sql += "WHERE LOID = " + loid + " ";
     //+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : ""));
     bool ret = true;
     try
     {
         ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
         if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
     }
     catch (Exception ex)
     {
         ret = false;
         _error = ex.Message;
     }
     return ret;
 }

 public static int Save(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans)
 {
     if(loan.Id == null){
         return Insert(loan, oraDbConn, oraTrans);
     } else {
         return Update(loan);
     }
 }

 /// <summary>
 /// Execute an OracleCommand (that returns no resultset) against an existing database transaction 
 /// using the provided parameters.
 /// </summary>
 /// <remarks>
 /// e.g.:  
 ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
 /// </remarks>
 /// <param name="trans">an existing database transaction</param>
 /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
 /// <param name="commandText">the stored procedure name or PL/SQL command</param>
 /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
 /// <returns>an int representing the number of rows affected by the command</returns>
 public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
 {
     OracleCommand cmd = new OracleCommand();
     PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
     int val = cmd.ExecuteNonQuery();
     cmd.Parameters.Clear();
     return val;
 }

 public static int Save(Applicant applicant, OracleConnection oraDbConn, OracleTransaction oraTrans)
 {
     if (applicant.Id == null) {
         return Insert(applicant, oraDbConn, oraTrans);
     } else {
         return Update(applicant);
     }
 }

 public OracleCommandSet(OracleConnection connection, OracleTransaction transaction)
 {
     this._usedParameterNames = new Hashtable(StringComparer.OrdinalIgnoreCase);
     this._commandList = new ArrayList();
     this._batchCommand = new OracleCommand();
     this.Connection = connection;
     this.Transaction = transaction;
 }

        /// <summary>
        /// Get Data List of This Table
        /// </summary>
        /// <param name="whereCause"></param>
        /// <param name="zTrans">Transaction, set to null if no transaction provided</param>
        /// <returns></returns>
        public DataTable GetDataList(string whereCause, OracleTransaction zTrans)
        {
            string sql = "SELECT PT.* , ";
            sql += "CASE PT.TYPE WHEN '" + Constz.ProductType.Type.FG.Code + "' THEN '" + Constz.ProductType.Type.FG.Rank + "' ";
            sql += "WHEN '" + Constz.ProductType.Type.WH.Code + "' THEN '" + Constz.ProductType.Type.WH.Rank + "' ";
            sql += "WHEN '" + Constz.ProductType.Type.Others.Code + "' THEN '" + Constz.ProductType.Type.Others.Rank + "' ";
            sql += "ELSE '' END AS RANK FROM PRODUCTTYPE PT ORDER BY RANK,PT.CODE";

            return OracleDB.ExecListCmd(sql);
        }

		public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx)
		{
			moreResults = -1;
			preparedStatement = null;
			CommandText = commandText;
			Connection = connection;
			Transaction = tx;
			CommandType = CommandType.Text;
			UpdatedRowSource = UpdateRowSource.Both;
			DesignTimeVisible = true;
			parameters = new OracleParameterCollection ();
		}

        public FrmSupplierCorrection(OracleConnection Conn, OracleTransaction Trans, string strGYSMC)
        {
            string strSQL = "select DWID, DWMC, DWBH, ZJM from JT_J_DWXX";
            OracleDataAdapter ada = new OracleDataAdapter(strSQL, Conn);
            ada.SelectCommand.Transaction = Trans;
            DataSet ds = new DataSet();
            ada.Fill(ds, "JT_J_DWXX");

            InitializeComponent();

            jTJDWXXBindingSource.DataSource = ds;
            jTJDWXXBindingSource.DataMember = "JT_J_DWXX";

            teOldSupplier.Text = strGYSMC;
        }

 public bool UpdateQCStockinItem(double stockin, string userID, string status, OracleTransaction zTrans)
 {
     string sql = "UPDATE STOCKINITEM ";
     sql += "SET STATUS = '" + status + "', ";
     sql += "UPDATEBY = '" + userID + "', ";
     sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
     sql += "WHERE STOCKIN = " + stockin + " ";
     bool ret = true;
     try
     {
         ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
         if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
     }
     catch (Exception ex)
     {
         ret = false;
         _error = ex.Message;
     }
     return ret;
 }

 public bool UpdatePDOrder(double loid, string userID, string status, OracleTransaction zTrans)
 {
     string sql = "UPDATE PDORDER SET ";
     sql += "STATUS = '" + status + "', ";
     sql += "UPDATEBY = '" + userID + "', ";
     sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
     sql += "WHERE LOID = " + loid + " ";
     //+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : ""));
     bool ret = true;
     try
     {
         ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
         if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
     }
     catch (Exception ex)
     {
         ret = false;
         _error = ex.Message;
     }
     return ret;
 }

 public bool UpdateQCStockin(double loid, string anacode, DateTime anadate, string userID, string status, OracleTransaction zTrans)
 {
     string sql = "UPDATE STOCKIN SET ANACODE = '" + anacode + "', ";
     sql += "STATUS = '" + status + "', ";
     sql += "ANADATE = " + OracleDB.QRDateTime(anadate) + ", ";
     sql += "UPDATEBY = '" + userID + "', ";
     sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
     sql += "WHERE LOID = " + loid + " ";
     bool ret = true;
     try
     {
         ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
         if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
     }
     catch (Exception ex)
     {
         ret = false;
         _error = ex.Message;
     }
     return ret;
 }

        internal static bool ExecuteNonQuery(string sql, OracleTransaction trans)
        {
            bool ret = false;
            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 240;
                cmd.ExecuteNonQuery();

                ret = true;
            }
            catch (Exception ex)
            {
                ret = false;
            }
            return ret;
        }

        public static int ExecNonQueryCmd(string sqlz, OracleTransaction zTrans)
        {
            OracleCommand zCommand = new OracleCommand();

            int retval;

            if (zTrans != null)
            {
                BuildzCommand(zCommand, zTrans.Connection, zTrans, CommandType.Text, sqlz, null);
                retval = zCommand.ExecuteNonQuery();
            }
            else
            {
                using (OracleConnection zConn = new OracleConnection(ConnectionString))
                {
                    BuildzCommand(zCommand, zConn, zTrans, CommandType.Text, sqlz, null);
                    retval = zCommand.ExecuteNonQuery();
                }
            }
            return retval;

        }

        private static int Insert(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans)
        {
            int LoanId = 0;

            using (OracleCommand insertLoanCommand = new OracleCommand()) {
                insertLoanCommand.CommandType = CommandType.StoredProcedure;
                insertLoanCommand.CommandText = "LoansPKG.insertLoan";
                insertLoanCommand.Connection = oraDbConn;
                insertLoanCommand.Transaction = oraTrans;

                insertLoanCommand.Parameters.AddWithValue("AppId", loan.ApplicantId);
                insertLoanCommand.Parameters.AddWithValue("LoanType", loan.Type);
                insertLoanCommand.Parameters.AddWithValue("LoanAmount", loan.Amount);

                OracleParameter outputLoanId = new OracleParameter("LoanId", OracleType.Number);
                outputLoanId.Direction = ParameterDirection.Output;
                insertLoanCommand.Parameters.Add(outputLoanId);
                insertLoanCommand.ExecuteNonQuery();
                LoanId = Convert.ToInt32(outputLoanId.Value);
            }

            return LoanId;
        }

        internal static DataTable ExecuteTable(string sql, OracleTransaction trans)
        {
            OracleCommand cmd = new OracleCommand();
            OracleDataAdapter adapter = new OracleDataAdapter();
            adapter.SelectCommand = cmd;
            DataTable dt = new DataTable();

            try
            {
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 240;

                adapter.Fill(dt);
                adapter.Dispose();
            }
            catch (Exception ex)
            {
                dt = new DataTable();
            }
            return dt;
        }

 /// <summary>
 /// 执行一个存储过程，没有返回数据集，只返回影响记录数 
 /// 这个方法并没有提供存取到输出参数或存储过程的返回值参数。
 /// </summary>
 /// <remarks>
 /// 这种方法并没有提供存取到输出参数或存储过程的返回值参数
 /// 
 /// 例如：  
 ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
 /// </remarks>
 /// <param name="transaction">有效的 OracleTransaction</param>
 /// <param name="spName">存储过程名称</param>
 /// <param name="parameterValues"> params object[]</param>
 /// <returns>影响记录数</returns>
 public static int ExecuteNonQuery(OracleTransaction transaction, string spName, params object[] parameterValues)
 {
     if ((parameterValues != null) && (parameterValues.Length > 0))
     {
         OracleParameter[] commandParameters = MsOracleProviderParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
         AssignParameterValues(commandParameters, parameterValues);
         return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
     }
     else
     {
         return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
     }
 }

 /// <summary>
 ///打开(如果必要的话),指定一个命令、连接、事务、参数类型和参数      
 /// </summary>
 /// <param name="command">OracleCommand</param>
 /// <param name="connection">OracleConnection</param>
 /// <param name="transaction">OracleTransaction 或  'null'</param>
 /// <param name="commandType"> CommandType (stored procedure, text, etc.)</param>
 /// <param name="commandText">存储过程名称或SQL语句</param> 
 /// <param name="commandParameters">OracleParameter[]</param>
 private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
 {
     if (connection.State != ConnectionState.Open)
     {
         connection.Open();
     }
     command.Connection = connection;
     command.CommandText = commandText;
     if (transaction != null)
     {
         //command.Transaction = transaction;
         transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
     }
     command.CommandType = commandType;
     if (commandParameters != null)
     {
         AttachParameters(command, commandParameters);
     }
     else
     {
         if (command.Parameters.Count > 0)
             command.Parameters.Clear();
     }
     return;
 }

        public bool CreateTransaction()
        {
            bool ret = true;
            try
            {
                if (_Conn == null) { _Conn = OracleDB.GetConnection(); }

                _Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            catch (Exception ex)
            {
                ret = false;
                _error = ex.Message;
            }
            if (!ret)
            {
                try
                {
                    if (_Conn != null) { _Conn.Close(); }
                    _Conn.Open();
                    _Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted);
                    ret = true;
                }
                catch (Exception ex)
                {
                    ret = false;
                    _error = ex.Message;
                }
            }
            return ret;
        }

		public void TestBeginTransactionChaos() {

			DataBaseServer dbType = ConnectedDataProvider.GetDbType(con);
			// not supported on DB2 and Oracle and Sybase
			if (dbType != DataBaseServer.Oracle && dbType != DataBaseServer.DB2 && dbType != DataBaseServer.Sybase) {
				con.Close();
				con.Open();
				try {
					BeginCase("BeginTransaction - IsolationLevel Chaos");
					tran = con.BeginTransaction(IsolationLevel.Chaos);
					Compare(tran == null, false);
				} 
				catch(Exception ex){exp = ex;}
				finally{EndCase(exp); exp = null;}
			}
			/*	not supported by MSSQL,DB2,Oracle
				con.Close();
				con.Open();
				try
				{
					BeginCase("BeginTransaction - IsolationLevel Unspecified");
					tran = con.BeginTransaction(IsolationLevel.Unspecified );
					Compare(tran == null, false);
				} 
				catch(Exception ex){exp = ex;}
				finally{EndCase(exp); exp = null;}
			*/
			
		}