Devart.Data.Oracle.OracleCommand does not support System.DateTimeOffset

[MassiTheThirdX]

Dear Sirs and Madams

We have some problems to handle data writing into columns of Oracle type TIMESTAMP WITH TIME ZONE with the OracleCommand method ExecuteNonQuery(). Inserting Values of dotNet type DateTimeOffset into the column of type TIMESTAMP WITH TIME ZONE causes the exception "Value with type System.DateTimeOffset not supported".

we are using dotconnect for oracle version 8.4.191

Tabledescription:

Code: Select all

create table EMAILMESSAGE
(
  id                NUMBER(18) not null,
  emailaddress      VARCHAR2(100) not null,
  text              VARCHAR2(2000) not null,
  statusid          NUMBER(9) not null,
  lastedittimestamp TIMESTAMP(6) WITH TIME ZONE 
)

The inserting code:

Code: Select all

            using (Devart.Data.Oracle.OracleConnection activeConnection = new Devart.Data.Oracle.OracleConnection(connectionString))
            {
                activeConnection.Open();

                string commandText = @"insert into EMAILMESSAGE (emailaddress, text, statusid, LASTEDITTIMESTAMP) 
                                        values (:1, :2, :3, :4)";

                using (Devart.Data.Oracle.OracleCommand command = activeConnection.CreateCommand(commandText))
                {
                    command.Parameters.Add(new OracleParameter("1", "email"));
                    command.Parameters.Add(new OracleParameter("2", "text"));
                    command.Parameters.Add(new OracleParameter("3", 0));
                    command.Parameters.Add(new OracleParameter("4", new DateTimeOffset(DateTime.Now)));

                    int affectedRecords = command.ExecuteNonQuery();

                    Assert.IsTrue(affectedRecords == 1);
                }
            }

The code crashes at command.ExecuteNonQuery().

We have the same problem with function command.ExecuteArray().

Is this a known bug? Is there any workaround?

Looking forward to hear from you.

Best regards

M. Sotoodeh

[Pinturiccio]

You assign a DateTimeOffset value to the parameter. If you don't assign a value to the OracleDbType property, dotConnect for Oracle assigns it itself based on the Value type. However, there is no OracleDbType, corresponding to the DateTimeOffset type, and this causes the exception. In order to pass a TIMESTAMP WITH TIME ZONE value, you can use an OracleTimeStamp or DateTime value. For more information, please refer to http://www.devart.com/dotconnect/oracle ... bType.html

We also provide an example that demonstrates how to execute your code correctly.

Code: Select all

using (OracleConnection activeConnection = new OracleConnection(connectionString))
{
    activeConnection.Open();

    string commandText = @"insert into EMAILMESSAGE (id, emailaddress, text, statusid, LASTEDITTIMESTAMP)
                                values (:0, :1, :2, :3, :4)";

    using (Devart.Data.Oracle.OracleCommand command = activeConnection.CreateCommand(commandText))
    {
        command.Parameters.Add(new OracleParameter("0", 1));
        command.Parameters.Add(new OracleParameter("1", "email"));
        command.Parameters.Add(new OracleParameter("2", "text"));
        command.Parameters.Add(new OracleParameter("3", 0));
        command.Parameters.Add(new OracleParameter("4", OracleDbType.TimeStampTZ));
        command.Parameters["4"].Value = DateTime.Now;
        //or
        //command.Parameters["4"].Value = new OracleTimeStamp(DateTime.Now);

        int affectedRecords = command.ExecuteNonQuery();

        Assert.IsTrue(affectedRecords == 1);
    }
}

For more information, please refer to http://www.devart.com/dotconnect/oracle ... Stamp.html