Dapper多重插入返回插入的对象 [英] Dapper multi insert returning inserted objects

问题描述

使用 Dapper 我想实现一个采用<$ c $的方法c>类型为 User 的对象的IEnumberable 。现在， User 看起来如下：

Using Dapper I would like to implement a method that takes an IEnumberable of objects of type User. Now, User looks as follows:

public class User
{
  public int UserId { get; internal set; }
  public DateTime DateCreated { get; internal set; }
  public DateTime DateChanged { get; internal set; }
  public string Username { get; set; }
}

这里的要点是 UserId ， DateCreated 和 DateChanged 绝不能通过对象设置，因此，内部关键字。相反，数据库将填充这些值。

The point here is that UserId, DateCreated and DateChanged shall never be set via object, hence the internal keyword. Instead the database will populate these values.

因为因此将对象修改为插入操作的一部分，所以我想返回另一个 IEnumerable 个用户类型为 User 的对象，但这一次填充了相应的属性。

Because the objects are therefore modified as part of the insert operation I want to return another IEnumerable of objects of type User but this time with the corresponding properties populated.

最近我意识到我可以让Dapper循环遍历 IEnumerable 中的 User 对象，如下所示：

Recently I realized that I can let Dapper loop through the User objects in the IEnumerable as follows:

public int Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username)");
  return GetOpenConnection().Execute<User>(sql, users);
}

这很简洁，因为我不必写 foreach 我自己。现在，这里的问题是 Execute 仅返回实际插入的行数。

This is neat because I do not have to write the foreach myself. Now, the problem here is that Execute will only return the number of actually inserted rows.

所以我尝试了使用 Query 如下：

So I tried it using Query as follows:

public IEnumerable<User> Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username) SELECT * FROM [User] WHERE UserId = scope_identity()");
  return GetOpenConnection().Query<User>(sql, users);
}

但是，这只会引发 InvalidOperationException 异常，并显示消息在此上下文中不允许使用可枚举的参数序列（数组，列表等）。

However, this simply throws an InvalidOperationException exception with message "An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context".

我对此感到困惑。我该如何工作？

I'm stuck with this. How can I make this work?

我是否必须遍历输入 IEnumerable 执行查询循环体内的每个对象？这样，如果要插入所有，则 Query 方法的 IDbTransaction 参数将无用。用户在同一事务中对象，因此我必须将整个循环包装在事务中，而不是将事务传递给 Query 。

Do I have to loop through my input IEnumerable executing Query for each object inside loop body? This way the IDbTransaction parameter of the Query method would be useless if I want to insert all User objects in the same transaction so I'd have to wrap the entire loop in a transaction instead of passing the transaction to Query.

使用Dapper插入多个对象并将完全填充的对象返回给调用者的正确方法是什么？

What is the "proper" way to insert multiple objects using Dapper and return the fully populated objects back to the caller?

推荐答案

使用Dapper.Net插入或更新对象列表，您将无法使用查询

to insert or update List of object with Dapper.Net you can't use Query

 connection.Query<Object>("your_query",your_list) 
 //connection.Query<Object>: use to select IEnumrable<object> from db
 //connection.QueryMultiple: use to execut multiple query at once then read result one by one 

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
  var customer = multi.Read<Customer>().Single();
  var orders = multi.Read<Order>().ToList();
  var returns = multi.Read<Return>().ToList();
   ...
} 

您应仅对多条插入使用Execute或更新

you should use only Execute for multi insert or update

Execute("your_query",your_list, your_transaction);

因此，如果您需要多次插入并返回插入记录的ID

so if you need to multi insert and return IDs for inserted records

// **using transaction depend on your needs**

//示例多次插入并返回完整记录

//Example to multi insert and return full record

  string query = @"Insert Into _TableName ( _columns) 
                                  OUTPUT INSERTED.* 
                                values ( _parameters )"; //parameters should be same as object properties name to let dapper do correct mapping 

[输出*] 将返回带有ID的完整插入行，您可以通过将星号替换为属性名 [OUTPUT INSERTED.Id] 来返回任何属性，而只返回ID

[OUTPUT INSERTED.*] will return full insert row with id and you are free to return any property by replace asterisk with propertyname [OUTPUT INSERTED.Id] will return only id

//会适合小型列表

 for (int i = 0; i < youList.Count-1; i++)
                {
                    youList[i] = DbConnection.Query<object>(query, youList[i]).FirstOrDefault();
                } // for loop is better for preformance

//对于大列表，您可以使用 SqlBulkCopy 查看此链接此处

//for big List you can use SqlBulkCopy review this link here

这篇关于Dapper多重插入返回插入的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！