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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
MassiTheThirdX
Posts: 1
Joined: Fri 04 Jul 2014 14:22

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

Post by MassiTheThirdX » Fri 04 Jul 2014 14:50

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Mon 07 Jul 2014 11:56

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

Post Reply