Handling nested try catch blocks in c#

Question

So let's suppose that I have the following method, for doing some complex sql operation:

private async Task ComplexSqlOperation(){
    using var connection = new IDbConnection();
    connection.Open();
    using var transaction = connection.BeginTransaction();
    try{
        string query = ""; //Simple sql query(e.g. inserting a new row into a table).
        await connection.ExecuteAsync(query, transaction);
    await ExecuteSimpleSqlOperation(connection, transaction);
        transaction.Commit();
    }catch(Exeption){
        transaction.Rollback();
    }
}

private async Task ExecuteSimpleSqlOperation(IDbConnection connection, IDbTransaction transaction){
    string query = ""; //Simple sql query (e.g. adding a unique constraint on a column).
    try{
        await connection.ExecuteAsync(query, transaction)
    }catch(Exception){
        transaction.Rollback();
    }
}

Suppose an error occurs in the nested method 'ExecuteSimpleSqlOperation', the transaction will be rolled back, and the code will continue to execute, then the parent callerwill try to commit the transaction (which was just rolled back) => another exception will occur, then the parent catch block will try to roll back the transaction again, and an exception here is raised too, since transaction is already completed. I found some related questions, but here I'll be asking for the best practices to follow.

• Is it worth it, catching in nested methods, for future use of methods from other teammates ?
• Is there a way to abort process of parent caller, after catching an exception in a child method(like in my case) ?

Answer 1

Firstly, you don't need to explicitly catch and rollback. As long as you have a using the rollback will happen automatically.

Furthermore, ExecuteSimpleSqlOperation doesn't need to rollback anything. The transaction is not its business. If any exceptions happen they should bubble up to the outer function, which handles the rollback with its using. If there was no ambient transaction then the statement itself will always be aborted anyway.

You are also missing some awaits.

private async Task ComplexSqlOperation()
{
    await using var connection = GetDbConnection();
    await connection.OpenAsync();
    await using var transaction = await connection.BeginTransactionAsync();
    const string query = @"
"; //Simple sql query(e.g. inserting a new row into a table).
    await connection.ExecuteAsync(query, transaction);
    await ExecuteSimpleSqlOperation(connection, transaction);
    await transaction.CommitAsync();
}

private async Task ExecuteSimpleSqlOperation(DbConnection connection, IDbTransaction transaction = null)
{
    string query = ""; //Simple sql query (e.g. adding a unique constraint on a column).
    await connection.ExecuteAsync(query, transaction);
}