Insert or update many rows in one call (bulk)
Run one query for many rows in one call, such as a bulk insert or a batch of updates, with a collection method.
Use this when your code holds many items, such as new users, and each item needs the same insert or update. Use it too when a failure must name the item that the database refused.
Insert many rows in one call
-- name: AddUser -> one
-- generator: collection-method
INSERT INTO users (name, email)
VALUES (@name, @email)
RETURNING id;
[SqlSourceGenerate]
public partial class UserRepository(NpgsqlDataSource dataSource);
// elsewhere
var newUsers = signups.Select(signup => new AddUserParams(signup.Name, signup.Email));
AddUserDto[] added = await users.AddUser(newUsers, cancellationToken);
What you get. -- generator: collection-method gives AddUser a second method. It takes the input model of every user, and adds them all in one transaction of its own. It returns their ids in the order of the users.
The rules you need.
- On SQL Server, the same query runs with
OUTPUT inserted.idbeforeVALUES, in place ofRETURNING id. See collection methods. - To run the method in a transaction that your code began, see The transaction it runs in, below, and transactions.
- To find the user that the database refused, see When one item fails, below, and
CollectionItemException.
The transaction it runs in
Read this to know which transaction a collection method runs in, or to run it in a transaction of your own.
-- name: AddUser -> one
-- generator: collection-method
INSERT INTO users (name, email)
VALUES (@name, @email)
RETURNING id;
// At the output methods
await connection.OpenAsync(cancellationToken);
await using var transaction = await connection.BeginTransactionAsync(cancellationToken);
AddUserDto[] added = await connection.AddUser(newUsers, transaction, cancellationToken: cancellationToken);
// Run the other queries of the transaction here, each with the transaction.
await transaction.CommitAsync(cancellationToken);
What you get. AddUser adds every user in your transaction. It neither commits the transaction nor rolls it back: your code does.
The rules you need.
- If you give it no transaction, a collection method begins its own. It commits it after the last item, and rolls it back if anything throws, so no item is left. See Transactions.
- To set the isolation level of that transaction, write it on the same line:
-- generator: collection-method transaction=serializable.transaction=offbegins no transaction, so a failure leaves the items before it. Seetransaction. - At the output
methods, the method uses the transaction that you give it, whatevertransactionsays. After an exception, what ran stays in your transaction, unless a database error aborted the transaction or rolled it back. On PostgreSQL, every database error aborts the transaction, so roll it back. See Transactions. - If a
TransactionScopeis open, the method begins no transaction, and runs in the scope's. This is how to put a repository's collection method, which takes no transaction, in a larger one. A connection that is not in the scope, such as one opened before it, runs the items in no transaction at all. See Transactions and timeouts.
When one item fails
Read this to find the item that the database refused, such as a user whose row breaks a constraint.
try
{
await users.AddUser(newUsers, cancellationToken);
}
catch (CollectionItemException exception)
{
// The database refused one user, so no user was added.
Console.WriteLine($"User {exception.ItemIndex} was refused: {exception.InnerException?.Message}");
}
What you get. The method's own transaction is rolled back, so no user is added. ItemIndex tells you which user the database refused.
The rules you need.
- A database error at an item is thrown as
CollectionItemException. ItsItemIndexis the position of the item, counted from 0, and itsInnerExceptionis the driver's exception, aPostgresExceptionor aSqlException. See What it throws. - It derives from
DbException, and itsSqlStateandIsTransientare those of its inner exception. So code that catchesDbException, or a retry policy that testsIsTransient, works unchanged. SeeCollectionItemException. - Only a database error at an item is wrapped. An exception of the method's own, such as
UnexpectedRowCountException, is thrown as it is. So is an error that no item raised, such as a deferred constraint that the commit checks. See What it throws. - The items run 1,000 at a time, so a bad item is found only after the round trips before its own. In your transaction, or under
transaction=off, the items of those round trips stay. See Collection methods.
If it does not work
NotSupportedException, "Specified method is not supported.", or a database error that names no item. The connection wraps the driver's connection. Give the method the driver's own connection. See the limit.TransactionRolledBackException, on SQL Server. You gave the method a transaction, and a later error of the query rolled it back. The first error is itsInnerException, such as theCollectionItemExceptionof the item that failed first. Begin another transaction to go on. SeeTransactionRolledBackException.- SQLSRC297.
transactionnames an isolation level that the database does not have. Write a level it has. See SQLSRC297.