C# OracleClient.OracleTransaction类代码示例

        /// <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;

        }

        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;
        }