search

dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
844 stars 280 forks source link

Implement SqlGeography and SqlGeometry for Spatial Types support for .Net Core #30

Closed ststeiger closed 1 year ago

ststeiger commented 6 years ago

Cannot run Microsoft.SqlServer.Types because \Microsoft.SqlServer.Server\IBinarySerialize.cs is missing in System.Data.

Since no source code is available, and contact owners on nuget yields a HTTP-500, I'm opening an issue here. I'd like to compute a polygon union...

#if false
namespace AnySqlWebAdmin
{
    public class GeoPoint
    {
        public decimal Lat;
        public decimal Long;
        public GeoPoint(decimal latitude, decimal longitude)
        {
            this.Lat = latitude;
            this.Long = longitude;
        }
        public GeoPoint() : this(0,0) { }
        public override string ToString()
        {
            return this.Lat.ToString(System.Globalization.CultureInfo.InvariantCulture)
                + " "
                + this.Long.ToString(System.Globalization.CultureInfo.InvariantCulture);
        }
    }
    public class GeographicOperations 
    {
        public static string ObjJoin(string separator, params object[] objs)
        {
            string result = null;
            System.Text.StringBuilder sb = new System.Text.StringBuilder();

            bool isNotFirst = false;

            for (int i = 0; i < objs.Length; ++i)
            {
                if (objs[i] == null)
                    continue;
                if (separator != null && isNotFirst)
                {
                    sb.Append(separator);
                }
                else
                    isNotFirst = true;

                if(objs[i] != null)
                    sb.Append(objs[i].ToString());
            }

            result = sb.ToString();
            sb.Clear();
            sb = null;

            return result;
        }
        // geography::STPolyFromText('POLYGON((9.3763619 47.4330074,9.3764389 47.4329684,9.3764072 47.4329405,9.3763969 47.4329322,9.3759864 47.4326004,9.376056 47.4325644,9.3761349 47.4325167,9.37619 47.4325653,9.376312 47.4326732,9.3765907 47.4328683,9.3766389 47.4328521,9.3767794 47.4329452,9.3764748 47.4331106,9.3763619 47.4330074))', 4326)
        // geography::STPolyFromText('POLYGON((9.3766833 47.4319973,9.3772045 47.4324131,9.3771257 47.432459,9.3769959 47.4323535,9.3767225 47.4325076,9.3768938 47.432637,9.3769843 47.4325975,9.3772713 47.432826,9.3771862 47.4328789,9.376941 47.4326789,9.3767283 47.4327757,9.3765053 47.4325749,9.376312 47.4326732,9.37619 47.4325653,9.3761349 47.4325167,9.376056 47.4325644,9.3757946 47.43237,9.3760399 47.4322419,9.376144 47.4323272,9.3761809 47.4323125,9.3762975 47.432428,9.3762371 47.4324602,9.3763095 47.4325246,9.3764699 47.4324328,9.3763633 47.4323437,9.3763976 47.4323193,9.3763247 47.4322628,9.3763972 47.4322251,9.3764363 47.4322061,9.3766528 47.4323718,9.3768611 47.4322514,9.3765976 47.4320409,9.3766833 47.4319973))', 4326)

        public static Microsoft.SqlServer.Types.SqlGeography ToPolygon(string text)
        {
            // text = @"POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))";
            System.Data.SqlTypes.SqlChars polygon = new System.Data.SqlTypes.SqlChars(new System.Data.SqlTypes.SqlString(text));
            Microsoft.SqlServer.Types.SqlGeography poly = Microsoft.SqlServer.Types.SqlGeography.STMPolyFromText(polygon, 4326);
            return poly;
        }

        public static Microsoft.SqlServer.Types.SqlGeography ToPolygon(GeoPoint[] points)
        {
            string pointText = ObjJoin(", ", points);
            string text = "POLYGON((" + pointText + "))";

            Microsoft.SqlServer.Types.SqlGeography poly = ToPolygon(text);
            return poly;
        }

        public static void Test()
        {
            // DECLARE @Geom TABLE ( shape geometry ); 
            // SELECT geometry::UnionAggregate(shape).ToString() FROM @Geom;
            // geometry ST_Union(geometry[] g1_array);
            // https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/unionaggregate-geometry-data-type?view=sql-server-2017
            // https://gis.stackexchange.com/questions/237644/what-does-the-st-union-of-the-geometry-column-of-two-tables-produce

            GeoPoint[] points = new GeoPoint[]
            {
                  new GeoPoint(0,0)
                , new GeoPoint(0,0)
                , new GeoPoint(0,0)
                , new GeoPoint(0,0)
                , new GeoPoint(0,0)
            };

            string s1 = "POLYGON((9.3763619 47.4330074,9.3764389 47.4329684,9.3764072 47.4329405,9.3763969 47.4329322,9.3759864 47.4326004,9.376056 47.4325644,9.3761349 47.4325167,9.37619 47.4325653,9.376312 47.4326732,9.3765907 47.4328683,9.3766389 47.4328521,9.3767794 47.4329452,9.3764748 47.4331106,9.3763619 47.4330074))";
            string s2 = "POLYGON((9.3766833 47.4319973,9.3772045 47.4324131,9.3771257 47.432459,9.3769959 47.4323535,9.3767225 47.4325076,9.3768938 47.432637,9.3769843 47.4325975,9.3772713 47.432826,9.3771862 47.4328789,9.376941 47.4326789,9.3767283 47.4327757,9.3765053 47.4325749,9.376312 47.4326732,9.37619 47.4325653,9.3761349 47.4325167,9.376056 47.4325644,9.3757946 47.43237,9.3760399 47.4322419,9.376144 47.4323272,9.3761809 47.4323125,9.3762975 47.432428,9.3762371 47.4324602,9.3763095 47.4325246,9.3764699 47.4324328,9.3763633 47.4323437,9.3763976 47.4323193,9.3763247 47.4322628,9.3763972 47.4322251,9.3764363 47.4322061,9.3766528 47.4323718,9.3768611 47.4322514,9.3765976 47.4320409,9.3766833 47.4319973))'";

            // ST_GeomFromText(text WKT, integer srid);
            // ST_Union, ST_AsText
            // ST_GeomFromText('POINT(-2 3)') ) )
            // ST_Intersects( geography geogA , geography geogB )

            // Could not load Microsoft.SqlServer.Server

            Microsoft.SqlServer.Types.SqlGeography poly1 = ToPolygon(s1); // points);
            Microsoft.SqlServer.Types.SqlGeography poly2 = ToPolygon(s2); // points);

            Microsoft.SqlServer.Types.SqlGeography poly3 = poly1.STUnion(poly2);
            System.Data.SqlTypes.SqlChars chars = poly3.STAsText();
            string value = new string(chars.Value);
            System.Console.WriteLine(value);
        }
    }
}
#endif

And while you are at it, the version for the full .NET framwork (core also) should also work if SQL-Server is not installed on the machine that Microsoft.SqlServer.Types is executed on...

danmoseley commented 6 years ago

@ststeiger does it work on master, rather than 2.1?

It seems it was added here https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/Sql/IBinarySerialize.cs

rlisnoff commented 5 years ago

I'm experiencing the same problem, but when interacting with a table that has a HierarchyId column on it.

divega commented 5 years ago

I believe the main issue here is that Microsoft.SqlServer.Types.dll is not available for .NET Core. This library is part of the SQL Server product.

@karelz based on this and our exchange on https://github.com/dotnet/core/issues/2273#event-2108607462, I added the "tracking-external-issue". Is this the right label?

There are a few workarounds:

karelz commented 5 years ago

That's the right label + no assignment + Future milestone + bug label. What is the external issue? Which (internal?) db? Which product?

dotMorten commented 5 years ago

@dotMorten has created a version of the Microsoft.SqlServer.Types library that works on .NET Core, but my understanding is that it does not implement spatial calculations.

That is correct. While I could probably implement most of the calculations, the results are going to be slightly off. I don't think that's a good idea (for now perform those calculations server-side as part of your query). I'd rather we can get the native part of the spatial types open-sourced, so we can recompile it for more platforms (that would be a lot less work than porting it to C#).

justintoth commented 5 years ago

+1 for implementing Microsoft.SqlServer.Types for .NET Standard. We're attempting to convert our class libraries from .NET Framework to .NET Standard, and the code is littered with references to SqlGeography and SqlGeometry. I appreciate @dotMorten for building his nuget package, but it doesn't implement enough of the original to be viable for us to port to. Trying to convert to using NetTopologySuite looks promising, but would be a huge effort to implement...

ststeiger commented 5 years ago

Well, basically, If there were any fully managed C# library that would correctly implement STUnion of two WGS84-polygons, that would have been sufficient. At the time, I found nothing that did it halfway reliably. But now it looks like DotSpatial.Topology does that.

dotMorten commented 5 years ago

@ststeiger @justintoth Are you still running on Windows though? If so, we can still use the native library and call into it from .NET Core. Just wouldn't work on Linux/mac/android/ios etc.

justintoth commented 5 years ago

@dotMorten Yes, we're only running our applications on Windows. I saw in SO threads that you can supposedly reference the .NET framework version of the Microsoft.SqlServer.Types dll's and then call something like this in .NET Core:

Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

However, I tried it and the Utilities class doesn't exist in Microsoft.SqlServer.Types so it was a dead end for me. Any idea the proper way to do this?

dotMorten commented 5 years ago

@justintoth I was able to make it work in .NET core. I added the System.Data.SqlClient v4.6.0 and Microsoft.SqlServer.Types v14.0.1016.290 nuget packages. Next I manually added the Loader.cs file from inside the nuget package to my project as well as the required native libraries:

  <ItemGroup>
    <Content Include="$(USERPROFILE)\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\nativeBinaries\**\*.dll">
      <Link>SqlServerTypes\%(RecursiveDir)%(Filename)%(Extension)</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>
    <Compile Include="$(USERPROFILE)\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\content\SqlServerTypes\Loader.cs" Link="Loader.cs" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Types" Version="14.0.1016.290" />
    <PackageReference Include="System.Data.SqlClient" Version="4.6.0" />
  </ItemGroup>

After this, I was successfully able to do the following in a .NET Core 3.0 app (I'm guessing it'll work on earlier versions too):

SqlServerTypes.Utilities.LoadNativeAssemblies(".");
SqlGeometry p1 = SqlGeometry.Point(23, 34, 4326);
SqlGeometry p2 = SqlGeometry.Point(23, 35, 4326);
var union = p1.STUnion(p2);

Of course this will ONLY work when running your app on Windows as x86 or x64,

dotMorten commented 5 years ago

WOAH I even got this working on UWP. And because I don't have to deal with AnyCPU, I don't even need the LoadNativeAssemblies call. Here's what I added to .csproj to make it work in UWP:

  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient">
      <Version>4.6.0</Version>
    </PackageReference>
    <Reference Include="Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
      <SpecificVersion>False</SpecificVersion>
      <HintPath>$(USERPROFILE)\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\lib\net40\Microsoft.SqlServer.Types.dll</HintPath>
    </Reference>
  </ItemGroup>
  <ItemGroup Condition="'$(Platform)'=='x64'">
    <Content Include="$(USERPROFILE)\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\nativeBinaries\x64\*.dll">
      <Link>%(Filename)%(Extension)</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>    
  </ItemGroup>
  <ItemGroup Condition="'$(Platform)'=='x86'">
    <Content Include="$(USERPROFILE)\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\nativeBinaries\x86\*.dll">
      <Link>%(Filename)%(Extension)</Link>
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </Content>    
  </ItemGroup>

(just note that the binaries violate store certification checks so you won't be able to put it in the store)

ststeiger commented 5 years ago

@dotMorten: Yea well, our Windows-only shop runs on Windows. But on my private laptop(s) which I use use to test new things on the way to and from work, there I only have Linux, and at home as well. Windows just doesn't go into standby and back fast enough, and it crashes, too. Also, I don't like the endless updates at moments in time that I don't want them (such as on a metered connection or when I have to change a train) and can't do anything against them, because although I have switched everything off and to manual and marked the connection as metered, it still does so whenever it feels like it...

I think if I remember right, I just did it in SQL on SQL-server, which worked on Linux, too - to my not little surprise. Which would probably indicate that the binaries are ported, or at the very least they found a replacement.

SELECT 
    geography::STPolyFromText('POLYGON((7.5999034 47.5506347,7.6001195 47.550805,7.5999759 47.5508885,7.5998959 47.5508256,7.5997595 47.5507183,7.5999034 47.5506347))', 4326)
    .STUnion(
    geography::STPolyFromText('POLYGON((7.6003356 47.5509754,7.6001926 47.551059,7.6000322 47.5509328,7.5999759 47.5508885,7.6001195 47.550805,7.6003356 47.5509754))', 4326)
    ).STAsText()
justintoth commented 5 years ago

@dotMorten How did you add the Microsoft.SqlServer.Types nuget package to your .NET Core package when it's a .NET Framework nuget package? When I try to add it to my .NET Standard class library it fails to add.

dotMorten commented 5 years ago

@justintoth i just "did it". Nothing fancy. It warns you it might not be compatible but still goes ahead. Are you targeting 3.0? I can't remember if it's a 3.0 thing to allow that

justintoth commented 5 years ago

@dotMorten It's a .NET Standard 2.0 project. Here is the error I get when trying to download from nuget packet manager.

Error NU1701 Package 'Microsoft.SqlServer.Types 14.0.1016.290' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETStandard,Version=v2.0'. This package may not be fully compatible with your project.

dotMorten commented 5 years ago

Yeah that wouldn't work. You'd need to target .NET Core App. The title here is about supporting .NET Core, not .NET Standard.

ststeiger commented 5 years ago

@dotMorten: If it helps implementing, this is how to do STUnion (with polygons) in NetTopologySuite: https://github.com/ststeiger/AnySqlWebAdmin/blob/master/AnySqlWebAdmin/Code/PolygonUnion/Program.cs

If the union is done, all that is needed is converting the geometry to sql-server geometry.

Some help for ST_Area covered in dept here: https://gis.stackexchange.com/questions/169422/how-does-st-area-in-postgis-work/258107#258107 (sql-server behaves differently than postgresql with respect to geography vs. geometry)

STDistance is trivial (on a sphere) because it can be googled. However, STDistance as in SQL-server is a bit less trivial. DotSpatial.Positioning seems to have a corresponding implementation, however. 

public static double SpatialDistanceBetweenPlaces(Wgs84Coordinates a, Wgs84Coordinates b)
{
    var fablat = new DotSpatial.Positioning.Latitude((double)a.Latitude);
    var fablng = new DotSpatial.Positioning.Longitude((double)a.Longitude);

    var sglat = new DotSpatial.Positioning.Latitude((double)b.Latitude);
    var sglng = new DotSpatial.Positioning.Longitude((double)b.Longitude);

    var fab = new DotSpatial.Positioning.Position(fablat, fablng);
    var sg = new DotSpatial.Positioning.Position(sglat, sglng);

    DotSpatial.Positioning.Distance dist = fab.DistanceTo(sg);

    return dist.ToMeters().Value;
} // End Function SpatialDistanceBetweenPlaces

I have updated DotSpatial to NetStandard2_0 here: https://github.com/ststeiger/DotSpatial

All you need is DotSpatial.Projections and NetTopologySuite for polygons, and DotSpatial.Positioning for distance.

qcc-na commented 5 years ago

This is one of the last things keeping us from moving to .net core. Libraries are no doubt going to use this. Targeting .NET Core App seems like a hack.

justintoth commented 5 years ago

@qcc-na This ended up working for us...

  1. Download the Microsoft.SqlServer.Types nuget package into a .NET Framework project.
  2. In your .NET Standard class library, do Dependencies > Add Reference and browse to the Microsoft.SqlServer.Types.dll from the nuget package (although really you'll want to store this dll somewhere better like {workSpace}/References.
  3. Copy the SqlServerSpatial140.dll from the nuget package into the root of your .NET Core project. Change the Properties > Build Action to Copy Always.

Although this is using the .NET Framework version of Microsoft.SqlServer.Types, it has worked for us so far in our .NET Standard class libraries and .NET Core applications. Good luck!

ststeiger commented 5 years ago

@justintoth: Copying a native *.dll will not work on Linux, Mac or Android. Come to think of it, if you just copied a 32 xor 64-bit dll, it will not work with AnyCPU on Windows either.
Come to think of it further, if you didn't xcopy the C/C++ runtime libraries (of the right version) along with any other dependencies, it won't work for sure on a single CPU either.

At the very least, do a System.IntPtr.Size*8 == 64, and copy either a 32 or 64 bit library from the embedded resources to the ExecutingAssembly directory, and load the library with LoadLibrary (ASP.NET) or use SetDLL !

Realistically, you'll need to have to deploy the right C/C++ runtime libraries from the embedded resources as well, plus any pinvokes need to work with the same signature types on 32 & 64 bit, on all processors, on all operating systems supported by .NET Core/NetStandard2.

Just saying implicitly, the approach with a native library is a technically impossible/prohibitive proposition, if it is to be guaranteed to work on (xcopy) deployment (without administrative rights to install anything, such as the C/C++ runtime). If you want this to work with simply copying the dll, you need to statically link the C/C++ runtime, which you can't because you don't have the dll's source code.

Come to think of it even further, you'll need to check the dll's license to see if you are even allowed to do so (without an sql-server license, publicly) in the first place ...

I read somewhere (don't remember where) that the PostgreSQL entity-framework provider does internally convert the NetTopologySuite geometry types to pgsql-types - perhaps the place to start looking.

But i think MS geometry has a reverse rotation of the polygon points. Very expedient.

divega commented 5 years ago

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

divega commented 5 years ago

@David-Engel just wanted to give you the heads up on why I moved this issue here. I understand that technically Microsoft.SqlServer.Types isn't a component of SqlClient, and that the SqlClient team doesn't own it, but from the customer perspective, these types are part of the functionality offered by SqlClient.

I will continue to talk to the owners on SQL Server about the priority of enabling this. But we need an issue to track it somewhere and I believe it belongs here more than on CoreFx.

Bilal-Nectari commented 5 years ago

I just want to add that we also have an API that is heavily using the GetSchemaTable reader function, to retrieve the metadata from the reader and process it after.

It is crashing as well because it is not able to locate the Microsoft.SqlServer.Types DLL, when a reader has a column of the type geometry, geography or hierarchy. It will be nice if it gets supported.

It will be nice to have this fixed so we can move to .NET Core.

Ansssss commented 4 years ago

Would this issue also cover implementing the SqlHierarchyId type for .NET Core, or should I enter a separate issue for that?

ststeiger commented 4 years ago

@Ansssss: I'm no expert on the subject, but the thing is SqlGeography & SqlGeometry use a native library, which is closed-source and windows-only.

HierarchyId is just a CLR-type, and it probaby does not use native libraries, or at least I couldn't fathom why it possibly would. If that is so, I would create a separate issue for HierarchyId.

I could imagine that the issues with HierarchyId are far easier to solve than those with Geography/Geometry.

yorek commented 4 years ago

When trying to use SqlGeometry (SqlGeograpy or HiearchyId) with the new Microsoft.Data.SqlClient i get the following error:

Unhandled Exception: System.ArgumentException: Specified type is not registered on the target server.Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.

even if I can see that I have that library in my SQL Server assemblies (select * from sys.assemblies)

MaceWindu commented 4 years ago

Those types will not work with new client without rebuild against new provider as they use types from old one.

If dotMorten.Microsoft.SqlServer.Types implementation will be enough for your cases, you can rebuild it against new provider or give your vote here to release it for new provider.

arnaudHerontrack commented 4 years ago

Hello.

Almost a year later, any news about spatial data types for .net core ? We are rebuilding a existing project (in .net core) from scratch because the architecture was crap and the question remain. What about spatial data. In the previous version, they used Entity Framework Core but that tool does not support them either... So we had (we maintained that previous version for a while) to use Net Topology Suite.

In the new version, we won't use Entity Framework but we still need spatial data types though...

ErikEJ commented 4 years ago

You can use: https://github.com/dotMorten/Microsoft.SqlServer.Types @arnaudHerontrack

arnaudHerontrack commented 4 years ago

@ErikEJ : I've seen that library but it does not include any spatial operation. Currently, it might be enough but for the future, I can't be sure that we won't need to perform spatial operation in memory. But I just read about .Net 5 coming soon. As it merge .Net Core and .Net Framework, will it solve this problem ?

ErikEJ commented 4 years ago

No, those types are not a part of .net 5

arnaudHerontrack commented 4 years ago

Ok will the librairy Microsoft.SqlServer.Types be usable ? If yes, then it's ok...

ErikEJ commented 4 years ago

Of course it will, it is a netstandrad 2 library

arnaudHerontrack commented 4 years ago

Oh !? I thought I read in this thread that this precise library was not compatible with .Net Standard.

jnyrup commented 4 years ago

https://www.nuget.org/packages/dotMorten.Microsoft.SqlServer.Types/ is a netstandard2.0 project https://www.nuget.org/packages/Microsoft.SqlServer.Types/14.0.1016.290 is a net40 project and might be usable under several constraint:

shailendrarampal commented 4 years ago

When trying to use SqlGeometry (SqlGeograpy or HiearchyId) with the new Microsoft.Data.SqlClient i get the following error:

Unhandled Exception: System.ArgumentException: Specified type is not registered on the target server.Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.

even if I can see that I have that library in my SQL Server assemblies (select * from sys.assemblies)

Have anyone found the solution of this issue ? I am facing the same exception while adding SQLGeography type as command.parameter command.Parameters.Add(new SqlParameter("@deviceGeolocation", SqlDbType.Udt) { UdtTypeName = "Geography", Value = geo, });

Ozzah commented 3 years ago

Another issue with dotMorten/Microsoft.SqlServer.Types (aside from the missing implementations of certain spatial operations) is that it is based on System.Data.SqlClient and does not support Microsoft.Data.SqlClient.

This has cost us a huge amount of developer time and we still don't have a good solution. We really need a .Net Standard cross-platform implementation compatible with Microsoft.Data.SqlClient.

ststeiger commented 3 years ago

@Ozzah: What spatial operations do you need ? I needed area, distance, polygon-union and multipolygon-union. I was able to do distance & area with DotSpatial.Projections, and Unions with NetTopologySuite.

Multipolygons required implementing a Concave-Hull-Algorithm in NetTopologySuite myselfs. https://github.com/ststeiger/OsmPolygon/blob/master/OsmPolygon/Concave/ConcaveHull.cs

john-larson commented 3 years ago

I am using EF 6.3 with .NET Core and want to use spatial types on a Linux machine with SQL Server. Is https://github.com/dotMorten/Microsoft.SqlServer.Types my only option right now? I am a little confused about what NetTopologySuite.IO.SqlServerBytes is. Can I use it in my case? I would prefer an official solution.

failwyn commented 3 years ago

It's very frustrating that multiple versions of the Framework have been released and there has been no movement on this issue in nearly 3 years; I just need to call FillSchema on a SqlDataAdapter for a table that has a Geography column...

cheenamalhotra commented 3 years ago

@failwyn

This issue is also out of bounds for us, as commented here: https://github.com/dotnet/SqlClient/issues/322#issuecomment-810516964 | https://github.com/dotnet/SqlClient/issues/322#issuecomment-810577349

All I can say is efforts are in progress to resolve these gaps, but timelines are currently not known.

failwyn commented 3 years ago

Thanks @cheenamalhotra, is there a more appropriate place to have this bug logged so that the community can communicate with the proper team? I just find it amazing that such a simple, yet integral piece of functionality, has been left out for so long...

Ozzah commented 3 years ago

A workaround I am using at the moment is to modify my SQL query and cast it as binary (varchar) like so:

SELECT
    geometryColumn.STAsBinary() AS [geometryColumn],
    geographyColumn.STAsBinary() AS [geographyColumn]

I then use GeoJSON.Net to parse it from binary representation into usable data structures. For INSERT/UPDATE, I do much the same thing except serialise the GeoJSON object as binary and cast it in back to the spatial type in the query.

Honestly, getting the Microsoft version to work across .Net Framework and Core versions, and getting the dotMorten package to work across both System.Data.SqlClient and Microsoft.Client.SqlClient was such a headache. This workaround works well for me for the time being.

Ramana2596 commented 3 years ago

We are also facing similar issue with .Net Core and .Net Standard projects that we have at our end. Can anyone please let me know when the proper package or solution will be provided regarding this?

kolyanch commented 3 years ago

Just use NetTopologySuite.

Package for reading/writing geometry: https://github.com/NetTopologySuite/NetTopologySuite.IO.SqlServerBytes Works with System.Data.SqlClient and Microsoft.Data.SqlClient.

The suite includes all necessary spatial operations and data conversion packages. For example, if you want to convert geometry to GeoJSON, read it with SqlServerBytesReader, get the Geometry object and write it with GeoJSON serializer: https://github.com/NetTopologySuite/NetTopologySuite.IO.GeoJSON.

Be careful. Some SqlClient methods can produce exceptions with Microsoft.SqlServer.Types.dll if you are trying to access the geometry column. For example, GetSqlValue will throw: Could not load file or assembly Microsoft.SqlServer.Types.

cl0ckt0wer commented 3 years ago

I was using dotMorten/Microsoft.SqlServer.Types for a time, but ran into the issue with it using System.Data.SqlClient. In the pull requests I saw this one Use Microsoft.Data.SqlClient 2.1.0 #58. I was able to pull that down and build it, then statically link the dll. It seems to be working enough that I can pump sqlGeography types into dapper.

https://github.com/ErikEJ/Microsoft.SqlServer.Types/tree/mds-package

Can someone reach out to dotMorten on twitter and ask him to push up the changes to his nuget package?

https://twitter.com/dotMorten

ErikEJ commented 3 years ago

@cl0ckt0wer @dotMorten FYI!

ErikEJ commented 3 years ago

@cl0ckt0wer Reach out to @dotMorten via the related GitHub issues the PR fixes

stevetalkscode commented 2 years ago

I have seen that the SQL Server team now have a preview of a newer version of Microsoft.SqlServer.Types that is now compatible with .NET Standard 2.1 ( https://www.nuget.org/packages/Microsoft.SqlServer.Types/160.900.6-rc0)).

Are there plans to update System.Data.SqlClient or will this only be supported in Microsoft.Data.SqlClient?

dotMorten commented 2 years ago

@stevetalkscode Not really that great. There's a runtimes folder in there indicating it still only supports Windows x86 and x64. No ARM64 support, nor ios, android, macos or linux support. Don’t really get why they target netstandard2.1. If they only support windows they cut of UWP and they could target net6.0-windows to make it clear that only Windows is supported.