Call a query without await, or stream rows
Call a query from synchronous code with a sync method, or read a large result one row at a time as an IAsyncEnumerable.
Use this when code that cannot await, such as a command of a console tool, must run a query. Use it too when a result is too large to hold in an array.
Run a query from code that does not await
-- output: methods
-- name: GetUserName -> one-optional
-- generator: method-type=both
SELECT name
FROM users
WHERE id = @id;
// A command of a console tool, whose API is synchronous
public int Run(int id)
{
using var connection = dataSource.OpenConnection();
GetUserNameDto? user = connection.GetUserName(new GetUserNameParams(id));
Console.WriteLine(user?.Name ?? "no such user");
return user is null ? 1 : 0;
}
// Elsewhere, code that awaits
GetUserNameDto? user = await connection.GetUserNameAsync(new GetUserNameParams(id), cancellationToken: cancellationToken);
What you get. -- generator: method-type=both gives the query two methods. The synchronous method, GetUserName, returns the row or null, and takes no cancellation token. Beside it, GetUserNameAsync is the asynchronous method.
// The same query at the default output, repository, without -- output: methods
public int Run(int id)
{
GetUserNameDto? user = users.GetUserName(new GetUserNameParams(id));
Console.WriteLine(user?.Name ?? "no such user");
return user is null ? 1 : 0;
}
A repository gets the same methods, and its interface a member for each, so users can be the interface.
The rules you need.
method-type=syncgives the query the synchronous method alone. See synchronous methods.- A query with
collection-methodgets its collection method the same way: synchronous, asynchronous or both. - The line can reach more than one query. See generator parameters for where to write it.
- On PostgreSQL, run a synchronous method on a data source without
Multiplexing=true. See what a method reads and throws. - A repository method gets its connection from the repository. See synchronous repository methods.
- If the only connection of a repository is a method that returns a task, mark a second one that returns none. See a connection from a service of your own.
Read rows one at a time (IAsyncEnumerable)
If a result is too large to hold in an array, stream its rows: your loop gets each row as it is read.
-- name: StreamUsersOldestFirst
-- collection-type: IAsyncEnumerable
SELECT
id,
name,
email
FROM users
ORDER BY created_at, id;
// Methods/sqlsource.json
{
"output": "methods",
}
[SqlSourceGenerate]
public sealed partial class UserQueries(DbConnection connection)
{
public async Task WriteUserNamesAsync(TextWriter writer, CancellationToken cancellationToken = default)
{
await foreach (
var user in connection.StreamUsersOldestFirst(cancellationToken: cancellationToken).ConfigureAwait(false)
)
{
await writer.WriteLineAsync(user.Name).ConfigureAwait(false);
}
}
}
What you get. At the output methods, which the folder's sqlsource.json sets, StreamUsersOldestFirst is a method of the connection that returns an IAsyncEnumerable<StreamUsersOldestFirstDto>. A repository gets the same method, users.StreamUsersOldestFirst(cancellationToken).
The rules you need.
-- collection-type: IAsyncEnumerableon a query whose shape ismanymakes its method stream them. The loop starts on the first row while the rest are still to come, and the rows are never all held at once. See Streaming rows and Collection type.- The call runs nothing. The query runs when the loop asks for the first row, and each loop over the result runs it again. See Streaming rows.
- The connection stays open until the loop ends, and is busy with the query. Run no other command on it in the body of the loop. Use another connection, or read the rows into a collection first. See Streaming rows.
- If the loop ends early, at a
breakor an exception, the method reads the rest of the rows and discards them. On a large result, that takes as long as reading them. To stop early, have the query return only the rows that the loop needs, with aWHERE,LIMITorTOP. See Streaming rows. - An error at a row, such as a null in a column whose property cannot be null, is thrown when the loop reaches that row. Your loop has then read the rows before it. See Streaming rows.
- The
cancellationTokenof the method cancels the loop, and so does a token that you give toWithCancellation. See Streaming rows. - A stream has no synchronous method. Under
method-type=both, the query keeps its stream alone. See Which queries get a method.
If it does not work
NotSupportedExceptionfrom a synchronous method, on PostgreSQL. The data source hasMultiplexing=true. Give the code that calls the method a data source without it, or call the asynchronous method. See what a method reads and throws.NpgsqlOperationInProgressException, orInvalidOperationExceptionon SQL Server, in the body of the loop. A command ran on the connection that the loop holds. Run it on another connection, or read the rows into a collection first. See Streaming rows.- The loop throws an error of the database in place of your own exception. An unread row raised the error as the loop ended. Catch your exception in the body of the loop, keep it in a variable, and
break. Then throw the kept exception, after the loop or in place of the database's exception. See Streaming rows. - SQLSRC298. The query streams its rows and has
method-type=sync. Writemethod-type=both, or a collection type that is read whole, such asList. See SQLSRC298.