如何使用 Dapper.NET 将 C# 列表插入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17150542/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert a C# List to database using Dapper.NET
提问by Praveen
Using dapper, how can I insert a C# Listto database. Previously without dapperI used the below code to insert the List values to database.
使用dapper，如何将一个插入C# List到数据库中。以前没有dapper我使用下面的代码将 List 值插入到数据库中。
try
{
connection.Open();
for (int i = 0; i < processList.Count; i++)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@Id, @st_Time, @ed_Time, @td_Time)";
command = new SqlCommand(processQuery, connection);
command.Parameters.Add("Id", SqlDbType.Int).Value = processList[i].ID;
command.Parameters.Add("st_Time", SqlDbType.DateTime).Value = processList[i].ST_TIME;
command.Parameters.Add("ed_Time", SqlDbType.DateTime).Value = processList[i].ED_TIME;
command.Parameters.Add("td_Time", SqlDbType.DateTime2).Value = processList[i].TD_TIME;
dataReader.Close();
dataReader = command.ExecuteReader();
}
connection.Close();
}
catch (SqlException ex)
{
//--Handle Exception
}
I'm familiar with fetching the data using dapperbut this is my first try using insert query.
我熟悉使用dapper获取数据，但这是我第一次尝试使用insert query。
I tried the below code, using Exceutelinked to query but stuck up with looping; I think using dapper tool, there is no need for looping statement.
我尝试了下面的代码，使用Exceute链接到查询但坚持循环；我认为使用 dapper 工具，不需要循环语句。
connection.Execute(processQuery ... );
EDIT:
编辑：
class ProcessLog
{
public int ID { get; set; }
public DateTime ST_TIME { get; set; }
public DateTime ED_TIME { get; set; }
public DateTime TD_TIME { get; set; }
public string frequency { get; set; }
}
Please advice on this. FYI:I'm using SQL Server 2008.
请就此提出建议。 仅供参考：我正在使用SQL Server 2008.
采纳答案by Haney
You'd have to do it a little differently. In Dapper, it matches on convention AKA property or field names being identical to SQL parameters. So, assuming you had a MyObject:
你必须做一些不同的事情。在 Dapper 中，它匹配与 SQL 参数相同的约定 AKA 属性或字段名称。所以，假设你有一个MyObject：
public class MyObject
{
public int A { get; set; }
public string B { get; set; }
}
And assuming processList = List<MyObject>, You'd want to do this
并假设processList = List<MyObject>，你想这样做
foreach (var item in processList)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, item);
}
Note that the MyObjectproperty names A and B match the SQL parameter names @A and @B.
请注意，MyObject属性名称 A 和 B 与 SQL 参数名称 @A 和 @B 匹配。
If you don't want to rename objects, you can use anonymous types to do the mappings instead of concrete types:
如果不想重命名对象，可以使用匿名类型而不是具体类型来进行映射：
foreach (var item in processList)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, new { A = item.A, B = item.B });
}
EDIT:
编辑：
Per Marc Gravell's comment, you can also have Dapper do the loop for you:
根据 Marc Gravell 的评论，您还可以让 Dapper 为您执行循环：
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, processList);
回答by Thalles Noce
I believe the bulk insert is better than iterate the list and insert one by one.
我相信批量插入比迭代列表并一一插入要好。
SqlTransaction trans = connection.BeginTransaction();
connection.Execute(@"
insert PROCESS_LOGS(Id, st_Time, ed_Time, td_Time)
values(@Id, @st_Time, @ed_Time, @td_Time)", processList, transaction: trans);
trans.Commit();
Reference: https://stackoverflow.com/a/12609410/1136277
回答by Yaroslav Voznyy
Use Dapper.Contrib https://dapper-tutorial.net/insert#example---insert-single! You can effortlessly insert list of objects in db.
使用 Dapper.Contrib https://dapper-tutorial.net/insert#example---insert-single！您可以毫不费力地在 db 中插入对象列表。
