插入 IEnumerable包含 Dapper 的集合会出现错误，并显示“Dapper 不支持类。”

Question

是的，这里有问题和此处了解如何使用 dapper-dot-net 插入记录。然而，答案虽然提供了丰富的信息，但似乎并没有为我指明正确的方向。情况如下：将数据从 SqlServer 移动到 MySql。将记录读入 IEnumerable 很容易，但我只是没有在插入中得到任何东西。首先，“移动记录代码”：

//  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 调用 dapper 扩展方法。现在，WTUser类（注意：列名从SqlServer中的“WindowsName”更改为MySql中的“ResourceName”，因此两个属性指向同一字段）：

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

我收到来自dapper的异常：“WTUser不受衣冠楚楚。” 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 =

System.Collections.Generic.List`1[[CRMBackEnd.WTUser, CRMBE，版本=1.0.0.0， 文化=中立， PublicKeyToken = null]]，mscorlib， 版本=2.0.0.0，文化=中立， PublicKeyToken=b77a5c561934e089

似乎 OwnerType 应该是 CRMBackEnd.WTUser ...而不是 List ...？？？因为正在发生的情况是集合属性正在迭代：计数、容量等。 我缺少什么？

更新

如果我将 session.WriteData 修改为：

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

插入记录很好...但我认为这没有必要，例如：

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"  

...来自 短小精悍的点网

Answer 1

我刚刚为此添加了一个测试：

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

它的工作原理如广告所示。我对多重执行的工作方式做了一些修改（所以它更快一点并且支持 object[]）。

我的猜测是您遇到了问题，因为您在 WTUser 上的所有字段上都缺少 getter 属性。所有参数必须具有读取器属性，我们不支持从字段中提取此属性，这需要复杂的解析步骤才能保持高效。

---

导致问题的另一点是向 dapper 传递具有不受支持的映射的参数。

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

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 

问题是 dapper 没有解析 SQL，它假设所有 props 都可以设置为参数，但无法解析 的 SQL 类型代码>用户。

最新版本解决了这个问题