dotnet source generator embed sql

Did you ever saw code like this one:

con.Query<Whatever>(@"
SELECT
  a,
  b,
  c,
  ...
FROM table
JOIN quadrillion_more_tables
WHERE quadrillion_more_considtions
");

There is few bad things:

we do not have code highlight and analysis
it formats and looks ugly
it is simple to make mistake
it is hard to run query

Imagine how much easier it will be read such code instead:

con.Query<Whatever>(Sql.SelectAllWhatever);

To do so we gonna need some static class like this one:

public static class Sql {
  public static string SelectAllWhatever = "SELECT ... FROM ....";
}

But problem still remains

To deal with this we are going to introduce code generator which only purpose will be to inject found sql file contents into this static class

So technically we may have empty Sql class but make it partial in our project, ok, maybe with some other helpers, aka:

using System.Data;

namespace Demo;

public static partial class Sql
{
  public static int Exec(this IDbConnection connection, string sql)
  {
    var command = connection.CreateCommand();
    command.CommandText = sql;
    if (connection.State != ConnectionState.Open)
    {
      connection.Open();
    }
    var affected = command.ExecuteNonQuery();
    if (connection.State == ConnectionState.Open)
    {
      connection.Close();
    }

    return affected;
  }
}

Next we are going to create class library in our solution with single file:

using System;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.CodeAnalysis;
using Microsoft.CodeAnalysis.Text;

namespace SqlConstants
{

  [Generator]
  public class Generator : ISourceGenerator
  {
    public void Initialize(GeneratorInitializationContext context)
    {
      // throw new NotImplementedException();
    }

    public void Execute(GeneratorExecutionContext context)
    {
      var sb = new StringBuilder(@"
namespace Demo
{
    public static partial class Sql
    {
        public static void World() => System.Console.WriteLine(""ACME"");

");
      sb.Append(@"");
      foreach (var file in context.AdditionalFiles.Where(file => file.Path.EndsWith(".sql")))
      {
        var name = Path.GetFileNameWithoutExtension(file.Path);

        var content = file.GetText(context.CancellationToken)?.ToString().Replace("\"", "\"\"");

        // if (!content.Contains("SELECT * ")) throw new Exception("QUERIES MUST BE WILDCARDED");

        sb.AppendLine($"public const string {name} = @\"{content}\";");
        // sb.AppendLine($"public static CompaniesByNameContains(this IDbConnection con, {content} + " WHERE name Container(name) ", string name ) {}");
      }

      sb.Append(@"
    }
}");

      context.AddSource("Sql.g.cs", sb.ToString());

    }
  }
}

Inside nothing special, we are traversing all files, looking for .sql ones, and grabing their content into our string builder.

The result is stored as so called Sql.g.cs it will live in

For this to work you gonna need following dependencies:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <!-- Microsoft.CodeAnalysis.CSharp - 4.0.1 - works fine -->
    <!-- Microsoft.CodeAnalysis.CSharp - 4.3.0 - brokes build -->
    <PackageReference Include="Microsoft.CodeAnalysis.CSharp" Version="4.0.1" PrivateAssets="all" />
    <PackageReference Include="Microsoft.CodeAnalysis.Analyzers" Version="3.3.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>

</Project>

Now to wire everything up you gonna need to add reference to this generator into main project

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
    <LangVersion>default</LangVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.123" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="6.0.8" />
  </ItemGroup>

  <ItemGroup>
    <!-- note OutputItemType="Analyzer" ReferenceOutputAssembly="false" that is required for source generators to work -->
    <ProjectReference Include="..\SqlConstants\SqlConstants.csproj" OutputItemType="Analyzer" ReferenceOutputAssembly="false" />
  </ItemGroup>

  <ItemGroup>
    <!-- note that we are going to catch all sql files -->
    <AdditionalFiles Include="**/*.sql" />
  </ItemGroup>
</Project>

With this in place after compilation suddenly you Sql class has everytthing you need, and your program become clean:

using Dapper;
using Demo;
using Microsoft.Data.Sqlite;

var connection = new SqliteConnection("Data Source=:memory:");
await connection.OpenAsync();

await connection.ExecuteAsync(Sql.CreateTable);
await connection.ExecuteAsync(Sql.Insert);

foreach (var player in connection.Query<Player>(Sql.SelectAllPlayers))
{
  Console.WriteLine(player);
}

public record Player
{
  public int Id { get; init; }
  public string Name { get; init; } = null!;
  public int Age { get; init; }
}

Also thing about following, imagine that we are working with SQL Server and want to enforce WITH (NOLOCK) to be present - we may parse content of sql file and throw exceptions from generator if we found something.