NkChinh.SqlPartial.Generator 1.0.0

SqlPartial.Generator

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

How it works

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 ANSI SQL automatically.

UserRepo.GetById.sql        → ANSI fallback
UserRepo.GetById.pg.sql     → PostgreSQL override
UserRepo.GetById.ms.sql     → SQL Server override

Generated output:

partial class UserRepo
{
    private static readonly SqlStrings SqlGetById = new SqlStrings(
        @"SELECT id, name FROM users WHERE id = @id",
        postgresql: @"SELECT id, name FROM users WHERE id = $1",
        sqlserver:  @"SELECT id, name FROM users WHERE id = @id"
    );
}

Runtime usage:

// providerName comes from appsettings, e.g. "PostgreSql"
var sql = UserRepo.SqlGetById.Get(providerName);

// For single-DBMS projects, use implicit conversion (returns AnsiSql)
string sqlSimple = UserRepo.SqlGetById;

Installation

dotnet add package NkChinh.SqlPartial.Generator

AI Agent Skill

If you use Gemini CLI or a similar agent-based environment, you can install the specialized skill to help you manage SQL files:

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

Setup

1. Configure DBMS providers

Add to your .csproj. ANSI SQL is always available — only declare additional providers:

<PropertyGroup>
    
    <SqlPartialProviders>pg:PostgreSql;ms:SqlServer;my:MySql</SqlPartialProviders>
</PropertyGroup>

2. Register your SQL files

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

3. Declare the partial class

The generator produces a partial class — you must declare the other half yourself:

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

The namespace is derived automatically from $(RootNamespace) + the relative directory of the .sql file.

File naming convention

ClassName.QueryName.sql          ANSI SQL (shared fallback)
ClassName.QueryName.an.sql       Same as above — explicit ANSI slug
ClassName.QueryName.pg.sql       PostgreSQL-specific
ClassName.QueryName.ms.sql       SQL Server-specific

• ClassName — must match the partial class name exactly.
• QueryName — becomes the property name on the class (prefixed with Sql).
• Slug — must match a slug declared in SqlPartialProviders, or an for ANSI.

Optional: sharing SqlStrings across projects

By default the SqlStrings struct is generated in each project that uses the package. If you have multiple projects and want to share the same struct, generate it in one "core" project and reference it from the others.

Core project — normal setup, struct is generated here.

Consumer project — add this property to skip struct generation:

<PropertyGroup>
    <SqlPartialStringsType>MyCompany.Core.SqlStrings</SqlPartialStringsType>
</PropertyGroup>

Controlling the namespace of SqlStrings

By default the struct is placed in $(RootNamespace). To override:

<PropertyGroup>
    <SqlPartialStringsNamespace>MyCompany.Data.Sql</SqlPartialStringsNamespace>
</PropertyGroup>

SQL authoring tips

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

Exclude blocks

Wrap SQL that should exist only in your SQL editor/tests (and be stripped from the generated C#) with --#exclude / --/exclude.

Note: --#testpart / --/testpart is also supported for backward compatibility, but #exclude is the official marker.

SELECT id, name FROM users
--#exclude
WHERE tenant_id = 'test-tenant'
--/exclude
AND id = @id

MSBuild property reference

Diagnostics

License

This project is licensed under the MIT License.