Azure中ExecuteSqlCommand的事务

本文关键字：事务 ExecuteSqlCommand Azure | 更新日期: 2023-09-27 18:00:53

我正在使用带有Azure Sql数据库的EF 6。根据微软的说法，不支持用户发起的交易(参考：https://msdn.microsoft.com/en-us/data/dn307226#transactions)

现在，使用EF6，默认情况下ExecuteSqlCommand被封装在事务中：

从EF6数据库开始。默认情况下，ExecuteSqlCommand((会将命令包装在事务中(如果还没有(。(参考：https://msdn.microsoft.com/en-us/data/dn456843.aspx)

在我的场景中，我是否应该始终抑制ExecuteSqlCommand事务行为，如下所示：

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, @"TRUNCATE TABLE Person;");

Azure中ExecuteSqlCommand的事务

您所指的此语句仅适用于重试策略：

当您配置了导致重试的执行策略时。。。

您链接到的文章不是Azure特有的。Azure SQL数据库支持事务。

是否要使用TransactionalBehavior.DoNotEnsureTransaction取决于您是否希望在命令作用域中存在事务。只有当批处理中有多个T-SQL语句时，这才是相关的(据我所知(。

换句话说，如果您的执行策略有重试次数，并且希望在一个事务中执行多个语句，那么它们必须都在一个批处理中，如下所示。

为了使事务跨越多个批次，必须使用db.Database.BeginTransaction创建它。您链接的文档解释说，正是这种明确的BeginTransaction不允许与重试结合使用。不管重试策略如何，都允许TransactionalBehavior.EnsureTransaction创建的事务(因为它完全由EF管理(。

// INSERT is rolled back due to error
context.Database.ExecuteSqlCommand(
    TransactionalBehavior.EnsureTransaction,
    @"INSERT INTO MyTable (i) VALUES (1)
    RAISERROR('This exception was intentionally thrown', 16, 1)");
// INSERT is committed
context.Database.ExecuteSqlCommand(
    TransactionalBehavior.DoNotEnsureTransaction,
    @"INSERT INTO MyTable (i) VALUES (1)
    RAISERROR('This exception was intentionally thrown', 16, 1)");

测试程序如下。

    private static void Main(string[] args)
    {
        //c:>sqlcmd -E
        //1> create database EFTransaction
        //2> go
        //1> use EFTransaction
        //2> go
        //Changed database context to 'EFTransaction'.
        //1> create table MyTable (i int primary key)
        //2> go
        const string connectionString = "Server=(local);Database=EFTransaction;Integrated Security=SSPI";
        using (DbContext context = new DbContext(connectionString))
        {
            context.Database.ExecuteSqlCommand(
                TransactionalBehavior.DoNotEnsureTransaction,
                @"IF EXISTS (SELECT * FROM sys.tables where name = 'MyTable')
                    DROP TABLE [dbo].[MyTable]
                CREATE TABLE MyTable (i INT PRIMARY KEY)");
        }
        Console.WriteLine("Insert one row."); 
        using (DbContext context = new DbContext(connectionString))
        {
            context.Database.ExecuteSqlCommand(
                TransactionalBehavior.EnsureTransaction,
                @"INSERT INTO MyTable (i) VALUES (0)");
            // Notice that there is no explicit COMMIT command required.
        }
        // Sanity check in a different connection that the row really was committed
        using (DbContext context = new DbContext(connectionString))
        {
            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 1
        }
        Console.WriteLine();
        Console.WriteLine("Insert one row and then throw an error, all within a transaction.");
        Console.WriteLine("The error should cause the insert to be rolled back, so there should be no new rows");
        using (DbContext context = new DbContext(connectionString))
        {
            try
            {
                context.Database.ExecuteSqlCommand(
                    TransactionalBehavior.EnsureTransaction,
                    @"INSERT INTO MyTable (i) VALUES (1)
                    RAISERROR('This exception was intentionally thrown', 16, 1)");
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 1
        }
        Console.WriteLine();
        Console.WriteLine("Insert one row and then throw an error, all within a transaction.");
        Console.WriteLine("The error will not cause the insert to be rolled back, so there should be 1 new row");
        using (DbContext context = new DbContext(connectionString))
        {
            try
            {
                context.Database.ExecuteSqlCommand(
                    TransactionalBehavior.DoNotEnsureTransaction,
                    @"INSERT INTO MyTable (i) VALUES (1)
                    RAISERROR('This exception was intentionally thrown', 16, 1)");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            int rows = context.Database.SqlQuery<int>(
                "SELECT COUNT(*) FROM MyTable").Single();
            Console.WriteLine("Rows: {0}", rows); // Rows: 2
        }
    }
相关文章：