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.

BeginTransactionAsync has an overload that takes an IsolationLevel. The default isolation level is provider-specific, but you can also override it with the DefaultIsolationLevel connector setting.

tip

If you are using MuchAdo.Sqlite, there are additional overloads that include the deferred parameter for creating deferred transactions.

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.

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!