ExecuteNonQueryAsync并在SQL事务中提交 [英] ExecuteNonQueryAsync and commit in a SQL Transaction

问题描述

在获得有关我创建的一段代码的帮助之后，我正在尝试在事务内通过c#进行Async SQL调用，例如，我可能正在更新或删除表中的行.

I am after some help with a piece of code I have created, I am attempting to make an Async SQL call from c# within a transaction, for example I might be updating or deleting rows from a table.

到目前为止，这是我所拥有的，但是根据我在这里所掌握的信息以及到目前为止我所了解的，我似乎找不到很多有关在事务中执行此操作的信息，我相信它可能会尝试在命令之前提交事务如果该命令很耗时，则说明已完全完成.如果您能提出建议/为我提供示例，我将不胜感激.

This is what I have so far, but I cannot seem to find much information on doing this in a transaction, from what I have here and what I understand so far, I believe it may attempt to commit the transaction before the command has fully completed if the command is time-consuming. If you could advise / point me to an example I would really appreciate it.

var sqlQuery = "delete from table";
        using (var connection = new SqlConnection(ConnectionString))
        {
            await connection.OpenAsync();
            using (var tran = connection.BeginTransaction())
            {
                using (var command = new SqlCommand(sqlQuery, connection, tran))
                {
                    await command.ExecuteNonQueryAsync();
                    tran.Commit();
                }
            }
        }

谢谢

推荐答案

您的代码看起来不错.我只是在命令的执行中添加了一个try/catch，这样您可以在事务失败时回滚事务.因为您在 await命令行上使用 await 关键字.ExecuteNonQueryAsync(); 的执行将阻塞，直到该方法返回为止，而不管它花了多长时间(除非超时)命令本身的异常，在这种情况下，您应该将命令的执行超时设置得更高一些，或者弄清楚为什么它花了这么长的时间).

Your code looks good. I just added a try/catch on the execution of the command so you can roll back your transaction if it fails. Because you are using the await keyword on the line await command.ExecuteNonQueryAsync(); execution will block until the method returns regardless of how long it takes (unless you get a timeout exception from the command itself in which case you should set the command's execution timeout higher or figure out why its taking so long).

    var sqlQuery = "delete from table";
    using (var connection = new SqlConnection(ConnectionString))
    {
        await connection.OpenAsync();
        using (var tran = connection.BeginTransaction())
        using (var command = new SqlCommand(sqlQuery, connection, tran))
        {
            try {
                await command.ExecuteNonQueryAsync();
            } catch {
                tran.Rollback();
                throw;
            }
            tran.Commit();
        }
    }

这篇关于ExecuteNonQueryAsync并在SQL事务中提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！