在foreach循环中插入列表时如何处理事务
本文关键字:何处理 处理 事务 循环 foreach 插入 列表 插入列 | 更新日期: 2023-09-27 18:06:10
我在我的DAL中有这个方法,它将列表中的所有对象插入到foreach循环中的表中。但是问题是处理事务的方式是,如果列表中的任何项不能插入,那么整个过程应该回滚。有办法处理这个问题,还是我必须改变方法?
public bool InsertEarnings(List<Earning> earningsList)
{
using (SqlConnection sqlConnection = new SqlConnection(db.GetConnectionString))
{
string insertStatement = "IF NOT EXISTS (SELECT * FROM SalaryTrans WHERE employee_id=@employee_id) " +
"BEGIN INSERT INTO salaryTrans... " +
"ELSE BEGIN UPDATE SalaryTrans SET" ;
using (SqlCommand sqlCommand = new SqlCommand(insertStatement, sqlConnection))
{
SqlParameter paramEmployeeID = new SqlParameter("@employee_id", SqlDbType.Char);
SqlParameter paramWorDays = new SqlParameter("@work_days", SqlDbType.Int);
//
sqlCommand.Parameters.Add(paramEmployeeID);
sqlCommand.Parameters.Add(paramWorDays);
//
sqlConnection.Open();
foreach (Earning earning in earningsList)
{
paramEmployeeID.Value = earning.EmployeeID;
paramWorDays.Value = earning.WorkDays;
//
sqlCommand.ExecuteNonQuery();
}
return true;
}
}
}
看这个示例,在表中coach所有字段nvarchar(50)IdCoche是PrimaryKey,表为空
private void button1_Click(object sender, EventArgs e)
{
//Reading conection from App Settings
//this insert values from 20 to 29
ExecuteSqlTransaction(Settings.Default.Conexion,20);
//this must insert values from 15 to 24
//But at 20 a PrimaryKey infraction raise exception and rollback
ExecuteSqlTransaction(Settings.Default.Conexion, 15);
}
private static void ExecuteSqlTransaction(string connectionString,int start)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "INSERT INTO [dbo].[Coches] ([IdCoche],[Marca],[Modelo],[Version]) VALUES (@IdCoche,@Marca,@Modelo,@Version)";
command.Parameters.AddRange(new SqlParameter[]{
new SqlParameter("@IdCoche",""),
new SqlParameter("@Marca",""),
new SqlParameter("@Modelo",""),
new SqlParameter("@Version","")
});
try
{
for (int i = start; i < start + 10; i++)
{
command.Parameters["@IdCoche"].Value = "IdCoche"+i.ToString();
command.Parameters["@Marca"].Value = "Marca" + i.ToString(); ;
command.Parameters["@Modelo"].Value = "Modelo" + i.ToString(); ;
command.Parameters["@Version"].Value = "Version" + i.ToString(); ;
command.ExecuteNonQuery();
}
// Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("10 records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
你所做的是非常低效的,因为你正在为"if"做查询。
如果每个员工只能有一条记录,那么字段employee_id应该是唯一的,并且,如果您将查询替换为"INSERT INTO ...."ON DUPLICATE KEY UPDATE…"将会更快(取决于你的记录计数),并且在不能插入一行时不会中断。
在这个链接中,他们描述了一些看起来像你正在尝试做的事情。而不是使用SqlCommand
他建议使用SqlTransaction
这样你可以运行你的插入,如果所有成功使用transaction.Commit
否则transaction.Rollback
希望有帮助
如果你使用c#代码发起的事务,它将触发MSDTC,这有很多开销,应该尽可能避免。如果有机会,请使用一个存储过程,它接受一个表参数,使用SQL Command构建和提交表,并在数据库中执行事务控制,而不是使用分布式事务协调器会话。我还建议使用MERGE语法。