Call your queries
At the end of this page, your application gets the repository by dependency injection, calls its methods with a cancellation token, and reads their models (DTOs).
The repository and its connection
You pass no connection to a method of the repository. Each method opens a connection, runs its query and disposes the connection. It gets the connection from the member of the class:
[SqlSourceGenerate]
public sealed partial class UserRepository(NpgsqlDataSource dataSource);
Each call opens a connection from the data source.
Register it
Register the repository as its interface, IUserRepository, which the build generated. In Program.cs of an ASP.NET Core application:
builder.Services.AddSingleton(NpgsqlDataSource.Create(builder.Configuration.GetConnectionString("App")!));
builder.Services.AddSingleton<IUserRepository, UserRepository>();
A class of your application then takes IUserRepository in its constructor. A test can give that class a fake, as Test code that uses a repository shows.
The models
For each query, the build generates a record for its query parameters and a record for its rows. For GetUser, they are:
public sealed record GetUserParams(int UserId);
public sealed record GetUserDto(int Id, string Name, string Email);
- The parameters model has one property for each query parameter. A query with no parameters, such as
ListUserNames, has none. - The row model has one property for each column. A property is nullable when its column can be null.
- A query that reads every column of a table, in the table's order, gets a row model named after the table, such as
UsersDto.
What each method returns
The result shape of each query decides what its method returns:
GetUserDto? user = await users.GetUser(new GetUserParams(1));
ListUserNamesDto[] names = await users.ListUserNames();
int renamed = await users.RenameUser(new RenameUserParams("Ada Lovelace", 1));
GetUser,-> one-optional: the row, or null.ListUserNames, with no shape: every row, in an array.RenameUser,-> rowcount: the number of rows changed.
Cancel a call
Each method takes a CancellationToken as its last argument. It cancels opening the connection, running the query and reading the rows. Pass on the token that your code gets:
app.MapGet("/users/{id}", async (int id, IUserRepository users, CancellationToken cancellationToken) =>
await users.GetUser(new GetUserParams(id), cancellationToken));
When a row is missing
GetUser is one-optional, so it returns null when no user has the id. Test for null:
GetUserDto? user = await users.GetUser(new GetUserParams(42));
if (user is null)
{
// No user has the id 42.
}
A query whose shape is one throws UnexpectedRowCountException when it returns no row. Both one and one-optional throw it when the query returns more than one row.
If it does not work
GetUserDtois not found: your table has only the query's columns, in its order, so the model isUsersDto.- A method is missing, and the build reports SQLSRC241 or SQLSRC244: describe the query, as Your first query shows.
What you have now
An application that gets IUserRepository from dependency injection, calls its methods, and reads their models.
Rules: repository methods, the connection, the interface, model names, table models, what each shape returns, the signature, UnexpectedRowCountException. Guides: names with Async, methods for code that does not await, another way to give a repository its connection.