Persisting DateTimeOffset with XPO

After my recent update about spatial data in conjunction with XPO, I heard that we also have requests about the DateTimeOffset type that's been available in the .NET Framework for a long time. I decided to create a quick example along the same lines as the spatial data example. Like before, I'm using SQL Server, which has a "datetimeoffset" field type. To persist to other database engines, you would have to find a compatible type and convert values accordingly - feel free to get back to me if you have any specific questions about this!

My full example is available in github here: https://github.com/oliversturm/xpo-sqlserver-datetimeoffset

For some of the details and additional background, I recommend you read my posts about spatial data (here and here), since many of the same considerations apply. For DateTimeOffset, I have introduced this ValueConverter:

public class DateTimeOffsetConverter : ValueConverter {
  public override object ConvertFromStorageType(object value) {
    return value;
  }

  public override object ConvertToStorageType(object value) {
    if (value is DateTimeOffset) {
      var dto = (DateTimeOffset)value;
        return dto.ToString();
      }
      else
        return value;
  }

  public override Type StorageType {
    get { return typeof(string); }
  }
}

In my persistent type, I have implemented the DateTimeOffset property like this:

DateTimeOffset dto;
[ValueConverter(typeof(DateTimeOffsetConverter))]
[DbType("datetimeoffset")]
public DateTimeOffset Dto {
  get { return dto; }
  set { SetPropertyValue("Dto", ref dto, value); }
}

Finally, I'm using a custom provider with the following implementation of ReformatReadValue. Some detailed thoughts about this approach are described in my previous posts. In a nutshell, this code deactivates the default behavior of the standard providers, which assume that read values of non-standard types will be convertible automatically. When dealing with types that are recognized by the underlying .NET database drivers, this behavior results in issues because default conversions are not available. Since an additional conversion is a superfluous extra step anyway, deactivating the mechanism in the provider is a reasonably choice.

protected override object ReformatReadValue(object value, ReformatReadValueArgs args) {
  if (value != null) {
    Type valueType = value.GetType();
    if (valueType == typeof(DateTimeOffset))
      return value;
  }
  return base.ReformatReadValue(value, args);
}

We are considering options to improve the XPO type mapping extensibility, so it is possible that there will be easier approaches available in the future. However, it's always been one of the greatest strengths of XPO that it enables truly database independent coding, and it is a priority for us not to disturb this basic concept. Unfortunately, data types that are not supported across the board of RDBMSs out there are therefore always special cases from the XPO point of view. Please feel free to let us know your thoughts on this!

Free DevExpress Products - Get Your Copy Today