How to read time offsets from oracle using Dapper

Question

I have data stored in a TIMESTAMP WITH TIME ZONE oracle column, and now I'm trying to read it back to the C # DateTimeOffset variable using Dapper. The problem is that dapper ignores the offset value in the database and always fills my variable with the current environment offset.

Is there an easy way to get dapper to recognize the offset value from the database?

Basically, I want something in these lines to work:

var input=new DateTimeOffset(2016, 3, 15, 14, 30, 0, TimeSpan.Zero); DateTimeOffset output; using(var connection=new OracleConnection(QueryConnectionString)) { output=connection.ExecuteScalar<DateTimeOffset>("Select to_timestamp_tz('"+input.ToString("yyyy-MM-dd HH:mm zzz")+"', 'YYYY-MM-DD HH24:MI TZH:TZM') From DUAL"); } Assert.AreEqual(input, output); 

As written, this gives an invalid lit exception, it seems like dapper reads it as a DateTime, and then tries to pass it to DateTimeOffset, ignoring the offset value.

My code, which queries the table and populates the class object defined by these types, does not throw an error, but fills the instance of the object with a local offset, not a value in the database. So instead of 2016-03-15 14:30 +0 I would 2016-03-15 14:30 -5 into 2016-03-15 14:30 -5 if I were working with the input value above.

Answer 1

So, I noticed that some other people are looking at this question, and as long as I don't have a direct answer, I thought I would share what we used ...

Disclaimer: this is a bit of shreds, and I would prefer a cleaner approach, but it works.

In fact, what we have finished is converting the Timestamp With Timezone column to a formatted string in a select statement. In the C # class, we added a property that was a string representation of DateTimeOffset using the same format. Then, we used aliases in the query to ensure that Dapper entered the value into the row property, whose configuration tool selects it back into DateTimeOffset and sets the original property.