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

score 2 · Accepted Answer · edited May 23 '17 at 12:07

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

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 http://stackoverflow.com/questions/24405645/query-spatial-data-with-dapper — Lee Gary, Jun 25 '14 at 10:02

How to insert DbGeography to SQL Server using dapper

Inserting to DB

Update #1

1 Answers1

Linked