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?

Answer 1

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.

Answer 2

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);