I am writing an application (using the .NET Framework 4.5.2 + SQL Server 2014 installed locally). The application must support both SQL Server 2014 and previous versions.
When reading data, using embedded type SQLCLR ( SqlGeometry, SqlGeography, SqlHierarchyID) ADO.NET standard methods (e.g. DataReader.GetValues()) use 10.0.0.0 assembly and an exception due to a mismatch with the loaded version (v11 or v12).
The rationale is documented (although it takes some time to search) in "Breaking Changes in SQL Server 2012" (for 11.0.0.0 ). There are three workarounds for SQL Server 2012:
Use Type System Version=SQL Server 2012in SQLConnection.ConnectionString
OR: Use app.config / runtime / assemblyBinding / dependentAssemblyto reprogram v10.0.0.0 to v11.0.0.0
OR (not a very “neat” way to handle this): rewrite your own code to manually deserialize from the instance SqlBytes...
When developing from a computer with SQL Server 2014 installed , the build version is v12.0.0.0 , and similar problems occur:
System.InvalidCastException: Unable to pass a type object Microsoft.SqlServer.Types.SqlGeometryfor input Microsoft.SqlServer.Types.SqlGeometry.
SQL Server 2014 ( ) ( ) - , v4.5 SqlConnection SQL Server:
app.config / runtime / assemblyBinding / dependentAssembly v10.0.0.0 v12.0.0.0
:, v10.0.0.0 v12.0.0.0 app.config ( ), ( ) , ?
( ):
private static void DoStuff()
{
SqlGeography geog_val = SqlGeography.STGeomFromText(new SqlChars("POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))"), 4326);
SqlGeometry geom_val = SqlGeometry.Parse("LINESTRING(1 1,2 3,4 8, -6 3)");
prm_geog.Value = DBNull.Value; prm_geom.Value = geom_val; ReadReturnedSpatialColumns(cmd);
prm_geog.Value = geog_val; prm_geom.Value = DBNull.Value; ReadReturnedSpatialColumns(cmd);
}
private static void ReadReturnedSpatialColumns(SqlCommand cmd)
{
using (var dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
dr.Read(); var items = new object[2]; dr.GetValues(items);
var geog_test = dr.IsDBNull(0) ? SqlGeography.Null : (SqlGeography)items[0];
var geom_test = dr.IsDBNull(1) ? SqlGeometry.Null : (SqlGeometry)items[1];
}
}