How to resolve an InvalidCastException when retrieving a SqlGeography type from a DataReader?

3
votes

I'm developing in C# using Visual Studio 2013 and SQL Server 2012. I've been able to store polygons in geography columns using T-SQL and am not trying to use the SqlGeography class in code to retrieve the data.

When I try:

SqlGeography polyB = (SqlGeography)dr["extent"]; // stored in OGC Well Known Binary format

to retrieve a polygon from the database I am receiving the message:

An unhandled exception of type 'System.InvalidCastException' occurred in GeoLib.dll

Additional information: [A]Microsoft.SqlServer.Types.SqlGeography cannot be cast to [B]Microsoft.SqlServer.Types.SqlGeography. Type A originates from 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'.

Type B originates from 'Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll'.

I suspect a version incompatibility between SQL Server and Visual Studio as indicated by the differing version numbers. Has anyone ever run into this? Maybe I need SQL Server 2014 installed? Any ideas are appreciated!

2
c#sql-serversql-server-2012sqlgeography
What version of Microsoft.SqlServer.Types do you have a reference to in your project? – DaveShaw
The project is using version 11 - I'll try to find 10 and use it. – Hank Brandenburg
Dave - that was it! I was missing the "forest for the trees". Thanks! – Hank Brandenburg
Why not post that as an answer and answer your own question for anyone else who might find themselves with a similar issue. – DaveShaw
Done - thanks again Dave. – Hank Brandenburg

2 Answers

6
votes

Whilst your own answer clearly sorts out your problem (which is great), by doing what you have, you have forced yourself into not having access to some of the methods available to Geometry and Geography only in SQL 2012 onwards.

The following link explains some better approaches to solving this problem, see Breaking Changes to Database Engine Features in SQL Server 2012

You'll find the relevant information about one-third down the page, sub-headed "SQL CLR Data Types (geometry, geography, and hierarchyid)". My chosen method was the redirect the assembly in app.config - but you can use one of the others if you prefer / need.

Note that the same solution is required for SQL Server 2014, but is not yet compatible with specifying version 12 of the Microsoft.SqlServer.Types assembly, nor can you specify "Sql Server 2014" as the Type System Version parameter of your connection string - use 2012.

Correct as of time of writing.

Apologies for the late answer, hope it helps.

1
votes

Turns out I was using version 11 (came with Visual Studio 2013 I suspect) instead of version 10 which SQL Server 2012 must be using. When I changed the project reference to the version 10 .dll everything worked fine.