Dealing with spatial datatypes in SQL Server 2012 tables

Question

Some background:

I'm running two different installations of SQL Server on two different VMs. I'm attempting to connect to them from my code (which was written for SQL Server 2008 R2). My current .NET framework version is 4.0. My Microsoft SQL Server 2008 R2 installation is running the AdventureWorks designed for it, and my SQL Server 2012 Express installation is running the newer AdventureWorks2012 database. I'm querying Person.Address.

Currently I'm running into an exception saying "DataType cannot be null". I've deduced from looking at the returned schemas that the problem comes from SQL Server 2012 returning a

Microsoft.SqlServer.Types.SqlGeography

datatype. It seems that Type.GetType cannot convert this to something usable and throws an exception. I'm relieved that it's nothing that I've done, but I'm also puzzled because this code should work universally. Keep in mind this code runs flawlessly on SQL Server 2008 R2 (because it lacks the spatial datatype).

I could think of 100 bad ways to handle this. However I wanted to come to you guys for suggestions on what to do. Will upgrading .NET to 4.5 remedy the situation? Do I have to change my code at all to accommodate the new datatype? Should I worry about the spatial datatypes if my application doesn't deal with them? How do I handle this, because obviously SqlClient and Type.GetType don't do a good job at it.

Thank you!

Assuming that you're using LINQ to SQL, this is a possible duplicate of [Is it possible to use SqlGeography with Linq to Sql?](http://stackoverflow.com/questions/2845767/is-it-possible-to-use-sqlgeography-with-linq-to-sql) — Pondlife, Oct 31 '12 at 13:13
@Pondlife - the error messages don't match. Also, the proposed duplicate is dealing with a very specific case, converting geography to varbinary. I don't see that happening here. — JDB, Oct 31 '12 at 13:49
That's why it's a "possible" duplicate:) But on the other hand the basic issue seems to be that your .NET 4.0 code does not support SqlGeography, which is also the basic issue described in the other question — Pondlife, Oct 31 '12 at 13:59

score 0 · Answer 1 · answered Feb 21 '13 at 16:06

If you're working with 2012 and 2008 R2 I suggest you force use of the SQL 2012 SqlGeography type.

You might consider adding assembly redirect, so any .NET code trying to load 2008 R2 SqlGeograph is treated as the SQL 2012 SqlGeography. SQL libraries are usually backward compatible when being serialized (whenever possible, i.e. when SQL 2012 specific features aren't being used, like CurvePolygon).

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

Dealing with spatial datatypes in SQL Server 2012 tables

1 Answers1