One of the identified items was in an invalid format - Microsoft.SqlServer.Type

Question

I'm trying to retrieve geography data from my SQl Server 2012 DB with:

new SqlDataAdapter("SELECT [SpatialColumn] FROM [SpatialTable]", myConnection).Fill(myDatatable);

When the data is a Sql Server 2008 geography type such as Polygon, it's all working fine. But when the type is the new CurvePolygon then that line of code crashes with the error:

System.FormatException occurred
Message="One of the identified items was in an invalid format."
Source="Microsoft.SqlServer.Types"
StackTrace: at Microsoft.SqlServer.Types.GeoData.Read(BinaryReader r)

In this MSDN article, in the section called SQL CLR Data Types I have read that when you reference the SqlTypes assembly version 11.0, and version 10.0 is also installed, you may see a similar error. So I changed my Config file as explained. But it hasn't solved my issue.

Any idea greatly appreciated!

Answer 1

You can try the following binding redirect:

 <assemblyBinding>   
      <!--....-->
     <dependentAssembly>
            <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" />
            <bindingRedirect oldVersion="10.0.0.0-11.0.0.0" newVersion="11.0.0.0" />
          </dependentAssembly>
        </assemblyBinding>

It looks like there is a problem with the loading assembly. More here: https://blog.devart.com/adventures-of-clr-types-in-net-framework.html

Answer 2

I had the same issue. It worked fine in SQL-Server 2012 but not in SQL-Server 2008. I had to consume my Geography data, using SqlDataReader.GetSqlBytes and then deserialize.

var geo = SqlGeography.Deserialize(dr.GetSqlBytes(0))

Answer 3

If you don't need the SQLGEOGRAPHY object try Converting the SQLType to Text in the DB.

new SqlDataAdapter("SELECT SpatialColumn.STAsText() FROM [SpatialTable]", myConnection).Fill(myDatatable);

Now your data should come through in the format;

"POLYGON((-123.22,102.32...."

or

"CURVEPOLYGON((-123.22,102.32...."

As either Type can be expressed as Text which will successfully fill the column in your DataTable.