Devart Forums

Code: Select all

 string connectionString = "User Id=;Password=;Direct=true;Data Source=;Port=1521;SID=;Pooling=true;Unicode=true";
            using (var transaction = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}))
            {
               using (OracleConnection connection = new OracleConnection(connectionString))
               {
                  connection.Open();
                  using (var command = connection.CreateCommand())
                  {
                     var array = new decimal?[1];
                     array.SetValue((decimal?) 2, 0);
                     var arrayGenericName = new String[1];
                     arrayGenericName.SetValue("TEST DATA", 0);
                     var arrayLastUpdatedDate = new DateTime[1];
                     command.CommandText = "update  GMSRCA200.TEST set name = :name where id = :id RETURNING  LAST_UPDATED_DT INTO  :LastUpdatedDt";
                     command.PassParametersByName = true;
                     var idParam = new OracleParameter("id", OracleDbType.Number)
                     {
                        Value = array,
                        Direction = ParameterDirection.Input
                     };
                     var genericNameParam = new OracleParameter("name", OracleDbType.VarChar)
                     {
                        Value = arrayGenericName,
                        Direction = ParameterDirection.Input
                     };
                     var lastUpdatedDateParam = new OracleParameter("LastUpdatedDt", OracleDbType.Date)
                     {
                        Value = arrayLastUpdatedDate,
                        Direction = ParameterDirection.Output
                     };
                     command.Parameters.Add(genericNameParam);
                     command.Parameters.Add(idParam);
                     command.Parameters.Add(lastUpdatedDateParam);

                     command.ExecuteArray(array.Length);
                  }
               }
               transaction.Complete();
            }        
} 

Code: Select all

Select * from v$version

Code: Select all

CREATE TABLE TEST
(
  ID                   NUMBER(19)               NOT NULL,  
  NAME                 VARCHAR2(1000 CHAR)      NOT NULL,
  LAST_UPDATED_DT      DATE                     NOT NULL  
);


CREATE UNIQUE INDEX AK_TEST_NAME ON TEST
(NAME);

CREATE OR REPLACE TRIGGER BIU_TEST_DT
    BEFORE INSERT OR UPDATE 
    ON TEST     REFERENCING NEW AS New OLD AS Old
    for each row
begin
      :new.last_updated_dt := sys_extract_utc(SYSTIMESTAMP);
end;

INSERT INTO TEST(ID,NAME)
VALUES(1,'TEST DATA');

INSERT INTO TEST(ID,NAME)
VALUES(2,'TEST DATA2');

COMMIT;

Code: Select all

      private static void Main(string[] args)
      {         
            string connectionString = "User Id=<...>;Password=<...>;Direct=true;Data Source=<...>;Port=1521;SID=<...>;Pooling=true;Unicode=true";
            using (var transaction = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}))
            {
               using (OracleConnection connection = new OracleConnection(connectionString))
               {
                  connection.Open();
                  using (var command = connection.CreateCommand())
                  {
                     var array = new decimal?[1];
                     array.SetValue((decimal?) 2, 0);
                     var arrayGenericName = new String[1];
                     arrayGenericName.SetValue("TEST DATA", 0);
                     var arrayLastUpdatedDate = new DateTime[1];
                     command.CommandText = "update  TEST set name = :name where id = :id RETURNING  LAST_UPDATED_DT INTO  :LastUpdatedDt";
                     command.PassParametersByName = true;
                     var idParam = new OracleParameter("id", OracleDbType.Number)
                     {
                        Value = array,
                        Direction = ParameterDirection.Input
                     };
                     var genericNameParam = new OracleParameter("name", OracleDbType.VarChar)
                     {
                        Value = arrayGenericName,
                        Direction = ParameterDirection.Input
                     };
                     var lastUpdatedDateParam = new OracleParameter("LastUpdatedDt", OracleDbType.Date)
                     {
                        Value = arrayLastUpdatedDate,
                        Direction = ParameterDirection.Output
                     };
                     command.Parameters.Add(genericNameParam);
                     command.Parameters.Add(idParam);
                     command.Parameters.Add(lastUpdatedDateParam);

                     command.ExecuteArray(array.Length);
                  }
               }
               transaction.Complete();
            }