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

10
votes

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?

2
c#sql-serverentity-frameworkado.netsqlgeography
Did you try this stackoverflow.com/questions/15061227/… – JunaidKirkire
I have tried all solutions here from stackverflow but none worked – 1110
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? - github.com/Glimpse/Glimpse/issues/646 – JunaidKirkire
Did you ever solve this? Could you try using the Microsoft.SqlServer.Types instead of System.Data.Entity.... – Josiah Peters
You can avoid all the guesswork building your command by using SqlCommandBuilder. Likewise, you can avoid all the ugliness of string manipulation by using SqlGeographyBuilder. Sorry for the double link...:O) – jsanalytics

2 Answers

10
votes

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://docs.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.

Wellknown text module

1
votes

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