CODEWITHSUNDEEP
c#sql-serversql-server-2012geographysqlgeography
Johann
Johann

Reputation: 12398

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

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!

Upvotes: 2

Views: 14933

Answers (3)

Razvan Dumitru
Razvan Dumitru

Reputation: 12452

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

Upvotes: 4

Daniel
Daniel

Reputation: 8368

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))

Upvotes: 1

Totero
Totero

Reputation: 2574

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.

Upvotes: 0

Related Questions