How Do I Use Sql Server Spatial Data Types

[marqdouj]

I am using .NET Core 5.0, Microsoft.Data.SqlClient Version 2.0.1

I have a table that contains a geography field. When I do a 'Select * From MyTable' I get this error: DataReader.GetFieldType(24) returned null.

I found this nuget package but it won't install: Microsoft.SqlServer.Types

So how do I read data that contains geography types?

[Wraith2]

I think the suggested option is to use https://github.com/NetTopologySuite

[marqdouj]

I the only package I found was for ER Core

---

From: Wraith notifications@github.com Sent: December 20, 2020 5:34 PM To: dotnet/SqlClient SqlClient@noreply.github.com Cc: marqdouj marqdouj@outlook.com; Author author@noreply.github.com Subject: Re: [dotnet/SqlClient] How Do I Use Sql Server Spatial Data Types (#858)

I think the suggested option is to use https://github.com/NetTopologySuite

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/858#issuecomment-748697352, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AD563UHDURAORZSHVU75MTDSV2J2TANCNFSM4VDKF7JQ.

[marqdouj]

EF Core

---

From: Douglas Marquardt marqdouj@outlook.com Sent: December 20, 2020 5:45 PM To: dotnet/SqlClient SqlClient@noreply.github.com; dotnet/SqlClient reply@reply.github.com Cc: Author author@noreply.github.com Subject: Re: [dotnet/SqlClient] How Do I Use Sql Server Spatial Data Types (#858)

I the only package I found was for ER Core

---

From: Wraith notifications@github.com Sent: December 20, 2020 5:34 PM To: dotnet/SqlClient SqlClient@noreply.github.com Cc: marqdouj marqdouj@outlook.com; Author author@noreply.github.com Subject: Re: [dotnet/SqlClient] How Do I Use Sql Server Spatial Data Types (#858)

I think the suggested option is to use https://github.com/NetTopologySuite

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/858#issuecomment-748697352, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AD563UHDURAORZSHVU75MTDSV2J2TANCNFSM4VDKF7JQ.

[Wraith2]

https://github.com/NetTopologySuite/NetTopologySuite.IO.SqlServerBytes ?

[marqdouj]

I will try that, thanks.

[marqdouj]

I don't think that is what I need (or at least I can't figure out how to use it). I have used Microsoft.SqlServer.Types before in .NET 4.7.2 and it worked fine; it just won't install in .NET Standard or .NET Core.

[Wraith2]

Microsoft.SqlServer.Types relies on a windows only native dll which contains the implementation of those types' methods. It isn't portable so you can't use it on core or netstandard. You need a fully managed or at least cross platform capable library that provides the specific functionality you want.

Can you given a simple standalone example of what you're trying to do?

[Wraith2]

related old thread, may cover what you need: https://github.com/dotnet/SqlClient/issues/30

[marqdouj]

This is the code I use to fill a table. I want to be able to use sql like 'Select * From MyTable Where Id = 1'. I'm not sure if the NetTopologySuite can be use here to make this work?

public static int FillTable(this SqlConnection cn, SqlTransaction tn, DataTable table, string sql, Dictionary<string, object> parameters = null, LoadOption fillLoadOption = LoadOption.OverwriteChanges)
{
    using (var cmd = cn.CreateCommand(tn, sql, parameters))
    {
        using (var adapter = new SqlDataAdapter((SqlCommand) cmd))
        {
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adapter.FillLoadOption = fillLoadOption;
            return adapter.Fill(table);
        }
    }
}

[ErikEJ]

@marqdouj I doubt this will work on .net core - you have to use DataReader...

[cheenamalhotra]

Hi @marqdouj

As @Wraith2 linked above too, there is an open issue #30 for the same. There is no .NET Core/Standard support for SQL Spatial types (Microsoft.SqlServer.Types) as of now.

[marqdouj]

Thanks all for the help. I've worked around the issue for now, as I don't really want to get the spatial data - it's just that I was getting errors. I'm first getting a list of column info using INFORMATION_SCHEMA.COLUMNS and then building my column list using all the columns that are not spatial.

[cheenamalhotra]

Will close this issue, as we already are tracking missing spatial type support in #30.