What is the right way to insert DBGeography type via ado.net

Question

I am trying to insert DBGeography type via ado.net but no luck.
This is the errors that I am getting:

No mapping exists from object type System.Data.Entity.Spatial.DbGeography to a known managed provider native type.

or:

Specified type is not registered on the target server.System.Data.Entity.Spatial.DbGeography, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

This is what I do when I am getting it from db and this works fine:

dynamic temp = reader.GetValue(3);

                var text = string.Format("POINT({0:R} {1:R})", temp.Long, temp.Lat);
                var srid = temp.STSrid.Value;
                this.Coordinates = System.Data.Entity.Spatial.DbGeography.PointFromText(text, srid);

But insert doesn't work:

updateCommand.Parameters.AddWithValue("@Coordinates", store.Coordinates);
// or ...
SqlParameter p = new SqlParameter();
                    p.ParameterName = "@Coordinates";
                    p.Value = store.Coordinates;
                    p.SqlDbType = System.Data.SqlDbType.Udt;
                    p.UdtTypeName = "geography";
                    updateCommand.Parameters.Add(p);

What is wrong here?

Did you try this http://stackoverflow.com/questions/15061227/no-mapping-exists-from-object-type-system-data-spatial-dbgeography-to-a-known-ma — JunaidKirkire, Jan 08 '15 at 10:45
I have tried all solutions here from stackverflow but none worked — 1110, Jan 08 '15 at 10:57
From the error "Specified type is not registered on the target server", it appears that some package is missing. Did you try re-installing? Did you check this link? - https://github.com/Glimpse/Glimpse/issues/646 — JunaidKirkire, Jan 08 '15 at 10:59
Did you ever solve this? Could you try using the Microsoft.SqlServer.Types instead of System.Data.Entity.... — Josiah Peters, Jun 09 '15 at 20:27
You can avoid all the guesswork building your command by using [SqlCommandBuilder](https://imgur.com/dcdUVyE). Likewise, you can avoid all the ugliness of string manipulation by using [SqlGeographyBuilder](https://imgur.com/dcdUVyE). Sorry for the double link...:O) — jsanalytics, Jul 07 '19 at 00:05
Hi, I am facing the below exception while adding SQLGeography type as SQL command.parameter to store procedue. `Specified type is not registered on the target server.Microsoft.SqlServer.Types.SqlGeography` `command.Parameters.Add(new SqlParameter("@deviceGeolocation", SqlDbType.Udt) { UdtTypeName = "Geography", Value = geo, });` [issue link here](https://stackoverflow.com/questions/64205007/specified-type-is-not-registered-on-the-target-server-microsoft-sqlserver-types) — Shailendra, Oct 05 '20 at 14:43

score 11 · Answer 1 · answered Jul 06 '19 at 06:14

DbGeography is a type that designed for EntityFramework not ADO.NET. Try to parse a Well Know Text Module to SqlGeography by SqlGeography.Parse(SqlString) method, this should fix your problem.

dynamic temp = reader.GetValue(3);
var text = string.Format("POINT({0:R} {1:R})", temp.Long, temp.Lat);
var coordinate= SqlGeography.Parse(text );

SqlParameter p = new SqlParameter();
                 p.ParameterName = "@Coordinates";
                 p.Value = coordinate;
                 p.SqlDbType = System.Data.SqlDbType.Udt;
                 p.UdtTypeName = "geography";

updateCommand.Parameters.Add(p);

TL;DR:

https://learn.microsoft.com/en-us/bingmaps/v8-web-control/modules/well-known-text-module

Well Known Text (WKT) is an Open Geospatial Consortium (OGC) standard that is used to represent spatial data in a textual format. Most OGC-compliant systems support Well Known Text. Spatial functionality in SQL Server 2008, 2012, and SQL Azure can easily convert between a spatial object in the database and WKT. A WKT can only store the information for a single spatial object and this spatial data format is usually used as part of a larger file format or web service response. The following are examples of each of the geometry types represented as Well Known Text and the equivalent Bing Maps class that is generated when parsing a Well Known Text string.

I am facing below issue while implementing this approach... https://stackoverflow.com/questions/64205007/specified-type-is-not-registered-on-the-target-server-microsoft-sqlserver-types — Shailendra, Oct 05 '20 at 14:30

score 1 · Answer 2 · answered Jul 08 '19 at 14:02

Add those ASSEMBLIES System.Data.SqlClient; System.Data.SqlTypes; System.Data.SqlServer.Types;

I think Geography is wrong(lower case).

extents.UdtTypeName = "Geography";

p.UdtTypeName = "geography"; or Location = DbGeography.FromText("POINT(-122.360 47.656)")

Try this

public void InsertArea(string nameParameter, string extentsString)
{
       SqlConnection sqlConn = new SqlConnection(...)

       sqlConn.Open();

       SqlCommand sqlCom = new SqlCommand("INSERT INTO areas (name, extents) VALUES (@name, @extents)", sqlConn);

       sqlCom.Parameters.AddWithValue("@name", nameParameter);

       SqlParamater extents = new SqlParameter("@extents", SqlDbType.Udt);
       extents.UdtTypeName = "Geography";
       extents.Value = GetGeographyFromText(extentsString);

       sqlCom.Parameters.Add(extents);

       sqlCom.ExecuteNonQuery();

       sqlConn.Close();
}

public SqlGeography GetGeographyFromText(String pText)
{
       SqlString ss = new SqlString(pText);
       SqlChars sc = new SqlChars(ss);
       try
       {
           return SqlGeography.STPolyFromText(sc, 4326);
       }
       catch (Exception ex)
       {
           throw ex;
       }
}


 string areaName = "Texas";
       string extents = string.Format("POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))", leftLongitude, upperLatitude, lowerLatitude, rightLongitude));

       InsertArea(areaName, extents);

What is the right way to insert DBGeography type via ado.net

2 Answers2