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

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

Postby 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
MassiTheThirdX
 
Posts: 1
Joined: Fri 04 Jul 2014 14:22

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

Postby 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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleDbType.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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleTimeStamp.html
Pinturiccio
Devart Team
 
Posts: 1884
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle