In version 8.4.313.0 we had for
command.Parameters[parameterName].Value equal null if our command execute return -1 value.
In version 8.4.333.0 we have the value which we have befor executing our command with command execute return 0.
Please take a look at this sample:
DDL:
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
using System;
using System.Data;
using System.Transactions;
using Devart.Data.Oracle;
using IsolationLevel = System.Transactions.IsolationLevel;
namespace BulkWithClob
{
internal class Program
{
private static void Main(string[] args)
{
//string connectionString = "connection string";
string connectionString = "User Id=;Password=;Direct=true;Data Source=;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_2", 0);
var arrayLastUpdatedDate = new DateTime?[1];
var lastUpdatedDate = new DateTime();
arrayLastUpdatedDate.SetValue(lastUpdatedDate, 0);
command.CommandText = "update TEST set name = :name where id = :id and LAST_UPDATED_DT = :LastUpdatedDt RETURNING LAST_UPDATED_DT INTO :LastUpdatedDt";
command.PassParametersByName = true;
var idParam = new OracleParameter("id", OracleDbType.Number)
{
Value = array,
Direction = ParameterDirection.InputOutput
};
var genericNameParam = new OracleParameter("name", OracleDbType.VarChar)
{
Value = arrayGenericName,
Direction = ParameterDirection.Input
};
var lastUpdatedDateParam = new OracleParameter("LastUpdatedDt", OracleDbType.Date)
{
Value = arrayLastUpdatedDate,
Direction = ParameterDirection.InputOutput
};
command.Parameters.Add(genericNameParam);
command.Parameters.Add(idParam);
command.Parameters.Add(lastUpdatedDateParam);
var result = command.ExecuteArray(array.Length);
if (result == 0)
{
var newLastUpdateDate = ((Array) command.Parameters["LastUpdatedDt"].Value).GetValue(array.Length-1);
if (newLastUpdateDate != null)
throw new Exception(string.Format("Value of LastUpdatedDt is not null {0} in devart version 8.4.333.0", newLastUpdateDate));
}
if (result == -1)
{
var newLastUpdateDate = ((Array)command.Parameters["LastUpdatedDt"].Value).GetValue(array.Length - 1);
if (newLastUpdateDate == null)
throw new Exception("Value of LastUpdatedDt is default for DateTime type it works in devart version 8.4.313.0");
if ((DateTime)newLastUpdateDate == default(DateTime))
throw new Exception("Value of LastUpdatedDt is default for DateTime type it works in devart versions 8.4.313.0");
}
}
}
transaction.Complete();
}
}
}
}
We prepared example for that and sent it to devart support team.