Use Dapper or your own ADO.NET code
Generate only the models and the SQL constants of your queries, and run them with Dapper or your own ADO.NET code.
Use this when Dapper, or ADO.NET code of your own, runs your SQL, and you want SqlSource to give you only the models and the SQL. If SqlSource can run the queries for you, keep the default output, repository: see The four outputs.
Models for Dapper, start to end
Before you start. Add the package and Dapper in your project's folder. Install the tool once, at the root of your repository. If the repository has a tool manifest, skip dotnet new tool-manifest.
$ dotnet add package SqlSource
$ dotnet add package Dapper
$ dotnet new tool-manifest
$ dotnet tool install SqlSource.Tool
Add your database's driver too:
$ dotnet add package Npgsql
Write a query. Save it as
Models/Users.sql:-- name: GetUser -- summary: One user, every column of the table. SELECT * FROM users WHERE id = @userId;Mark a partial class, in
Models/StoreQueries.cs.Source = "."gives it every.sqlfile of its folder:using SqlSource; [SqlSourceGenerate(Source = ".")] public sealed partial class StoreQueries;Set the dialect of your database and the output. Save this as
sqlsource.json, beside your project file:
// sqlsource.json, beside your project file
{
"root": true,
"dialect": "postgres",
"output": "models",
}
For the SQL alone, set the output to sql, and go on at Only the SQL.
- Give the tool your connection. Write it in a
.envfile in your project's folder. Add.envto your.gitignore.SQLSOURCE_CONNECTIONserves a project with one database:
SQLSOURCE_CONNECTION="Host=localhost;Port=55432;Database=examples;Username=examples;Password=examples"
Describe the query. The tool asks your database about it, and writes the answer to
Models/Users.sql.json. Commit that file:$ dotnet sqlsource describe $ git add Models/Users.sql.jsonRun the query through Dapper, into its models:
// Models/StoreQueries.cs using System.Data.Common; using System.Threading.Tasks; using Dapper; using SqlSource; [SqlSourceGenerate(Source = ".")] public sealed partial class StoreQueries(DbConnection connection) { // SELECT * gives the model of the table, UsersDto, which every query that reads the whole table shares. public Task<UsersDto?> GetUserAsync(int userId) => connection.QuerySingleOrDefaultAsync<UsersDto>(Sql.GetUser, new GetUserParams(userId)); }Once, when your application starts, set
DefaultTypeMap.MatchNamesWithUnderscores = true, as Run the models through Dapper shows.
More: the tool, dialect, connections, .env, connecting to PostgreSQL and SQL Server, describe.
What you get. Once the tool has described GetUser, in step 5, the query gets its models and a constant, in place of a method that runs it. Sql.GetUser is the constant: it holds the SQL of the query. GetUserParams is the input model. UsersDto is the output model: it is the model of the table users, since the query reads the whole table. A query with a token gets a method that returns its SQL, in place of the constant.
The output models is the output of the settings file of step 3. This sample and each sqlsource.json below show that one file again, not a file of their own:
// sqlsource.json, beside your project file
{
"root": true,
"dialect": "postgres",
"output": "models",
}
Another type gets the same from its own file. Here UserRepository claims UserRepository.sql, which holds the queries GetUser and FindUsers, and runs them through Dapper:
using System.Data.Common;
using Dapper;
using SqlSource;
[SqlSourceGenerate]
public partial class UserRepository(DbConnection connection)
{
public Task<GetUserDto?> Get(int id) =>
connection.QuerySingleOrDefaultAsync<GetUserDto>(Sql.GetUser, new GetUserParams(id));
public Task<IEnumerable<FindUsersDto>> Find(string? name) =>
connection.QueryAsync<FindUsersDto>(Sql.FindUsers, new FindUsersParams(name));
}
The rules you need.
- To run the queries with Dapper or your own ADO.NET code, set the output to
models. You can also set it for one folder, one file or one query. See Output. - The constants are in a private class
Sqlof the type. They are plain strings, so any library can run them. See Constants. - By default, a query that does not read a whole table gets an output model named after the query, such as
GetUserDto. See Table models. For the names, the shape and the properties of the models, see Models.
Use the queries with Dapper or your own ADO.NET code
To run the queries of a file with Dapper or with your own ADO.NET code, set the output of the file to models:
-- output: models
-- name: GetUser
SELECT id, name FROM users WHERE id = @id;
using System.Data;
using System.Data.Common;
using Dapper;
using SqlSource;
[SqlSourceGenerate]
public partial class UserRepository(DbConnection connection)
{
// Dapper
public Task<GetUserDto> Get(int id) =>
connection.QuerySingleAsync<GetUserDto>(Sql.GetUser, new { id });
// ADO.NET
public async Task<string?> GetName(int id)
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
await using var command = connection.CreateCommand();
command.CommandText = Sql.GetUser;
var parameter = command.CreateParameter();
parameter.ParameterName = "@id";
parameter.Value = id;
command.Parameters.Add(parameter);
await using var reader = await command.ExecuteReaderAsync();
return await reader.ReadAsync() ? reader.GetString(reader.GetOrdinal("name")) : null;
}
}
What you get. Sql.GetUser is a constant, a plain string, so any library can run it. GetUserDto is the model of the rows of this GetUser, which reads two columns of users. The GetUser of step 1 reads the whole table, so its model is the model of the table, UsersDto.
The rules you need.
- Under the output
modelsorsql, a query is a constant. Underrepository, the default, ormethods, it has a method in place of the constant. So a file whose queries you run yourself sets the output tomodels. See Constants and Output. - Under
models, SqlSource opens no connection. Dapper opens a closed connection for you. ADO.NET needs the connection open. - Under
models, SqlSource binds no value. You give the database the value of each@nameyourself. See Query parameters. - ADO.NET needs no package. Dapper is a separate package, which you added before step 1.
Run the models through Dapper
Dapper reads the rows by the names of their columns. Here Dapper runs a query of orders whose output is models, and one setting lets Dapper match the columns:
-- output: models
-- name: GetOrder
SELECT o.id, o.placed_at, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = @orderId;
using System.Data.Common;
using Dapper;
using SqlSource;
[SqlSourceGenerate]
public partial class OrderRepository(DbConnection connection)
{
public Task<GetOrderDto?> Get(long orderId) =>
connection.QuerySingleOrDefaultAsync<GetOrderDto>(Sql.GetOrder, new GetOrderParams(orderId));
}
// Once, when the application starts, before the first query
DefaultTypeMap.MatchNamesWithUnderscores = true;
What you get. GetOrderParams is the input model: Dapper takes it as the parameter object. GetOrderDto is the output model: the type of a row. Sql.GetOrder is the constant. The output models gives the query no method. repository, the default, or methods would give it a method in place of the constant. See Models.
The rules you need. Each rule holds with either database.
Columns in snake_case need
DefaultTypeMap.MatchNamesWithUnderscores. For columns such ascreated_atandcustomer_name, set it totrueonce, when your application starts. Dapper matchescustomer_nametoCustomerNameonly with it. See Property names.Write the query's parameters in camelCase,
@orderIdand not@order_id, as@userIdis in step 1. Dapper sends a property of the input model, such asOrderIdofGetOrderParams, only to the parameter written as its name. It ignores case, but not_. If a query keeps@order_id, give Dapper its parameters by hand:new { order_id = orderId }. See the limit.Sorted models are classes. Dapper matches the constructor of a record to the columns by position and name. So a record whose properties are sorted no longer matches. Dapper matches a class by name:
// sqlsource.json, in the project's folder { "root": true, "dialect": "postgres", "output": "models", "generatorParameters": ["sort-output"], "outputModelType": "class", }Set the nullability of a column with a
-- column:marker, not with!or?at the end of its alias. A mark changes the name of the column: the mark stays in the name,display_name!. So Dapper does not match the column to the propertyDisplayName. For a record that it cannot make without the column, Dapper throwsInvalidOperationException. For a class, Dapper leaves the property as the class made it, with no error. The marker leaves the name alone:-- name: GetCustomerLabel -- column: display_name not null SELECT c.id, initcap(c.name) AS display_name FROM customers c WHERE c.id = @customerId;A list parameter,
IN @ids, works on SQL Server alone, and only without a null test. On SQL Server, Dapper sends one parameter for each value of a list, and a null list as an empty one. But Dapper rewrites every@idsof the query. So@ids IS NULLis an error from two values up, and it is true for an empty list, as for a null one. On PostgreSQL, Dapper sends a list as one array. Writeid = ANY(@ids)there, an array parameter.
Each database needs more, below: PostgreSQL and SQL Server. On both, a type override to a type that the driver does not give needs a Dapper type handler. On SQL Server, send a datetime2 parameter as a datetime2, since Dapper sends a DateTime as a datetime.
With PostgreSQL
On PostgreSQL, some types need more than the rules above: a type override, an enum, and some parameters.
The rules you need.
A type override to a type that Npgsql does not give needs a Dapper type handler. Dapper fills a property with the type that Npgsql gives for its column, or through a handler. For example, a settings file's
"timestamptz": "System.DateTimeOffset"makes a property aDateTimeOffset, where Npgsql gives aDateTime. A domain mapped to a struct of your own is the same: Npgsql gives the base type of the domain, such as astringfor a domain overtext. Add a handler for each, once, besideMatchNamesWithUnderscores:using System.Data; using Dapper; public sealed class UtcDateTimeOffsetHandler : SqlMapper.TypeHandler<DateTimeOffset> { public override DateTimeOffset Parse(object value) => value is DateTime dateTime ? new DateTimeOffset(DateTime.SpecifyKind(dateTime, DateTimeKind.Utc)) : (DateTimeOffset)value; public override void SetValue(IDbDataParameter parameter, DateTimeOffset value) => parameter.Value = value; }SqlMapper.AddTypeHandler(new UtcDateTimeOffsetHandler());A
DateTimeOffsetparameter is sent as atimestamptz, which takes an offset of 0 alone. Another offset throwsArgumentException, so passvalue.ToUniversalTime().A type of yours that has converters for the query methods needs only a handler that calls them. Here the type is
Email, mapped from a domain overtext, and its converters take and return astring:using System.Data; using Dapper; public sealed class EmailHandler : SqlMapper.TypeHandler<Email> { public override Email Parse(object value) => Email.FromDatabase((string)value); public override void SetValue(IDbDataParameter parameter, Email value) => parameter.Value = Email.ToDatabase(value); }SqlMapper.AddTypeHandler(new EmailHandler());A PostgreSQL enum mapped to a C# enum needs
MapEnum<T>()on the data source. An enum read as astring, the default, needs nothing:var builder = new NpgsqlDataSourceBuilder(connectionString); builder.MapEnum<OrderStatus>(); await using var dataSource = builder.Build();Dapper sends a C# enum parameter as its number, and a column of the enum refuses a number. Send it as the next rule says.
Some parameters Dapper cannot send. Dapper refuses a parameter of these types, or sends it as a type that the column refuses:
- the unsigned numbers:
oid,xid,cid,xid8,regtype,regconfigandoidvector; - the network addresses:
inet,cidr,macaddrandmacaddr8; - the geometric types:
point,line,lseg,boxandcircle; - the ranges:
int4range,int8range,numrange,tsrange,tstzrangeanddaterange; tid,pg_lsn,tsquery,int2vectorandtimetz;- a
stringforjson,jsonb,jsonpath,xml,refcursor,ltree,lquery,ltxtquery, an enum or an array of an enum; - a C# enum, as above.
For a
string, cast the query parameter in the SQL, as in@payload::jsonb. PostgreSQL then reads the text from Dapper as that type. For the others, give Dapper the parameters by hand. Give that one parameter as aSqlMapper.ICustomQueryParameterthat adds anNpgsqlParameterwith itsDataTypeName. You cannot give Dapper theNpgsqlParameteritself, since Dapper refuses anNpgsqlParametergiven as a value:using System.Data; using Dapper; using Npgsql; public sealed class TypedParameter(string dataTypeName, object? value) : SqlMapper.ICustomQueryParameter { public void AddParameter(IDbCommand command, string name) => command.Parameters.Add( new NpgsqlParameter { ParameterName = name, DataTypeName = dataTypeName, Value = value ?? DBNull.Value } ); }connection.QueryAsync<FindSessionsDto>(Sql.FindSessions, new { address = new TypedParameter("inet", address) });- the unsigned numbers:
With SQL Server
-- name: ListCustomerOrdersSince
SELECT id, placed_at, total
FROM dbo.orders
WHERE customer_id = @customerId AND placed_at >= @since;
connection.QueryAsync<ListCustomerOrdersSinceDto>(
Sql.ListCustomerOrdersSince,
new { customerId, since = new TypedParameter(SqlDbType.DateTime2, since) }
);
What you get. since is sent as a datetime2, so the query compares placed_at with the exact time you gave. TypedParameter is the class of the second rule below.
The rules you need.
Send a
datetime2parameter as adatetime2. Dapper sends aDateTimeas adatetime, which holds a time to 1/300 of a second. So13:45:30.1234567is written to adatetime2(7)column as13:45:30.1233333. Compared with such a column, it is another value:WHERE placed_at = @placedAtfinds no row of the time you gave. Send it as above, withTypedParameterfrom the next rule. Or give Dapper the input model inDynamicParameters, and add that parameter with itsDbType, under the name of the parameter. It then takes the place of the property of that name in the model, whatever its case:var args = new ListCustomerOrdersSinceParams(customerId, since); var parameters = new DynamicParameters(args); parameters.Add("since", args.Since, DbType.DateTime2);Some parameters Dapper cannot send. For a parameter of these types, Dapper sends another type, or refuses it, or the column refuses it:
datetime2, which Dapper sends as adatetime, as above;rowversion, which SQL Server fills and refuses a value for: leave it out of the columns of anINSERT;vector, whoseSqlVector<float>Dapper refuses withNotSupportedException.
For
datetime2andvector, give Dapper the parameters by hand. Give that one parameter as aSqlMapper.ICustomQueryParameterthat adds aSqlParameterwith itsSqlDbType:using System; using System.Data; using Dapper; using Microsoft.Data.SqlClient; public sealed class TypedParameter(SqlDbType sqlDbType, object? value) : SqlMapper.ICustomQueryParameter { public void AddParameter(IDbCommand command, string name) => command.Parameters.Add( new SqlParameter { ParameterName = name, SqlDbType = sqlDbType, Value = value ?? DBNull.Value, } ); }using Microsoft.Data; using Microsoft.Data.SqlTypes; connection.ExecuteAsync( Sql.SaveEmbedding, new { id, embedding = new TypedParameter(SqlDbTypeExtensions.Vector, new SqlVector<float>(elements)) } );SqlDbTypeExtensionsis part of Microsoft.Data.SqlClient, in the namespaceMicrosoft.Data. So the call builds on .NET 8 and later.SqlDbType.Vectoris in .NET 10 alone.A
stringfor ajsoncolumn needs nothing: SQL Server converts the text from Dapper tojson.A type override to a type that SqlClient does not give needs a Dapper type handler. Dapper fills a property with the type that SqlClient gives for its column, or through a handler. For example, a settings file's
"date": "System.DateOnly"makes a property aDateOnly, where SqlClient gives aDateTime."time": "System.TimeOnly"makes a property aTimeOnly, where SqlClient gives aTimeSpan. An alias type mapped to a struct of your own is the same: SqlClient gives the base type of the alias type, such as astringfor one overnvarchar. Add a handler for each, once, besideMatchNamesWithUnderscores:using System; using System.Data; using Dapper; public sealed class DateOnlyHandler : SqlMapper.TypeHandler<DateOnly> { public override DateOnly Parse(object value) => DateOnly.FromDateTime((DateTime)value); public override void SetValue(IDbDataParameter parameter, DateOnly value) { parameter.DbType = DbType.Date; parameter.Value = value; } }using System; using System.Data; using Dapper; public sealed class TimeOnlyHandler : SqlMapper.TypeHandler<TimeOnly> { public override TimeOnly Parse(object value) => TimeOnly.FromTimeSpan((TimeSpan)value); public override void SetValue(IDbDataParameter parameter, TimeOnly value) { parameter.DbType = DbType.Time; parameter.Value = value; } }SqlMapper.AddTypeHandler(new DateOnlyHandler()); SqlMapper.AddTypeHandler(new TimeOnlyHandler());Then a
DateOnlyparameter is sent as adate, and aTimeOnlyas atime.A type of yours that has converters for the query methods needs only a handler that calls them. Here the type is
Email, mapped from an alias type overnvarchar, and its converters take and return astring:using System.Data; using Dapper; public sealed class EmailHandler : SqlMapper.TypeHandler<Email> { public override Email Parse(object value) => Email.FromDatabase((string)value); public override void SetValue(IDbDataParameter parameter, Email value) => parameter.Value = Email.ToDatabase(value); }SqlMapper.AddTypeHandler(new EmailHandler());On a connection with a transaction open, give every call the transaction. Without it, SqlClient refuses the command with
InvalidOperationException:await using var transaction = await connection.BeginTransactionAsync(); var note = await connection.QuerySingleAsync<AddNoteDto>(Sql.AddNote, args, transaction); await transaction.CommitAsync();
Only the SQL
To get the SQL of each query and nothing else, for Dapper or your own ADO.NET code to run, use the output sql. Follow steps 1 to 3, set the output to sql, and skip the tool. For a SQL Server project:
// sqlsource.json, beside your project file
{
"root": true,
"dialect": "mssql",
"output": "sql",
}
What you get. Each query gets its constant. A query with a {{token}} gets a method that fills the token in, in place of the constant. You run it as Models for Dapper, start to end shows, without the models.
The rules you need.
sqlneeds no database, no tool and no sidecar. See Output.- The tool leaves out each query whose output is
sql, so a run on the project reports nothing for it. The build needs no sidecar for it, and generates no models for it. - Without
sql, the default output,repository, asks for every query to be described, and for a method that runs it. The build then reports an error for each file or query that is not described. See Database support.
If it does not work
- Dapper sends no value for a parameter such as
@account_id. Dapper sends a property only to a parameter of its name, and it does not ignore_. Write the parameter in camelCase. See the limit. InvalidOperationException, or a property left as the class made it, for a column whose alias ends in!or?. The mark stays in the name of the column. Use a-- column:marker. See Run the models through Dapper.@ids IS NULLis an error on SQL Server, for a list of two values or more. Dapper rewrites every@idsof the query. Leave out the null test. See Run the models through Dapper.ArgumentExceptionfor aDateTimeOffsetparameter on PostgreSQL. Atimestamptztakes an offset of 0 alone. Passvalue.ToUniversalTime(). See With PostgreSQL.- Dapper refuses a parameter, or the column refuses it. Dapper cannot send some types. Cast the parameter in the SQL, or give it as a
TypedParameter, as the section of your database says. Forrowversionon SQL Server, leave the column out of theINSERT. See With PostgreSQL and With SQL Server. - A
datetime2loses digits on SQL Server, orWHEREfinds no row of the time you gave. Dapper sends aDateTimeas adatetime. Send it as adatetime2. See With SQL Server. InvalidOperationExceptionon SQL Server, on a connection with a transaction open. SqlClient refuses a command without the transaction. Give every call the transaction. See With SQL Server.
Limits
Dapper sends no value for a parameter such as @account_id
What you see. A query that writes @account_id gets no value from the property AccountId of its input model.
Why. An input model binds through Dapper only when the query writes its parameters as the properties of the model are named. Dapper sends a property of a parameter object only to a parameter that the SQL writes as @ and the name of the property. It ignores case, but not _. So the property AccountId binds to @accountId, and a query that writes @account_id gets no value from it.
What to do. Do one of these:
- Write the parameters of the query in camelCase, such as
@accountId. - Give Dapper the parameters yourself, as in
new { account_id = args.AccountId }.