ado.net 中事务的使用

时间:2021-12-03 01:31:09

SqlHelper 类方法中启用事务

 public static int UpdateByTran(List<string> sqlList)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();//开启事务
int result = ;
foreach (string sql in sqlList)
{
cmd.CommandText = sql;
result += cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//提交事务
return result;
}
catch (Exception ex)
{
//写入日志...
if (cmd.Transaction != null)
cmd.Transaction.Rollback();//回滚事务
throw new Exception("调用事务更新方法时出现异常:" + ex.Message);
}
finally
{
if (cmd.Transaction != null)
cmd.Transaction = null;//清除事务
conn.Close();
}
}

调用

 static void Main(string[] args)
{
List<string> sqlList = new List<string>()
{
"delete from ScoreList where StudentId=100013",
"delete from ScoreList where StudentId=100014",
"delete from ScoreList where StudentId=100011", "delete from Students where StudentId=100010",
"delete from Students where StudentId=100013",
"delete from Students where StudentId=100014",
"delete from Students where StudentId=100011",
};
string sql = "select count(*) from Students";
Console.WriteLine("删除前学生总数:{0}", SQLHelper.GetSingleResult(sql).ToString());
Console.WriteLine("------------------------------------------------------------");
int result = ;
try
{
result = SQLHelper.UpdateByTran(sqlList);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("------------------------------------------------------------");
}
if (result > )
Console.WriteLine("删除成功!");
else
Console.WriteLine("删除失败!");
Console.WriteLine("------------------------------------------------------------");
Console.WriteLine("删除后学生总数:{0}", SQLHelper.GetSingleResult(sql).ToString());
Console.ReadLine();
}