Home
Softono
Microsoft.SqlServer.Types

Microsoft.SqlServer.Types

Open source Apache-2.0 C#
GitHub
76
Stars
33
Forks
14
Issues
4
Watchers
2 years
Last Commit
Relational Databases Geospatial & GIS

About Microsoft.SqlServer.Types

dotMorten.Microsoft.SqlServer.Types is a .NET Standard library that provides an implementation of the spatial types found in Microsoft's Microsoft.SqlServer.Types, specifically SqlGeometry and SqlGeography. It enables cross-platform support for these CLR types, which were traditionally Windows-only, allowing .NET applications on Linux, macOS, and other platforms to serialize and deserialize spatial data from SQL Server without requiring the native SQL Server assemblies. The library supports assigning SqlGeometry and SqlGeography objects as parameters to SQL commands and reading them from query results. It offers two version tracks: v1.x depends on the legacy System.Data.SqlClient package, while v2.x depends on the modern Microsoft.Data.SqlClient package. Note that spatial operations such as intersection and area calculations are not included; users are expected to perform these operations within their SQL queries. The project is being gradually sunset as the official Microsoft.SqlServer.Types NuGet package ad

Platforms

Web Self-hosted Windows

Languages

C#

Links

Source Code
d
Published by
dotmorten
Visit View Profile

README.md

View on GitHub

Notice

This project will slowly start to sunset, as the official Microsoft.SqlServer.Types NuGet package is starting to add .NET 6 support, and bring cross-platform support online. While it isn't quite there just yet, it does mean that efforts in this library will start to diminish, now that we are finally getting the support the types library always needed, and the need for this custom implementation is getting less and less important.

Microsoft.SqlServer.Types

a .NET Standard implementation of the spatial types in Microsoft.SqlServer.Types

Difference between v1.x and v2.x

The v1.x and v2.x versions are identical and kept in sync. The only difference is the dependency:

  • v1.x : Depends on the legacy System.Data.SqlClient package.
  • v2.x : Depends on updated Microsoft.Data.SqlClient package.

Sponsoring

If you like this library and use it a lot, consider sponsoring me. Anything helps and encourages me to keep going.

See here for details: https://github.com/sponsors/dotMorten

NuGet:

Install the package dotMorten.Microsoft.SqlServer.Types from NuGet.

Examples

Input parameter

Assigning SqlGeometry or SqlGeography to a command parameter:

   command.Parameters.AddWithValue("@GeographyColumn", mySqlGeography);
   command.Parameters["@GeometryColumn"].UdtTypeName = "Geography";

   command.Parameters.AddWithValue("@GeographyColumn", mySqlGeometry);
   command.Parameters["@GeometryColumn"].UdtTypeName = "Geometry"

The geometry will automatically be correctly serialized.

Reading geometry and geography

Use the common methods for getting fields of specific types:

   var geom1 = reader.GetValue(geomColumn) as SqlGeometry;
   var geom2 = reader.GetFieldValue<SqlGeometry>(geomColumn);
   var geom3 = SqlGeometry.Deserialize(reader.GetSqlBytes(geomColumn)); //Avoids any potential assembly-redirect issue. See https://docs.microsoft.com/en-us/previous-versions/sql/2014/sql-server/install/warning-about-client-side-usage-of-geometry-geography-and-hierarchyid?view=sql-server-2014#corrective-action

Notes:

The spatial operations like intersection, area etc are not included here. You can perform these as part of your query instead and get them returned in a column.