SQL Server ADO.NET事务

  #region 事务
/// <summary>
/// 事务
/// </summary>
/// <param name="sqlList">SQL语句list</param>
/// <returns></returns>
        public static int UpdateByTransaction(List<string>sqlList)
        {
            //开启事务
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                int result = 0;
                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();//回滚事务（意思就是撤销sql语句执行）并且自动清楚事务

                }
                throw  new Exception("执行 UpdateByTransaction(List<string>sqlList)方法出错"+ex.Message);
            }
            finally
            {
                if(cmd.Transaction!=null)
                {
                    cmd.Transaction = null;//清楚事务
                }
                conn.Close();
            }

      
        }
        #endregion

在sqlhelper中增加方法

然后在主函数中测试

  #region 测试事务
            List<string> sqlList = new List<string>()
            {
                "insert into StudentClass (ClassName)values(N'WPF-1班')",
                    "insert into StudentClass (ClassName)values(N'WPF-2班')",
                    "insert into StudentClass (ClassName)values(N'WPF-3班')",
                    "insert into StudentClass (ClassName)values(N'WPF-4班')",
            };
            int result=SQLHelper.UpdateByTransaction(sqlList);
            Console.WriteLine("执行成功" + result + "SQL语句");

            #endregion

测试结果刷新学生表