如何插入IEnumerable 用dapper-dot-net收集

c# dapper insert vb.net

是的, 這里這裡有關於如何使用dapper-dot-net插入記錄的問題 。然而,答案雖然提供了豐富的信息,但似乎並未將我指向正確的方向。以下是這種情況:將數據從SqlServer移動到MySql。將記錄讀入IEnumerable<WTUser>很容易,但我只是沒有在插入上獲取內容。首先,'移動記錄代碼':

//  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收到一個例外:“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,Version = 1.0.0.0,Culture = neutral,PublicKeyToken = null]],mscorlib,Version = 2.0.0.0,Culture = neutral,PublicKeyToken = b77a5c561934e089

似乎OwnerType應該是CRMBackEnd.WTUser ...不是List<CRMBackEnd.WTUser> ...... ???因為正在發生的事情是集合屬性正在被迭代:Count,Capacity等。我缺少什麼?

更新

如果我將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"  

......來自dapper-dot-net

一般承認的答案

我剛剛為此添加了一個測試:

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

它像宣傳的那樣工作。我對multi-exec的工作方式做了一些修改(所以它的速度更快,支持object [])。

我的猜測是你有問題,因為你在WTUser所有領域都缺少一個getter屬性。所有參數都必須具有閱讀器屬性,我們不支持從字段中提取它,它需要復雜的解析步驟才能保持高效。


導致問題的另一個問題是將dapper傳遞給不支持映射的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類型User

最新轉速解決了這個問題



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因