Tricky stuff with Sql Server Spatial part 2

So you have this Sql Server 2012 with spatial datatypes and you want to read it with C#.

The first thing to do is to add a reference in your project to Microsoft.SqlServer.Types where SqlGeography and the likes reside.

The next step would be to make a query and read the results using a SqlDataReader. Something like this:

                result.Location  = (SqlGeography)reader["Location"];

The problem is that this will result in a System.InvalidCastException with the message:

{"[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'."}

If you read the message you actually can understand what's going on. Even though you put a specific reference to the types in Sql Server 2012, it still seems as if C# is trying to read with the old version of Sql Server (2008).

The easiest way to fix this is through the connection string and the Type System Version construct that appeared in .Net 4.5.

My first attempt on a connection string was like this (and yeah, my computer is named GAAH):


    <add name="Main" connectionString="data source=GAAH\SQLEXPRESS;initial catalog=SpatialSample;integrated security=True" />

By adding Type System Version we can tell it that we will use the 2012 version of Sql Server Types.

    <add name="Main" connectionString="Type System Version=SQL Server 2012;data source=GAAH\SQLEXPRESS;initial catalog=SpatialSample;integrated security=True" />

Now youre just a read away from your spatial objects.