I have been using Microsoft.SqlServer.Types.dll in WPF and ASP.NET apps to work with SqlGeometry type and spatial queries for years (since v.10) and here is the latest tips I found to successfully load the SqlServerSpatialXXX.dll as one of the prerequisites of the Microsoft.SqlServer.Types.dll.
SqlGeometry and SqlGeography types can be used in VS projects (e.g. C#) by referencing the Microsoft.SqlServer.Types.dll.Microsoft.SqlServer.Types.dll is a managed library and has some unmanaged library as prerequisites and they are like SqlServerSpatialXXX.dll and msvcrXXX.dll - Since Sql Server 2008, different versions of
Microsoft.SqlServer.Types.dll are available, however, I don't see any functionality change from 2012 on.
Consider 64bit/32bit issues
- For 64 bit machanies, if you install CLR Types for Sql Server, you can find 64bit versions of these prerequisites files in Windows/System32 and also you can find 32bit versions of prerequisites files in Windows/SysWOW64 folder
- If CLR Types are not installed on a machine, You should manually load proper versions (32bit/64bit) of these prerequisites based on your project (32bit or 64bit) otherwise you will errors like
Error Loading SqlServerSpatialXXX.dll
You can check 32bit/64bit issue at runtime in C# using Environment.Is64BitProcess. Here is a sample code:
[DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
private static extern IntPtr LoadLibrary(string libname);
private static void LoadNativeAssembly(string nativeBinaryPath, string assemblyName)
{
var path = Path.Combine(nativeBinaryPath, assemblyName);
if (!File.Exists(path))
{
throw new FileNotFoundException($"{path} not found");
}
var ptr = LoadLibrary(path);
if (ptr == IntPtr.Zero)
{
throw new Exception(string.Format(
"Error loading {0} (ErrorCode: {1})",
assemblyName,
Marshal.GetLastWin32Error()));
}
}
public static void LoadNativeAssembliesv13(string rootApplicationPath)
{
var nativeBinaryPath = Environment.Is64BitProcess
? Path.Combine(rootApplicationPath, @"SqlServerTypes\x64\")
: Path.Combine(rootApplicationPath, @"SqlServerTypes\x86\");
LoadNativeAssembly(nativeBinaryPath, "msvcr120.dll");
LoadNativeAssembly(nativeBinaryPath, "SqlServerSpatial130.dll");
}
Consider binary path in different project types
It is recommended to have a folder named SqlServerTypes in the execution path of your project like this
SqlServerTypes>x64
SqlServerTypes>x32
and load unmanaged assemblies like this
Utilities.LoadNativeAssembliesv13(Environment.CurrentDirectory); //WPF
Utilities.LoadNativeAssembliesv13(HttpRuntime.BinDirectory); //ASP.NET
Issues when using ADO.NET to read SqlGeometry from Sql Server
Despite which version of Microsoft.SqlServer.Types.dll you are using, if you try to read them from Sql Server using ADO.NET you may encounter a cast exception because SQL Client will by default load version 10.0.0.0 of Microsoft.SqlServer.Types.dll. In this case some years ago I tried WKB (approach 1 and 2) and WKT as a medium to convert between SqlGeometry type for different version of Microsoft.SqlServer.Types.dll and found WKB is about 10 times faster but some month ago I found using assembly redirection we can force the program to load the version we are using and using a simple cast we can get the SqlGeometry (approach 3)
private List<SqlGeometry> SelectGeometries(string connectionString)
{
SqlConnection connection = new SqlConnection(connectionString);
var command = new SqlCommand(select shapeCol from MyTable, connection);
connection.Open();
List<SqlGeometry> geometries = new List<SqlGeometry>();
SqlDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
return new List<SqlGeometry>();
}
while (reader.Read())
{
//approach 1: using WKB. 4100-4200 ms for hundred thousands of records
//geometries.Add(SqlGeometry.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes((byte[])reader[0]), srid).MakeValid());
//approach 2: using WKB. 3220 ms for hundred thousands of records
//geometries.Add(SqlGeometry.Deserialize(reader.GetSqlBytes(0)));
//approach 3: exception occur if you forget proper assembly redirection. 2565 ms for hundred thousands of records
geometries.Add((SqlGeometry)reader[0]);
}
connection.Close();
return geometries;
}
