Use MySQL, SQLite, Oracle or another database
Keep the SQL of a MySQL, MariaDB, SQLite, Oracle, CockroachDB or DuckDB project in .sql files, as C# constants.
Use this when your database is not PostgreSQL or SQL Server. You get the SQL alone, and run it with your own code.
Add the package
Step 1. In your project's folder, add the package, and your database's driver:
$ dotnet add package SqlSource
$ dotnet add package Microsoft.Data.Sqlite
A project whose queries all have the output sql needs no sqlsource tool. See The tool.
Write a query
Step 2. Save your queries as Queries/UserQueries.sql:
-- name: CountUsers
-- summary: The number of users.
SELECT count(*) FROM users;
-- name: CountRows
-- summary: The number of rows of a table that the code chooses.
SELECT count(*) FROM {{table}};
The rules you need.
-- name:starts a query, and its name is a C# identifier. See Queries and names.- On MySQL and MariaDB, a marker needs white space after
--, and a#comment is never a marker. See Markers undermysql.
Mark a class
Step 3. Save a partial class of the file's name as Queries/UserQueries.cs:
// Queries/UserQueries.cs
using SqlSource;
[SqlSourceGenerate]
public sealed partial class UserQueries;
What you get. The class gets UserQueries.sql, the file named for it in its folder. The class must be partial. See The attribute and Source.
Set the dialect of your database and the output sql
Step 4. Save this sqlsource.json:
// sqlsource.json, beside your project file
{
"root": true,
"dialect": "sqlite",
"output": "sql",
}
What you get. SqlSource reads every .sql file of the project as SQLite. Each query gets a constant or a method that returns its SQL, which step 5 runs.
Use mysql, mariadb, sqlite, oracle (also for Firebird) or cockroachdb for that database, postgres for DuckDB, and ansi for any other.
The rules you need.
- Any dialect works with the output
sql. Set the one named for your database, such asmysqlorsqlite. See Choosing a dialect. - The dialect tells SqlSource where a comment or a string ends. See Choosing a dialect.
- The output
sqlgives each query its SQL alone, and needs no sidecar and no connection. See Output.
Run the constant
Step 5. Run the SQL with your driver:
// Queries/UserQueries.cs
using Microsoft.Data.Sqlite;
using SqlSource;
[SqlSourceGenerate]
public sealed partial class UserQueries(SqliteConnection connection)
{
public long CountUsers() => Count(Sql.CountUsers);
public long CountRows(string table) => Count(Sql.CountRows(table));
private long Count(string sql)
{
using var command = connection.CreateCommand();
command.CommandText = sql;
return (long)command.ExecuteScalar()!;
}
}
What you get. Sql.CountUsers is a const string. Sql.CountRows(table) returns the SQL with the token filled in.
The rules you need.
Sqlis a private class nested inUserQueries, so only its own code can use it. See Where the members go.- The SQL is as you wrote it, without its markers and comments. A query parameter such as
@idstays in it: give the database its value yourself. See Constants and What reaches the generated SQL. - Nothing in a token is escaped. Pass only text that your own code chooses. See Token methods.
What you do not get
You get no models and no method that runs the query. Nothing checks a query against your schema, so a wrong table or column fails only when the query runs.
The rules you need.
- Only PostgreSQL and SQL Server can be described. On another dialect, an output other than
sqlis SQLSRC209,modelsincluded. See Databases that are not described. - If your database is DuckDB, set the dialect to
postgresand the output tosql. The tool cannot describe a DuckDB query. See DuckDB.
If it does not work
- SQLSRC209 for each file. The output of a query in the file is not
sql, and only PostgreSQL and SQL Server can be described. Set the output tosql, as step 4 does. See SQLSRC209. - SQLSRC101 or SQLSRC102 on valid SQL, or SQL missing from a constant. SqlSource reads a few constructs differently from your database. See What is still read differently.