CODEWITHSUNDEEP
c#ado.net
David B
David B

Reputation: 929

ExecuteNonQueryAsync and commit in a SQL Transaction

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();
                }
            }
        }

Thanks,

Upvotes: 11

Views: 28473

Answers (1)

Igor
Igor

Reputation: 62228

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();
        }
    }

Upvotes: 16

Related Questions