Oliver's Blog

January 2017 - Posts

  • 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!





  • XPO, SQL Server and spatial data revisited

    All the way back in 2008, I wrote a blog post showing how spatial data can be persisted using XPO in conjunction with the built-in data types SqlGeography and SqlGeometry in SQL Server. You can find my original post by following this link.

    Recently our support guys brought a request to my attention, where it was pointed out that the mechanism I demonstrated in my old blog post did not correctly take the SRID value into account that is associated with the SQL Server types. I'm not an expert in GIS, but I understand that this ID specifies the type or scope of the information. This obviously seems important, but curiously the standard text or XML formats supported for (readable) serialization of SqlGeography or SqlGeometry data don't contain or even support this field. Because my implemented mechanism relied on the WKT (well-known text - creative naming!) format, the SRID was lost.

    I looked around a bit for a mechanism I could use instead. There is a SQL API to fetch the SRID associated with a column, where the syntax makes the SRID appear like a sub-column:

    select MYCOLUMN.STSrid from MYTABLE

    However, this  is not an approach easily usable from XPO because it would require running an extra query (or alternatively extend the query run by XPO to include the separate field). When data is selected from the column itself, it does not contain the SRID value, but only the string representations I had previously used.

    In the end it turned out that I had already summarized the solution in my previous article. Towards the end, I was elaborating on a performance issue I found back then, because it seemed sub-optimal to be converting the values into and out of string format several times. I also said I wouldn't recommend that approach because it neglected certain contracts assumed by XPO. I stand by these comments, but at the same time this approach seems to be the only way of getting hold of the SRID without making changes to the existing query. Compared to my old code, I made the following changes.

    First, to persist the SRID together with the spatial data, I changed the ConvertToStorageType method to this:

    public override object ConvertToStorageType(object value) {
      if (value == null) return null;
    
      var sqlGeography = value as SqlGeography;
      if (sqlGeography != null)
        return sqlGeography.Serialize();
      else {
        var sqlGeometry = value as SqlGeometry;
        if (sqlGeometry != null)
          return sqlGeometry.Serialize();
        else
          return value;
      }
    }
    


    Instead of using a string representation, I'm simply using the standard serialization format supported by the types. The Serialize helper produces a SqlBytes object, and the data includes the SRID. 

    Second, I changed the ConvertFromStorageType method to not do any actual conversion. Technically this is unnecessary because the SqlClient that loads the data on some low level, automatically constructs instances of SqlGeography or SqlGeometry. This is the topic that was already described in my previous post, and I'm now ignoring the conversion contract by skipping this step.

    public override object ConvertFromStorageType(object value) {
      return value;
    }
    


    Finally, I changed the ReformatReadValue method implementation to also skip any conversion, simply returning the original value when I find the type to be one of the supported ones. This has the effect of deactivating the base class behavior, which would otherwise result in runtime exceptions because there is no automatic conversion method.

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


    While I still subscribe to the idealistic view that contracts shouldn't be ignored (in other words, methods should not be overridden or implemented to do something different than they are supposed to), I see these changes as a reasonable approach under the circumstances. Any other technique would be much more complicated to implement, and the past nine years have shown that the APIs have remained stable and unchanged. I hope this mechanism will do the job for those of you who work with this combination of XPO and SQL Server.

    You can find the complete sample in a github repository here: xpo-sqlserver-spatial

    The sample has been updated to work with our latest release, and to utilize Microsoft's nuget package for the supporting Microsoft.SqlServer.Types assembly. In case you'd like to compare, you can also follow this link to see the sample from the original blog post, updated for versions but otherwise unchanged.





LIVE CHAT

Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

FOLLOW US

DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2017 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners