SqlHelper 类方法中启用事务
1 public static int UpdateByTran(List<string> sqlList) 2 { 3 SqlConnection conn = new SqlConnection(connString); 4 SqlCommand cmd = new SqlCommand(); 5 cmd.Connection = conn; 6 try 7 { 8 conn.Open(); 9 cmd.Transaction = conn.BeginTransaction();//开启事务10 int result = 0;11 foreach (string sql in sqlList)12 {13 cmd.CommandText = sql;14 result += cmd.ExecuteNonQuery();15 }16 cmd.Transaction.Commit();//提交事务17 return result;18 }19 catch (Exception ex)20 {21 //写入日志...22 if (cmd.Transaction != null)23 cmd.Transaction.Rollback();//回滚事务24 throw new Exception("调用事务更新方法时出现异常:" + ex.Message);25 }26 finally27 {28 if (cmd.Transaction != null)29 cmd.Transaction = null;//清除事务30 conn.Close();31 }32 }
调用
1 static void Main(string[] args) 2 { 3 List<string> sqlList = new List<string>() 4 { 5 "delete from ScoreList where StudentId=100013", 6 "delete from ScoreList where StudentId=100014", 7 "delete from ScoreList where StudentId=100011", 8 9 "delete from Students where StudentId=100010",10 "delete from Students where StudentId=100013", 11 "delete from Students where StudentId=100014", 12 "delete from Students where StudentId=100011",13 };14 string sql = "select count(*) from Students";15 Console.WriteLine("删除前学生总数:{0}", SQLHelper.GetSingleResult(sql).ToString());16 Console.WriteLine("------------------------------------------------------------");17 int result = 0;18 try19 {20 result = SQLHelper.UpdateByTran(sqlList);21 }22 catch (Exception ex)23 {24 Console.WriteLine(ex.Message);25 Console.WriteLine("------------------------------------------------------------");26 }27 if (result > 0)28 Console.WriteLine("删除成功!");29 else30 Console.WriteLine("删除失败!");31 Console.WriteLine("------------------------------------------------------------");32 Console.WriteLine("删除后学生总数:{0}", SQLHelper.GetSingleResult(sql).ToString());33 Console.ReadLine();34 }