如何用短小精悍插入DbGeography到SQL Server [英] How to insert DbGeography to SQL Server using dapper

问题描述

我创建的模型使用System.Data.Entity.Spatial;

 公共类商店
 {
公众诠释标识{搞定;私人集; } 
公共字符串名称{;组; } 
公共字符串地址{搞定;组; } 
公共DbGeography位置{搞定;组; } 
} 
  

插入到数据库

 使用（SqlConnection的康恩= SqlHelper.GetOpenConnection（））
 {
常量字符串的sql =INSERT INTO百货（名称，地址，位置）+ 
VALUES（@Name，@address，@location）; 
返回conn.Execute（SQL，存储）; 
} 
  

我得到异常键入System.Data.Entity的.Spatial.DbGeography不能用作参数值

我试着寻找方法来插入，的this 是最好的我能找到，但它试图插入只有1个参数，我应该怎么做，以便插入具有dbgeography成员的对象？

更新＃1

我'已经放弃了试图破解周围或扩展的东西，因为我很新的短小精悍和时间目前还不在我的身边。我又回到了基本的这样做，因为我不是做地理数据类型插入非常频繁要求

 使用（SqlConnection的康恩= SqlHelper.GetOpenConnection（））
 {
 VAR SQL =INSERT INTO百货（姓名，地址，IsActive，位置，TenantId）+ 
VALUES（'@名称，@地址，@IsActive，地理::点（@纬度，@ LNG，4326），@TenantId）;; 
 
返回conn.Execute（SQL，新的
 {
名称= store.Name，
地址= store.Address，
 IsActive = store.IsActive ，
纬度= store.Location.Latitude.Value，
 LNG = store.Location.Longitude.Value，
 TenantId = store.TenantId 
}）; 
} 
  

解决方案

添加直接外线类型的支持芯ADO.NET组件是有问题的，因为它要么力大量基于名称的反射，或溶胀的依赖关系（并导致版本问题）。这是没有必要的（甚至是适当的，IMO）使用 IDynamicParameters 在这里 - 相反， ICustomQueryParameter 可以用来代表单个参数。还有就是 DbGeography 不存在特殊情况的检测，这样的除非有库更改的，你必须做的是这样的：

 返回conn.Execute（SQL，新{
 store.Name，store.Address，位置= store.Location.AsParameter（） 
}）; 
  

其中， AsParameter（）是一个扩展方法返回一个 ICustomQueryParameter 实现，适当地增加了它。

---

编辑：在这里看到这个更新： http://stackoverflow.com/a/24408529/23354

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

解决方案

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: http://stackoverflow.com/a/24408529/23354

这篇关于如何用短小精悍插入DbGeography到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！