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 d

Solution 1:

You can try the following binding redirect:

<assemblyBinding><!--....--><dependentAssembly><assemblyIdentityname="Microsoft.SqlServer.Types"publicKeyToken="89845dcd8080cc91" /><bindingRedirectoldVersion="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

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

Solution 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.