问题描述
是的，这里有和有关如何使用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调用dapper扩展方法。现在，WTUser类（注意：列名从SqlServer中的 WindowsName更改为MySql中的 ResourceName，因此这两个属性指向同一字段）：
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
我收到dapper的异常消息： Dapper不支持WTUser。 DataMapper（dapper）中的此方法：
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 =
似乎OwnerType应该是 CRMBackEnd.WTUser ...而不是 List< 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);
}
它的工作原理与广告宣传相同。我对多执行器的工作方式进行了一些修改（因此，它的运行速度更快，并且支持object []）。
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属性。所有参数都必须具有读取器属性，我们不支持从字段中拉出该属性，这需要复杂的解析步骤才能保持效率。
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.
引起问题的另一个原因是，将不支持映射的参数传递给dapper。
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，但无法解析 User 的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>。 Dapper错误收集并显示为“ Dapper不支持类”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！