How to insert DbGeography to SQL Server using dapper

Question

I've created the model using System.Data.Entity.Spatial;

public class Store
{
    public int Id { get; private set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public DbGeography Location { get; set; }
}

Inserting to DB

using (SqlConnection conn = SqlHelper.GetOpenConnection())
{
    const string sql = "INSERT INTO Stores(Name, Address, Location) " + 
                       "VALUES (@Name, @Address, @Location)";
    return conn.Execute(sql, store);                                
}

i get the exception type System.Data.Entity.Spatial.DbGeography cannot be used as a parameter value

I've tried searching for ways to insert, this is the best i can find, but it's trying to insert only 1 parameter, what should i do in order to insert an object that have a dbgeography member?

Update #1

I've gave up trying to hack around or extend stuff, as i'm very new to dapper and time is not currently on my side. I went back to basic doing this, as i'm not required to do geography data type insertion very frequently

using (SqlConnection conn = SqlHelper.GetOpenConnection())
        {
            var sql = "INSERT INTO Stores (Name, Address, IsActive, Location, TenantId) " +
                      "VALUES('@Name', '@Address', @IsActive, geography::Point(@Lat,@Lng, 4326), @TenantId);";

            return conn.Execute(sql, new 
            { 
                Name = store.Name, 
                Address = store.Address, 
                IsActive = store.IsActive,
                Lat = store.Location.Latitude.Value,
                Lng = store.Location.Longitude.Value,
                TenantId = store.TenantId
            });             
        }

Community · Accepted Answer · 2017-05-23 12:07:34Z

2

Adding direct support for types outside of the core ADO.NET assemblies is problematic, as it either forces lots of name-based reflection, or swells the dependencies (and causes versioning problems). It is not necessary (or even appropriate, IMO) to use IDynamicParameters here - instead, ICustomQueryParameter can be used to represent a single parameter. There is no existing special-case detection of DbGeography, so unless there are library changes, you would have to do something like:

return conn.Execute(sql, new {
    store.Name, store.Address, Location=store.Location.AsParameter()
});

where AsParameter() is an extension method that returns an ICustomQueryParameter implementation that adds it appropriately.

Edit: see here for updates on this: https://stackoverflow.com/a/24408529/23354

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Jun 25, 2014 at 9:47

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lee Gary Over a year ago

i've updated the question and have the 2nd part of the problem, which i realise is as problematic, which is to query the data out from db and binding it back to mvc stackoverflow.com/questions/24405645/…

Collectives™ on Stack Overflow

How to insert DbGeography to SQL Server using dapper

Inserting to DB

Update #1

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Inserting to DB

Update #1

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related