7. sql server事务

数据库事务操作

#region 事务
        /// <summary>
        /// 事务: 显示事务和隐式事务
        /// 显示事务：手动操作的
        /// 隐式事务：平常的一条SQL语句
        /// </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;
                //提交事务
                //循环提交sql语句
                foreach (string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    result += cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交数据库事物
                return result;
            }
            //回滚事务
            catch (Exception ex)
            {
                if (cmd.Transaction!=null)
                {
                    //回滚事务（撤销所有的sql语句执行）并且自动清除事务
                    cmd.Transaction.Rollback();
                }
                throw new Exception("执行UpdateByTransaction(List<string> sqlList)方法时出错：" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction = null;//清除事务
                }
                //关闭连接
                conn.Close();
            }
        }
        #endregion

数据库事务调用

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