如何插入一个IEnumerable< T>集合，短小精悍点网 [英] How to insert an IEnumerable<T> collection with dapper-dot-net

问题描述

是的，还有这里是的问题和的这里有关如何使用短小精悍点网插入记录。然而，问题的答案，而知识性，似乎并没有指出我在正确的方向。这里的情况是：从移动数据的SqlServer到MySql。读记录到的IEnumerable< WTUser> 很容易，但我只是没有得到的东西插入。首先，移动记录代码：

Yep, there are questions here and here about how to insert records with dapper-dot-net. However, the answers, while informative, didn't seem to point me in the right direction. Here is the situation: moving data from SqlServer to MySql. Reading the records into an IEnumerable<WTUser> is easy, but I am just not getting something on the insert. First, the 'moving records code':

//  moving data
Dim session As New Session(DataProvider.MSSql, "server", _
                           "database")

Dim resources As List(Of WTUser) = session.QueryReader(Of WTUser)("select * from tbl_resource")


session = New Session(DataProvider.MySql, "server", "database", _
                      "user", "p@$$w0rd")

//    *edit* - corrected parameter notation with '@'
Dim strInsert = "INSERT INTO tbl_resource (ResourceName, ResourceRate, ResourceTypeID, ActiveYN) " & _
                "VALUES (@ResourceName, @ResourceRate, @ResourceType, @ActiveYN)"

Dim recordCount = session.WriteData(Of WTUser)(strInsert, resources)

//  session Methods
    Public Function QueryReader(Of TEntity As {Class, New})(ByVal Command As String) _
                                                            As IEnumerable(Of TEntity)
        Dim list As IEnumerable(Of TEntity)

        Dim cnn As IDbConnection = dataAgent.NewConnection
        list = cnn.Query(Of TEntity)(Command, Nothing, Nothing, True, 0, CommandType.Text).ToList()

        Return list
    End Function

    Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, ByVal Entities As IEnumerable(Of TEntity)) _
                                                          As Integer
        Dim cnn As IDbConnection = dataAgent.NewConnection

        //    *edit* if I do this I get the correct properties, but no data inserted
        //Return cnn.Execute(Command, New TEntity(), Nothing, 15, CommandType.Text)

        //    original Return statement
        Return cnn.Execute(Command, Entities, Nothing, 15, CommandType.Text)
    End Function

cnn.Query和cnn.Execute调用短小精悍的扩展方法。现在，WTUser类（注：列名'WindowsName中的SqlServer MySql中改为资源名称，因此两个属性指向相同的字段）：

cnn.Query and cnn.Execute call the dapper extension methods. Now, the WTUser class (note: the column name changed from 'WindowsName' in SqlServer to 'ResourceName' in MySql, thus the two properties pointing to the same field):

Public Class WTUser
    //    edited for brevity - assume the following all have public get/set methods
    Public ActiveYN As String
    Public ResourceID As Integer
    Public ResourceRate As Integer
    Public ResourceType As Integer
    Public WindowsName As String
    Public ResourceName As String

End Class

我收到了来自短小精悍的异常：WTUser不小巧玲珑的支持。此方法的DataMapper（短小精悍）：

I am receiving an exception from dapper: "WTUser is not supported by Dapper." This method in DataMapper (dapper):

    private static Action<IDbCommand, object> CreateParamInfoGenerator(Type OwnerType)
    {
        string dmName = string.Format("ParamInfo{0}", Guid.NewGuid());
        Type[] objTypes = new[] { typeof(IDbCommand), typeof(object) };

        var dm = new DynamicMethod(dmName, null, objTypes, OwnerType, true); // << - here
        //    emit stuff

        //    dm is instanced, now ...
        foreach (var prop in OwnerType.GetProperties().OrderBy(p => p.Name))

在这一点上OwnerType =

At this point OwnerType =

System.Collections.Generic.List`1 [CRMBackEnd.WTUser，
CRMBE，版本= 1.0.0.0，
区域性=中性，
公钥= NULL]，mscorlib程序，
版本2.0.0.0 =文化=中立，
公钥= b77a5c561934e089

System.Collections.Generic.List`1[[CRMBackEnd.WTUser, CRMBE, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

好像OwnerType应 CRMBackEnd.WTUser ...不是列表< CRMBackEnd.WTUser> ...？ ??因为正在发生的事情是，集合属性被迭代：计数，容量等的我缺少什么

It seems like OwnerType should be CRMBackEnd.WTUser ... not List<CRMBackEnd.WTUser> ... ??? because what is happening is that the collection properties are being iterated: Count, Capacity, etc. What am I missing?

更新

如果我修改session.WriteData为：

If I modified session.WriteData as:

Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, _
                                                      ByVal Entities As IEnumerable(Of TEntity)) _
                                                      As Integer
    Dim cnn As IDbConnection = dataAgent.NewConnection
    Dim records As Integer

    For Each entity As TEntity In Entities
        records += cnn.Execute(Command, entity, Nothing, 15, CommandType.Text)
    Next

    Return records
End Function

记录插入很好......但我没想到这会像必要的，因为例子：

records are inserted nicely ... but I didn't think this would be necessary given examples like:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"  

...从短小精悍点网

推荐答案

我只是增加了一个为这个测试：

I just added a test for this:

class Student
{
    public string Name {get; set;}
    public int Age { get; set; }
}

public void TestExecuteMultipleCommandStrongType()
{
    connection.Execute("create table #t(Name nvarchar(max), Age int)");
    int tally = connection.Execute(@"insert #t (Name,Age) values(@Name, @Age)", new List<Student> 
    {
        new Student{Age = 1, Name = "sam"},
        new Student{Age = 2, Name = "bob"}
    });
    int sum = connection.Query<int>("select sum(Age) from #t drop table #t").First();
    tally.IsEqualTo(2);
    sum.IsEqualTo(3);
}

它像宣传的那样。我做了一些修订路多EXEC作品（所以它一点点速度更快，支持使用对象[]）。

It works as advertised. I made a few amendments to the way multi-exec works (so its a tad faster and supports object[]).

我的猜测是你有你的事业上了你的所有领域的 WTUser 缺少一个getter财产问题。所有PARAMS必须有读者性质，我们不支持从田地拉这一点，它需要一个复杂的分析步骤保持高效。

My guess is you were having issues cause you were missing a getter property on all you fields on WTUser. All params must have reader properties, we do not support pulling this from fields, it would require a complex parsing step to stay efficient.

这导致的一个问题是通过短小精悍的不支持的映射参数另外一点。

An additional point that caused an issue is passing dapper a param with unsupported mapping.

例如，下面的类不支持作为参数：

For example, the following class is not supported as a param:

class Test
{
   public int Id { get; set; }
   public User User {get; set;}
}

cnn.Query("select * from Tests where Id = @Id", new Test{Id = 1}); // used to go boom 

的问题是，短小精悍做的不可以解析在SQL，它假定所有的道具都可以设置为PARAMS，但无法解析用户 SQL类型。

The issue is that dapper did not parse the SQL, it assumed all the props are settable as params but was unable to resolve the SQL type for User.

最新修订版解决了这个

这篇关于如何插入一个IEnumerable< T>集合，短小精悍点网的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！