Table of Contents

Run queries in a transaction (or with a timeout)

At the end of this page, two queries run in one transaction, with a commit or a rollback, and a query has its own timeout.

Run queries in a transaction

A repository method takes no transaction, so you cannot give it yours. Whether it still runs in a transaction of yours depends on its connection and its database. Give each query that must run in the transaction the line -- output: methods. The method of such a query takes your connection and your transaction. By default, it is an internal extension method of the connection.

The sample is a second repository of the same application, AccountRepository. It reads a table accounts with the columns id, owner and balance. Its queries are in AccountRepository.sql, beside the class:

-- name: GetAccount -> one-optional
SELECT id, owner, balance FROM accounts WHERE id = @accountId;

-- name: GetBalance -> one
-- output: methods
SELECT balance FROM accounts WHERE id = @accountId FOR UPDATE;

-- name: SetBalance -> none
-- output: methods
UPDATE accounts SET balance = @balance WHERE id = @accountId;
using Npgsql;
using SqlSource;

[SqlSourceGenerate]
public partial class AccountRepository(NpgsqlDataSource dataSource)
{
    public async Task Withdraw(int accountId, decimal amount, CancellationToken cancellationToken = default)
    {
        await using var connection = await dataSource.OpenConnectionAsync(cancellationToken);
        await using var transaction = await connection.BeginTransactionAsync(cancellationToken);

        var account = await connection.GetBalance(
            new GetBalanceParams(accountId), transaction, cancellationToken: cancellationToken);
        await connection.SetBalance(
            new SetBalanceParams(account.Balance - amount, accountId), transaction, cancellationToken: cancellationToken);

        await transaction.CommitAsync(cancellationToken);
    }
}

// Adds Withdraw to the generated interface, beside GetAccount
public partial interface IAccountRepository
{
    Task Withdraw(int accountId, decimal amount, CancellationToken cancellationToken = default);
}

Withdraw is a method of your own on the repository. It opens a connection, begins the transaction, calls the two methods and commits. If a method throws before the commit, await using disposes the transaction, and the driver rolls it back: neither change is kept.

  • Give every method the transaction. On SQL Server, a method that does not get it throws InvalidOperationException.
  • The partial interface adds Withdraw to IAccountRepository, so code that takes the interface can call it.

Set a command timeout

A repository method runs with the driver's default timeout. If a query needs a timeout of its own, give it -- output: methods in the same way. Its method takes the argument commandTimeout, in seconds:

public async Task<decimal> GetBalanceSlowly(int accountId, CancellationToken cancellationToken = default)
{
    await using var connection = await dataSource.OpenConnectionAsync(cancellationToken);
    var account = await connection.GetBalance(
        new GetBalanceParams(accountId), commandTimeout: 60, cancellationToken: cancellationToken);
    return account.Balance;
}

The method is part of AccountRepository. A method of -- output: methods opens a closed connection itself, and closes it again before it returns.

One file, two outputs

The output of a query says what SqlSource generates for it. The default output, repository, gives a method of the repository, which opens its own connection. The output methods gives a method that takes your connection. One file can hold queries of both outputs: GetAccount stays a method of the repository.

If it does not work

  • InvalidOperationException on SQL Server, about a pending local transaction: give the method the transaction.
  • SQLSRC241 for the new queries: describe them, as Your first query shows.

What you have now

A repository method of your own that runs two queries in one transaction, and a query with its own command timeout.

To insert many rows in one call and one transaction, see Insert or update many rows in one call (bulk). To compare all the outputs, see Choose what is generated (output modes).

Rules: transactions and timeouts, the connection, the signature, where the methods go, the output, the interface.

Next: Test code that uses a repository