Transactions

To start a database transaction, call BeginTransactionAsync on the connector. MuchAdo calls the corresponding method on the ADO.NET connection to get an IDbTransaction, which it stores with the connector.

To commit the transaction, call CommitTransactionAsync on the connector, which commits and then disposes the stored transaction.

await using (await connector.BeginTransactionAsync())
{
    var existingWidgetId = await connector
        .CommandFormat($"select id from widgets where name = {name}")
        .QuerySingleOrDefaultAsync<long?>();
    widgetId = existingWidgetId ?? await connector
        .CommandFormat(
            $"insert into widgets (name, height) values ({name}, {height})")
        .Command("select last_insert_id()")
        .QuerySingleAsync<long>();

    await connector.CommitTransactionAsync();
}

When the object returned from BeginTransactionAsync is disposed, the transaction is disposed, which rolls back the transaction if it has not been committed, e.g. if an exception is thrown.

info

ADO.NET requres that the Transaction property of IDbCommand be set to the current transaction. MuchAdo takes care of that automatically when creating and executing commands.

You can explicitly roll back the current transaction with RollbackTransactionAsync, but that's not typically necessary, since an uncommitted transaction will be rolled back when it is disposed.

Auto-commit transactions

To automatically commit the database transaction, you can use ExecuteInTransactionAsync:

widgetId = await connector.ExecuteInTransactionAsync(async () =>
{
    var existingWidgetId = await connector
        .CommandFormat($"select id from widgets where name = {name}")
        .QuerySingleOrDefaultAsync<long?>();
    return existingWidgetId ?? await connector
        .CommandFormat(
            $"insert into widgets (name, height) values ({name}, {height})")
        .Command("select last_insert_id()")
        .QuerySingleAsync<long>();
});

To wrap an auto-commit transaction around a command batch, chain a call to InTransaction before executing the command.

Transaction settings

The transaction methods have overloads that accept a DbTransactionSettings, which is typically used to specify the transaction isolation level. Feel free to use an IsolationLevel directly; it will be implicitly converted to DbTransactionSettings.

The default isolation level is provider-specific, but you can override it with the DefaultTransactionSettings connector setting.

tip

If you are using MuchAdo.Sqlite, you can use SqliteDbTransactionSettings to create deferred transactions.

ADO.NET access

You can attach an existing IDbTransaction to the connector by calling AttachTransaction.

If you need to access the IDbTransaction that is stored by the connector, use the Transaction property. If there is no active transaction, the property will be null.

tip

You can use the Transaction property to create savepoints. If you would like MuchAdo to support savepoints directly, please create an issue!