如何使用 Dapper.NET 向数据库插入 C # 列表

使用衣冠楚楚，如何向数据库插入 C# List。以前没有 衣冠楚楚我使用以下代码的 将 List 值插入到 database 。

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
}

我熟悉使用 衣冠楚楚获取数据，但这是我第一次尝试使用 插入查询。

我尝试了下面的代码，使用 Exceute链接到查询，但坚持使用循环; 我认为使用适配器工具，没有必要使用循环语句。

connection.Execute(processQuery ... );

编辑:

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

请对此提出建议。我正在使用 SQL Server 2008。

124604

小开

最佳答案

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:

public class MyObject
{
public int A { get; set; }


public string B { get; set; }
}

And assuming processList = List<MyObject>, You'd want to do this

foreach (var item in processList)
{
string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, item);
}

Note that the MyObject property names A and B match the SQL parameter names @A and @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:

string processQuery = "INSERT INTO PROCESS_LOGS VALUES (@A, @B)";
connection.Execute(processQuery, processList);

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