/// <summary>  
        /// 执行数据库事务查询操作,返回受影响的行数  
        /// </summary>  
        /// <param name="transaction">数据库事务对象</param>  
        /// <param name="cmdType">Command类型</param>  
        /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
        /// <param name="commandParameters">命令参数集合</param>  
        /// <returns>当前事务查询操作影响的数据行数</returns>  
        public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand command = new OracleCommand();
            OracleConnection connection = transaction.Connection;
            int result = 0;

            try
            {
                PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch
            {
                throw;
            }
            finally
            {
                transaction.Dispose();
                command.Dispose();
                connection.Close();
                connection.Dispose();
            }

            return result;
        }

 /// <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 OracleCustomCommandBuilder( DataTable dataTable, OracleConnection connection, ArrayList columnOracleTypes, OracleTransaction transaction)
		{
			if (dataTable.Columns.Count != columnOracleTypes.Count)
			{
				throw new ArgumentException("Number of OracleTypes does not match number of columns.");
			}
			this.dataTable = dataTable;
			this.connection = connection;
			this.columnOracleTypes = columnOracleTypes;
			this.transaction = transaction;
		}

 /// <summary>
 /// Create and execute a command to return a single-row DataReader after binding to multiple parameters.
 /// </summary>
 /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
 /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
 /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
 /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
 /// <param name="cmdParms">An array of OracleParameter objects to bind to the query.</param>
 public static OracleDataReader ExecuteReaderSingleRow(OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
 {
     if (conn.State != ConnectionState.Open)
         conn.Open();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conn;
     cmd.CommandText = cmdText;
     PrepareCommand(cmd, cmdParms);
     OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
     return rdr;
 }

 /// <summary>
 /// Create and execute a command to return DataReader after binding to a single parameter.
 /// </summary>
 /// <param name="conn">Connection to execute against. If not open, it will be here.</param>
 /// <param name="trans">ADO transaction.  If null, will not be attached to the command</param>
 /// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
 /// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
 /// <param name="singleParm">The single OracleParameter object to bind to the query.</param>
 public static OracleDataReader ExecuteReaderSingleParm(OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter singleParm)
 {
     OracleCommand cmd = new OracleCommand();
     if (conn.State != ConnectionState.Open)
         conn.Open();
     cmd.Connection = conn;
     cmd.CommandText = cmdText;
     cmd.Parameters.Add(singleParm);
     OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
     return rdr;
 }

        public static void GrabarMatriculaUpdate(ref MatriculaDto mat, ref OracleConnection con, ref OracleTransaction tran)
        {
            var cmdPro = new OracleCommand("PRC_MATRICULA_UPDATE");
             cmdPro.CommandType = CommandType.StoredProcedure;
             cmdPro.Connection = con;

             cmdPro.Parameters.Add(CreateParameter("iMTRID", mat.MtrId));//NUMBER
             cmdPro.Parameters.Add(CreateParameter("iMTRDESCRIPCION", mat.MtrDescripcion, 45));//NUMBER

             cmdPro.Transaction = tran;
             cmdPro.ExecuteNonQuery();//EJECUTO CONSULTA
        }

		public OracleDatabaseQuery(
			OracleConnection connection,
			OracleTransaction transaction)
		{
			Contract.Requires(connection != null);

			this.Connection = connection;
			this.ConnectionString = connection.ConnectionString;
			this.Transaction = transaction;
			Queue =
				new Lazy<OracleAQQueue>(() =>
				{
					var q = new OracleAQQueue("\"-NGS-\".NOTIFY_QUEUE", Connection, OracleAQMessageType.Udt, "-NGS-.NOTIFY_INFO_TYPE");
					q.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
					q.EnqueueOptions.DeliveryMode = OracleAQMessageDeliveryMode.Persistent;
					return q;
				});
		}

        public static void GrabarMatriculaInsert(ref MatriculaDto mat, ref OracleConnection con, ref OracleTransaction tran)
        {
            #region INSERT Matricula del profesional  -------------------------------------------------------------------

            var cmdMat = new OracleCommand("PRC_MATRICULA_INSERT");
            cmdMat.CommandType = CommandType.StoredProcedure;
            cmdMat.Connection = con;

            cmdMat.Parameters.Add(CreateParameter("iMTRDESCRIPCION", mat.MtrDescripcion, 45));//VARCHAR
            cmdMat.Parameters.Add(CreateParameter("iMTR_MTTID", mat.MtrTipo.MttId));//NUMBER
            cmdMat.Parameters.Add(CrearParametroSalida("oMTRID", OracleDbType.Int32));//NUMBER

            cmdMat.Transaction = tran;
            cmdMat.ExecuteNonQuery();

            var varMtrId = cmdMat.Parameters["oMTRID"].Value;
            mat.MtrId = Convert.ToInt16(varMtrId.ToString());

            #endregion
        }

           /// <summary>
           /// 为command添加执行内容
           /// </summary>
           /// <param name="cmd">OracleCommand类型</param>
           /// <param name="conn">OracleConnection类型</param>
           /// <param name="trans">OracleTransaction要处理的事物</param>
           /// <param name="cmdType">执行类型（过程/语句）</param>
           /// <param name="cmdText">执行的过程名称/查询语句</param>
           /// <param name="cmdParms">ommand执行的OracleParameter[]数组</param>
           private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
           {
               if (conn.State != ConnectionState.Open)
                   Open(conn);

               cmd.Connection = conn;
               cmd.CommandText = cmdText;

               if (trans != null)
                   cmd.Transaction = trans;

               cmd.CommandType = cmdType;

               if (cmdParms != null)
               {
                   if (cmdParms.Length > 0)
                   {
                       foreach (OracleParameter parm in cmdParms)
                           cmd.Parameters.Add(parm);
                   }
               }
           }

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

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

		/// <summary>
		/// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified
		/// OracleTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
		/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
		/// </summary>
		/// <remarks>
		/// This method provides no access to output parameters or the stored procedure's return value parameter.
		/// 
		/// e.g.:  
		///  OracleDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
		/// </remarks>
		/// <param name="transaction">a valid OracleTransaction</param>
		/// <param name="spName">the name of the stored procedure</param>
		/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
		/// <returns>an OracleDataReader containing the resultset generated by the command</returns>
		public static OracleDataReader ExecuteReader(OracleTransaction transaction, string spName, params object[] parameterValues)
		{
			//if we got parameter values, we need to figure out where they go
			if ((parameterValues != null) && (parameterValues.Length > 0)) 
			{
				OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

				AssignParameterValues(commandParameters, parameterValues);

				return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
			}
				//otherwise we can just call the SP without params
			else 
			{
				return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
			}
		}

		/// <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">a valid OracleTransaction</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 used to execute the command</param>
		/// <returns>a dataset containing the resultset generated by the command</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 = new OracleDataAdapter(cmd);
			DataSet ds = new DataSet();

			//fill the DataSet using default values for DataTable names, etc.
			da.Fill(ds);
			
			//return the dataset
			return ds;
		}

		/// <summary>
		/// Execute an OracleCommand (that returns no resultset) against the specified OracleTransaction
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
		/// </remarks>
		/// <param name="transaction">a valid OracleTransaction</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 used to execute the command</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(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);
			
			//finally, execute the command.
			return cmd.ExecuteNonQuery();
		}

 public static OracleConnection GetConnection()
 {
     if (conn != null) {
         if (conn.State == System.Data.ConnectionState.Open) {
             return conn;
         }
     }
     string DBName = "RA00C000";
     string DBUser = "voshod";
     string DBPass = "voshod";
     string[] args = Environment.GetCommandLineArgs();
     if (args.Length > 3) {
         DBName = args[1];
         DBUser = args[2];
         DBPass = args[3];
     }
     oradb = "Data Source=" + DBName + ";User Id=" + DBUser + ";Password=" + DBPass + ";";
     try {
         conn = new OracleConnection(oradb);
         conn.Open();
         transaction = conn.BeginTransaction();
         return conn;
     } catch (Exception) {
         return null;
     }
 }

 public static void Commit()
 {
     if (conn != null) {
         transaction.Commit();
         transaction = conn.BeginTransaction();
     }
 }

        public CT2Repository()
        {
            this.con = ConnectionFactory.GetConnection();

            if (this.con.State != ConnectionState.Open)
                this.con.Open();
            if(usaTransacao)
                this._transaction = this.con.BeginTransaction();
        }

 /// <summary>
 /// Connects to the database.
 /// </summary>
 /// <param name="connDetails"></param>
 public SqlRunner(ConnectionDetails connDetails)
 {
     this.connDetails = connDetails;
     string connStr = GetConnectionString();
     log.Info("connecting to [" + connDetails.Name + "] with connection string " + connStr);
     conn = new OracleConnection(connStr);
     conn.Open();
     currentTransaction = conn.BeginTransaction();
 }

        public DataContextTransaction BeginTransaction()
        {
            if (Connection.State == ConnectionState.Closed)
                Connection.Open();

            if (Transaction == null)
                Transaction = Connection.BeginTransaction();

            return this;
        }

        public void Dispose()
        {
            if (Transaction != null)
            {
                Transaction.Dispose();
                Transaction = null;
            }

            if (Connection.State != ConnectionState.Closed)
                Connection.Close();
        }

 public static void Rollback()
 {
     if(transaction != null) {
         try {
             transaction.Rollback();
             transaction = conn.BeginTransaction();
         } catch (Exception) {
             // Подавляем исключения.
         }
     }
 }