NkChinh.SqlPartial.Generator 1.1.3

SqlPartial.Generator

Modern Roslyn source generator that turns .sql files into strongly-typed, DBMS-aware C# constants — with full IntelliSense, zero-boilerplate overloads, and automatic generation on save.

Why SqlPartial?

Writing SQL as string literals inside C# is a painful experience:

• No Syntax Highlighting: SQL strings are just plain text to your editor.
• No Linting/Validation: SQL errors are only caught at runtime.
• Hard to Test: You can't easily run a string literal against a database without copying it.
• Messy Code: Large SQL queries clutter your C# logic.

SqlPartial solves this by letting you keep your SQL in dedicated .sql files. You get full editor support (highlighting, formatting, schema validation) while the generator seamlessly bridges them into your C# code as strongly-typed constants.

How it works

1. Simple Property Generation

Each .sql file becomes a static readonly SqlStrings property prefixed with Sql on a partial class. At runtime, call Get("PostgreSql") to receive the provider-specific SQL, falling back to the default SQL automatically.

UserRepo.GetActive.ms.sql     → SQL Server version
UserRepo.GetActive.pg.sql     → PostgreSQL version
UserRepo.GetActive.sql        → (Optional) Default fallback

Generated output:

partial class UserRepo
{
    private static readonly SqlStrings SqlGetActive = new SqlStrings(
        postgresql: @"SELECT * FROM Users WHERE IsActive = true",
        sqlserver:  @"SELECT * FROM Users WHERE IsActive = 1",
        @default:   @"SELECT * FROM Users WHERE IsActive = 1"
    );
}

2. Zero-Boilerplate Parameter Injection

Use the [Sql] attribute from SqlPartial to automatically resolve the correct SQL string based on the current DBMS.

using SqlPartial;

public partial class UserRepo
{
    // You must define this property (static or instance)
    public string SqlProviderName { get; set; } = "PostgreSql";

    // Mark parameter with [Sql]
    public Task<IEnumerable<User>> Execute([Sql] string query)
    {
         // The generator handles the .Get(SqlProviderName) call for you
         return connection.QueryAsync<User>(query);
    }
}

// USAGE: The generator creates a generic overload automatically!
var users = await repo.Execute(UserRepo.SqlGetActive);
// At runtime, 'query' becomes the PostgreSql string.

Installation

Available on NuGet.

dotnet add package NkChinh.SqlPartial.Generator

AI Agent Skills

If you use an agent-based development environment, you can install specialized skills to help you manage SQL files and maintain high-quality SQL code:

Core Management

Handle file creation, class naming, and generator configuration.

npx skills add nkchinh/sql-partial --skill sql-partial

Style & Quality (Optional)

Ensure production-grade SQL with best practices for documentation, performance, and multi-DBMS support.

npx skills add nkchinh/sql-partial --skill sql-partial-style

Setup

1. Configure DBMS providers

Add to your .csproj. A default is always available — only declare additional providers for multi-DBMS support.

SqlPartial is DBMS-agnostic. You can define any provider by choosing an extension (matched at the end of the filename) and a Display Name (used in C# code and Get() calls). Multiple extensions can map to the same DBMS.

<PropertyGroup>
    
    <SqlPartialProviders>.pg.sql:PostgreSql,.pgsql:PostgreSql,.ms.sql:SqlServer</SqlPartialProviders>
</PropertyGroup>

Suggestive List of Providers

2. Register your SQL files

<ItemGroup>
    
    <AdditionalFiles Include="**/*.sql;**/*.*.pgsql" Exclude="obj/**/*;bin/**/*">
        <SourceItemType>SqlPartial</SourceItemType>
    </AdditionalFiles>
</ItemGroup>

3. Declare the partial class

The generator adds members to an existing top-level, non-generic partial class. Its name and namespace must match exactly; otherwise generation is skipped with error SQLPG021. The namespace is derived automatically from $(RootNamespace) + the relative directory of the .sql file. Names are not automatically recased or normalized.

namespace MyApp.Data
{
    public partial class UserRepo { }
}

Classes containing methods with [Sql] parameters must be declared partial. This includes static classes containing extension methods; otherwise the generator reports SQLPG024 and skips overload generation. Interfaces use a separate generated extension class and do not need to be partial. See Limitations for the supported interface shape and unsupported target types.

File naming convention

ClassName.QueryName.sql          Default fallback (ANSI SQL recommended)
ClassName.QueryName.pg.sql       PostgreSQL-specific
ClassName.QueryName.pgsql        Also PostgreSQL (if configured)
ClassName.QueryName.ms.sql       SQL Server-specific

• ClassName — must be a valid C# identifier and match an existing partial class name exactly.
• QueryName — must be a valid C# identifier and becomes the property name on the class (prefixed with Sql).
• Extension — must match an extension declared in SqlPartialProviders, or use .sql for the default.

Keyword names are escaped with @ in generated C# (for example, class.Query.sql targets partial class @class). Files must be under the project directory, and directory names must form a valid C# namespace.

Authoring Tips & Patterns

1. Parameter Documentation with Exclusion Blocks

Use -- #exclude to provide test data and document parameter meanings. This block is stripped from C# but remains in your SQL file for IDE use.

File: ProductRepo.GetById.sql (Shared default SQL)

-- #exclude
-- Parameters for local testing & documentation
DECLARE @Id INT = 1;
-- /exclude

SELECT p.Id, p.Name, p.Price
FROM Products p
WHERE p.Id = @Id

2. Handling Multi-DBMS Transitions

If your original query was written for a specific DBMS (e.g., SQL Server), rename it when adding support for a second one. This prevents your "generic default" from containing incompatible syntax.

File: ProductRepo.Search.ms.sql (SQL Server version)

-- #exclude
DECLARE @SearchText NVARCHAR(100) = 'Laptop';
-- /exclude

SELECT p.Id, p.Name FROM Products p
WHERE p.Name LIKE '%' + @SearchText + '%'
ORDER BY p.CreatedDate DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

File: ProductRepo.Search.pg.sql (PostgreSQL version)

-- #exclude
DECLARE SearchText TEXT := 'Laptop';
-- /exclude

SELECT p.Id, p.Name FROM Products p
WHERE p.Name ILIKE '%' || :SearchText || '%'
ORDER BY p.CreatedDate DESC
LIMIT 10

Rule of thumb: The base .sql file should only contain code that works across all your configured providers. If the syntax differs, use explicit extensions.

3. Line comments are stripped

Lines beginning with -- are removed from the generated constant, so you can annotate freely:

-- Returns a single user by primary key
SELECT id, name, email FROM users WHERE id = @Id

Advanced Configuration

Customizing Access Modifiers

By default, generated SQL properties are private static readonly. If you need to share them across classes or projects, use the [SqlPartial] attribute.

using SqlPartial;

[SqlPartial(AccessModifier.Public)]
public static partial class SharedQueries
{
    // Any .sql files targeting SharedQueries will generate PUBLIC properties
}

Available modifiers: Private (default), Internal, Protected, Public.

For sharing within a project, use Internal. Public fields require public SQL types, provided by SqlPartialEmitSharedNamespace, a shared namespace, or an external public type.

A shared SQL catalog needs only this one class declaration. Add SharedQueries.GetUsers.sql, SharedQueries.GetRoles.sql, etc. beside it, then access SharedQueries.SqlGetUsers from other classes.

Sharing types across projects (Recommended)

To avoid duplicating core types and enable cross-project attribute sharing, use the Shared Namespace model:

Abstractions project:

<PropertyGroup>
    <SqlPartialEmitSharedNamespace>MyCompany.Data.Abstractions</SqlPartialEmitSharedNamespace>
</PropertyGroup>

Implementation project:

<PropertyGroup>
    <SqlPartialUseSharedNamespace>MyCompany.Data.Abstractions</SqlPartialUseSharedNamespace>
</PropertyGroup>

MSBuild property reference

SqlPartialEmitSharedNamespace and SqlPartialUseSharedNamespace are mutually exclusive. Namespace and external type settings must contain valid C# names. Provider extensions must be unique (case-insensitive); .sql is reserved for fallback. Multiple distinct extensions may share the exact same provider name. Provider names that generate conflicting members or parameter names, including Default, Get, and Fallback, are rejected; C# keywords are supported through @ escaping.

Diagnostics

Robustness & Conflict Handling

SqlPartial reports configuration and target-class errors before emitting SQL members. Naming and SQL mapping collisions produce warnings with the behaviors described below.

1. Naming Collisions

If a generated property (e.g., SqlGetUsers) would conflict with an existing field, property, or method in your C# class, the generator will:

1. Emit a SQLPG005 warning.
2. Automatically append a numeric suffix to the generated property (e.g., SqlGetUsers1).

This ensures that your manual code always takes precedence and the project remains compilable.

2. SQL Mapping Collisions

If multiple files resolve to the same DBMS provider for the same query (e.g., GetUsers.pg.sql and GetUsers.pgsql both mapping to PostgreSql), the generator will:

1. Emit a SQLPG006 warning.
2. Select the first encountered file. Resolve the warning rather than relying on file ordering.

Extension matching within a single filename still chooses the longest configured suffix. A provider-specific file and a fallback .sql file are separate mappings and may coexist.

Usage Patterns

All patterns are unified under the ISqlString interface. Choose based on one question: is the SQL structure fixed at compile time?

Static SQL (structure known at compile time)

From .sql files — the default choice

.sql files suit any SQL from simple to highly complex. The key advantage is full IDE support: syntax highlighting, schema validation, and the ability to run the query directly in your SQL editor.

Same syntax for all DBMS  → single ClassName.QueryName.sql
Different syntax per DBMS  → ClassName.QueryName.pg.sql + ClassName.QueryName.ms.sql

await QueryAsync(SqlGetActiveUsers);  // generated from your .sql file(s)

Inline strings — when a file isn't worth it

Only for true one-liners where the overhead of a separate file outweighs the IDE benefit.

// Same SQL for all providers
await QueryAsync(new SqlStrings(@default: "SELECT name FROM users"));

// Different SQL per provider
await QueryAsync(new SqlStrings(
    postgresql: "SELECT name FROM users LIMIT 10",
    sqlserver:  "SELECT TOP 10 name FROM users",
    @default:   "SELECT name FROM users"
));

Dynamic SQL (structure changes at runtime)

SqlDynamic — runtime values embedded in SQL structure

Use when the SQL structure itself is computed at runtime (table name, partition, timestamp):

var dynamicQuery = new SqlDynamic(
    postgresql: () => $"SELECT * FROM sales_{DateTime.Now:yyyyMM}",
    sqlserver:  () => $"SELECT * FROM sales_{DateTime.Now:yyyyMM}",
    @default:   () => "SELECT * FROM sales"
);
await QueryAsync(dynamicQuery);

SqlStringBuilder — assembling SQL from ISqlString parts

Use when the final query is composed of multiple pieces — base query, filter clause, ORDER BY — each potentially DBMS-specific. The DBMS provider is not needed while building; it is resolved only at Build().

var builder = new SqlStringBuilder()
    .Append(UserRepo.SqlGetActive)      // generated SqlStrings (from .sql files)
    .Append(" WHERE status = @status")  // literal — same for all providers
    .Append(new SqlStrings(             // inline static SQL per provider
        postgresql: "LIMIT $1",
        sqlserver:  "FETCH NEXT @n ROWS ONLY",
        @default:   ""))
    .AppendLine(new SqlDynamic(         // inline dynamic SQL (evaluated at Build time)
        postgresql: () => $"-- generated {DateTime.Now:yyyy-MM-dd}",
        @default:   () => ""));

// Pass to a [Sql] overload — provider resolved at call site
await repo.Execute(builder);

// Or resolve manually for a specific provider
string pg  = builder.Build("PostgreSql");
string ms  = builder.Build("SqlServer");

SqlStringBuilder is thread-safe: Append/AppendLine/Clear and Build can be called from multiple threads. ToString() resolves against the default (fallback) provider.

Generic Execution Pattern

To get the most out of SqlPartial, use ISqlString as a generic constraint in your data access layer. This allows your methods to accept SQL from any source (Auto or Manual) with zero allocation overhead for static cases.

public async Task<T> QueryAsync<TSql>(TSql sql) where TSql : struct, ISqlString
{
    // 'dbProvider' is resolved at runtime from your configuration (e.g., "PostgreSql", "SqlServer")
    string rawSql = sql.Get(dbProvider);
    return await connection.QueryAsync<T>(rawSql);
}

License

MIT