dotnet roslyn sql repository genertor

Problem: pieces of code like this:

public class Whatever
{
    // ...
    public IEnumerable<Result> FindAll() {
        // ...
        var query = @"
SELECT
    Foo,
    Bar
FROM Acme
";
        // ...
    }
}

are ugly (ok, we have tripple quotes syntax)

are error prone:

no syntax higlight
no validation
can forget to modify corresponding response records
and so on

previously with help of source generators we managed to at least generate constants class automatically from sql files in project, e.g. in project you have GetTopCities.sql and as a result of source generation, suddenly you have something like:

public static partial class Sql
{
    public const string GetTopCities = "...contents of GetTopCities.sql...";
}

so your code becomes:

public class Whatever
{
    // ...
    public IEnumerable<Result> FindAll() {
        // ...
        var query = Sql.GetTopCities;
        // ...
    }
}

which is not only more readable but as well, because of dedicated sql files your ide will help you with syntax highlighting and validation.

But there is even more!

In modern SQL Server there are two amazing stored procedures:

sp_describe_first_result_set - returns the shape (schema) of query without running it
sp_describe_undeclared_parameters - returns the shape (schema) of query parameters

sp_describe_first_result_set

here is an example:

exec sp_describe_first_result_set @tsql="SELECT TOP 3 Id, Name FROM City WITH(NOLOCK)"

we are interested in name, system type name and is nullable columns, which in this example will be:

name	system type name	is nullable
Id	smallint	0
Name	varchar(255)	0

having that in place we technically may produce record like:

public record GetTopCitiesResponse
{
    public short Id { get; init; }
    public string Name { get; init; } = default!;
}

sp_describe_undeclared_parameters

same way as with response it does allow us to get data for request, aka:

exec sp_describe_undeclared_parameters @tsql="SELECT Id, Name FROM City WITH(NOLOCK) WHERE Id = @Id"

but now we are interested in name and suggested system type name

name	suggested system type name
@Id	smallint

which once again allow us to generate record like:

public record FindCityByIdRequest
{
    public short Id { get; init; }
}

Ok, so from now on we may not only generate constants but models for request and response as well, aka:

public static DbConnectionExtensions
{
    public IEnumarable<FindCityByIdResponse> FindCityById(this IDbConnection con, FindCityByIdRequest request) => con.Query<FindCityByIdResponse>(Constants.FindCityById, request);
}

The benefits here are:

now sql files are the single source of truth
if query is incorrect - stored procedures will fail and we will know upfront (aka imagine if you have typo in column name)
we never ever will forget to modify corresponding records if query changed
we may generate as much complex code with boilerplate as we want (aka add logging, telemetry, ...)

roslyn

To generate code we will use Roslyn

At the minimum it is as simple as:

using FluentAssertions;

using Microsoft.CodeAnalysis;
using Microsoft.CodeAnalysis.CSharp;
using Microsoft.CodeAnalysis.CSharp.Syntax;

namespace Repository.Tests;

public class RoslynGettingStartedExamples
{
    [Fact]
    public void EmptyExample()
    {
        var code = SyntaxFactory
            .CompilationUnit()
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("");
    }

    [Fact]
    public void NamespaceExample()
    {
        var ns = SyntaxFactory.NamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var code = SyntaxFactory
            .CompilationUnit()
            .AddMembers(ns)
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("""
        namespace Repository
        {
        }
        """);
    }

    [Fact]
    public void FileScopedNamespaceExample()
    {
        var ns = SyntaxFactory.FileScopedNamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var code = SyntaxFactory
            .CompilationUnit()
            .AddMembers(ns)
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("""
        namespace Repository;
        """);
    }

    [Fact]
    public void ClassExample()
    {
        var ns = SyntaxFactory.FileScopedNamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var cls = SyntaxFactory.ClassDeclaration("Demo")
            .AddModifiers(SyntaxFactory.Token(SyntaxKind.PublicKeyword));
        ns = ns.AddMembers(cls);
        var code = SyntaxFactory
            .CompilationUnit()
            .AddMembers(ns)
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("""
        namespace Repository;
        public class Demo
        {
        }
        """);
    }

    [Fact]
    public void PublicStringConstantExample()
    {
        var ns = SyntaxFactory.FileScopedNamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var cls = SyntaxFactory.ClassDeclaration("Demo")
            .AddModifiers(SyntaxFactory.Token(SyntaxKind.PublicKeyword));
        cls = cls.AddMembers(SyntaxFactory.FieldDeclaration(
                SyntaxFactory.VariableDeclaration(
                        SyntaxFactory.PredefinedType(
                            SyntaxFactory.Token(SyntaxKind.StringKeyword)
                        )
                    )
                    .WithVariables(
                        SyntaxFactory.SingletonSeparatedList(
                            SyntaxFactory.VariableDeclarator(
                                    SyntaxFactory.Identifier("Hello")
                                )
                                .WithInitializer(
                                    SyntaxFactory.EqualsValueClause(
                                        SyntaxFactory.LiteralExpression(
                                            SyntaxKind.StringLiteralExpression,
                                            SyntaxFactory.Literal("World")
                                        )
                                    )
                                )
                        )
                    )
            )
            .WithModifiers(
                SyntaxFactory.TokenList(
                    new[] { SyntaxFactory.Token(SyntaxKind.PublicKeyword), SyntaxFactory.Token(SyntaxKind.ConstKeyword) }
                )
            ));
        ns = ns.AddMembers(cls);
        var code = SyntaxFactory
            .CompilationUnit()
            .AddMembers(ns)
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("""
        namespace Repository;
        public class Demo
        {
            public const string Hello = "World";
        }
        """);
    }

    [Fact]
    public void ParseExample()
    {
        var tree = CSharpSyntaxTree.ParseText($"record Demo {{ public string Name {{ get; init; }} = default!; }}");
        var root = (CompilationUnitSyntax)tree.GetRoot();
        var inner = (root.Members.First() as RecordDeclarationSyntax)!;
        var prop = (inner.Members.First() as PropertyDeclarationSyntax)!;


        var ns = SyntaxFactory.FileScopedNamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var cls = SyntaxFactory.ClassDeclaration("Demo")
            .AddModifiers(SyntaxFactory.Token(SyntaxKind.PublicKeyword));

        cls = cls.AddMembers(prop);

        ns = ns.AddMembers(cls);
        var code = SyntaxFactory
            .CompilationUnit()
            .AddMembers(ns)
            .NormalizeWhitespace()
            .ToFullString();

        code.Should().Be("""
        namespace Repository;
        public class Demo
        {
            public string Name { get; init; } = default !;
        }
        """);
    }
}

There is an online resource: https://roslynquoter.azurewebsites.net/

Which may be used to quickly convert source code to its roslyn syntax declaration presentation.

For me the best usage was after enabling "Do not require 'using static Microsoft.CodeAnalysis.CSharp.SyntaxFactory;'" and "Closing parenthesis on a new line" checkboxes.

How it Works

Idea here is that we have dedicated repository project, containing sql files and single generated c# file with all wanted models, interfaces, etc

So for example if we will have single GetTopCities.sql with query SELECT TOP 5 Id, Name FROM City generated code will be:

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

#nullable enable
using System.CodeDom.Compiler;
using Microsoft.Extensions.Logging.Abstractions;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Diagnostics;
using System.Data;
using Dapper;

namespace Repository;
[GeneratedCode("Repository.Generator", "1.0.0.0")]
public record GetTopCitiesResponse
{
    public short Id { get; init; }

    public string Name { get; init; } = default !;
}

[GeneratedCode("Repository.Generator", "1.0.0.0")]
internal static class Constants
{
    public const string GetTopCities = "SELECT \n    TOP 10 \n    Id, \n    Name\nFROM City WITH (NOLOCK)\nORDER BY Id ASC";
}

[GeneratedCode("Repository.Generator", "1.0.0.0")]
public interface IRepository
{
    Task<IEnumerable<GetTopCitiesResponse>> GetTopCitiesAsync(CancellationToken token = default);
}

[GeneratedCode("Repository.Generator", "1.0.0.0")]
internal class Repository : IRepository
{
    private static readonly ActivitySource ActivitySource = new(nameof(Repository));
    private readonly IDbConnection _connection;
    private readonly ILogger _logger;
    public Repository(IDbConnection connection, ILogger? logger = null)
    {
        _connection = connection ?? throw new ArgumentNullException(nameof(connection));
        _logger = logger ?? NullLogger.Instance;
    }

    public async Task<IEnumerable<GetTopCitiesResponse>> GetTopCitiesAsync(CancellationToken token = default)
    {
        using var activity = ActivitySource.StartActivity(nameof(Constants.GetTopCities), ActivityKind.Client);
        using (_logger.BeginScope(new List<KeyValuePair<string, object>>{new("Name", "GetTopCities")}))
        {
            var timer = Stopwatch.StartNew();
            try
            {
                var result = await _connection.QueryAsync<GetTopCitiesResponse>(new CommandDefinition(Constants.GetTopCities, cancellationToken: token));
                if (_logger.IsEnabled(LogLevel.Debug))
                {
                    _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                }

                activity?.SetStatus(ActivityStatusCode.Ok);
                return result;
            }
            catch (Exception ex)
            {
                activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                if (_logger.IsEnabled(LogLevel.Warning))
                {
                    _logger.LogWarning("{Message}", ex.Message);
                }

                throw;
            }
        }
    }
}

[GeneratedCode("Repository.Generator", "1.0.0.0")]
public static partial class ServiceCollectionExtensions
{
    public static IServiceCollection AddRepository(this IServiceCollection services, string connectionStringName) => services.AddSingleton<IRepository>(p => new Repository(new SqlConnection(p.GetRequiredService<IConfiguration>().GetConnectionString(connectionStringName))));
}

Which means that from our API we may consume it like so:

using Microsoft.AspNetCore.Mvc;

using Repository;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddRepository("RabotaUA2");

var app = builder.Build();

app.MapGet("/", ([FromServices]IRepository repo, CancellationToken token) => repo.GetTopCitiesAsync(token));

app.Run();

Notes:

this is just an example of what's possible
by intent we are hiding all implementations and exposing only interfaces
for example we are providing extensions to hide service registration as well
for example we are adding some logging and telemetry boilerplate code

Workflow

Having such an setup if I add yet another GetResumesByCityId.sql file with query like:

SELECT
    TOP 10
    Id, Speciality
FROM Resume
WHERE CityId = @CityId

Suddenly in generated code following will appear:

// ...

public record GetResumesByCityIdResponse
{
    public int Id { get; init; }

    public string Speciality { get; init; } = default !;
}

public record GetResumesByCityIdRequest
{
    public short CityId { get; init; }
}

internal static class Constants
{
    // ...
    public const string GetResumesByCityId = "SELECT \n    TOP 10\n    Id, Speciality\nFROM Resume\nWHERE CityId = @CityId";
}

public interface IRepository
{
    // ...
    Task<IEnumerable<GetResumesByCityIdResponse>> GetResumesByCityIdAsync(GetResumesByCityIdRequest request, CancellationToken token = default);
}

internal class Repository : IRepository
{
    // ...

    public async Task<IEnumerable<GetResumesByCityIdResponse>> GetResumesByCityIdAsync(GetResumesByCityIdRequest request, CancellationToken token = default)
    {
        using var activity = ActivitySource.StartActivity(nameof(Constants.GetResumesByCityId), ActivityKind.Client);
        activity?.SetTag("Request", request);
        using (_logger.BeginScope(new List<KeyValuePair<string, object>>{new("Name", "GetResumesByCityId"), new("Request", request)}))
        {
            var timer = Stopwatch.StartNew();
            try
            {
                var result = await _connection.QueryAsync<GetResumesByCityIdResponse>(new CommandDefinition(Constants.GetResumesByCityId, request, cancellationToken: token));
                if (_logger.IsEnabled(LogLevel.Debug))
                {
                    _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                }

                activity?.SetStatus(ActivityStatusCode.Ok);
                return result;
            }
            catch (Exception ex)
            {
                activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                if (_logger.IsEnabled(LogLevel.Warning))
                {
                    _logger.LogWarning("{Message}", ex.Message);
                }

                throw;
            }
        }
    }
}

// ...

And I can use it like so:

app.MapGet("/demo", ([FromServices]IRepository repo, CancellationToken token) => repo.GetResumesByCityIdAsync(new GetResumesByCityIdRequest { CityId = 1 }, token));

And because we have single source of truth imagine how it will look like if I will remove or rename some columns from response or modify input variables - after code generation, if query is still valid, project wont compile and point me to places which are broken by this changes

Which is exactly desired behavior

Further ideas

Source Code Generation

I was not able to run SQL queries in Source Generator, there is a bunch of issues and open tickets in GitHub so probably it is for future

At moment it might be done as dedicated dotnet tool, build step or like in my case - simple test (I did it in that way only because it is so much easier to debug it)

Nested Namespaces

For a big project there will be bunch of SQL files and will be nice to group them in folders, aka put GetTopCities.sql, FindCityById.sql to Cities folder and do the same for vacancy related files.

As a result to avoid collisions we gonna need to generate nested namespaces, aka:

namespace Repository.Cities
{
    // GetTopCities.sql
    // FindCityById.sql
}

namespace Repository.Vacancies
{
    // ...
}

Source Code Generated Wrapper

Theoretically with such approach we may generate wrappers for classes

Imagine you have some class like:

public class SomeClient
{
    public IEnumerable<Foo> Method1() {
        // ...
    }
    // ...
    public IEnumerable<Bar> MethodN() {
        // ...
    }
}

It should be possible to create class:

public class SomeClientWrapped
{
    private readonly SomeClient _someClient;
    private readonly ILogger<SomeClientWrapped> _logger;

    public SomeClientWrapped(SomeClient someClient, ILogger<SomeClientWrapped> logger)
    {
        _someClient = someClient;
        _logger = logger;
    }

    public IEnumerable<Foo> Method1() {
        var timer = Stopwatch.StartNew();
        try {
            var result = _someClient.Method1();
            _logger.LogDebug("Method1 done in {Elapsed}", timer.Elapsed);
            return result;
        } catch(Exception ex) {
            _logger.LogWarning("{Message}", ex.Message);
            throw;
        }
    }
    // ...
    public IEnumerable<Bar> MethodN() {
        // wrap with timer, logging, promtheus, retry, circuit braker, etc
    }
}

Imagine how much of boiler plate code might be removed

Generator

Here is what I have ended up with for now:

using System.Data;
using System.Text.Json;

using Dapper;

using Microsoft.CodeAnalysis;
using Microsoft.CodeAnalysis.CSharp;
using Microsoft.CodeAnalysis.CSharp.Syntax;
using Microsoft.Data.SqlClient;

namespace Repository.Tests;

public class Generator
{
    [Fact]
    public void Generate()
    {
        DefaultTypeMap.MatchNamesWithUnderscores = true;

        var ns = SyntaxFactory.FileScopedNamespaceDeclaration(SyntaxFactory.ParseName("Repository"));
        var constants = new ConstantsBuilder();
        var inter = new InterfaceBuilder();
        var impl = new ImplementationBuilder();
        var connectionString = JsonSerializer.Deserialize<AppSettings>(File.ReadAllText("../../../../Api/appsettings.json"))!.ConnectionStrings.First().Value;
        var con = new SqlConnection(connectionString);
        var files = Directory.GetFiles("../../../../Repository/", "*.sql", SearchOption.AllDirectories).Select(Path.GetFullPath);
        foreach (var file in files)
        {
            var name = Path.GetFileNameWithoutExtension(file);
            var query = File.ReadAllText(file).Trim();
            var parameters = con.Query<DescribeUndeclaredParameters>("sp_describe_undeclared_parameters", new { tsql = query }, commandType: CommandType.StoredProcedure).ToList();
            var columns = con.Query<DescribeFirstResultSetResponse>("sp_describe_first_result_set", new { tsql = query }, commandType: CommandType.StoredProcedure).ToList();
            var hasResponse = columns.Count > 0;
            var hasRequest = parameters.Count > 0;

            constants.Add(name, query);

            if (hasResponse)
            {
                var response = new RecordBuilder($"{name}Response");
                foreach (var column in columns)
                {
                    response.Add(column.Name, Convert(column.SystemTypeName), column.IsNullable);
                }

                ns = ns.AddMembers(response.Build());
            }

            if (hasRequest)
            {
                var request = new RecordBuilder($"{name}Request");
                foreach (var parameter in parameters)
                {
                    request.Add(parameter.Name, Convert(parameter.SuggestedSystemTypeName), false);
                }

                ns = ns.AddMembers(request.Build());
            }

            inter.Add(name, hasResponse, hasRequest);
            impl.Add(name, hasResponse, hasRequest);
        }

        var code = SyntaxFactory.CompilationUnit()
            .WithUsing("System.CodeDom.Compiler")
            .WithUsing("Microsoft.Extensions.Logging.Abstractions")
            .WithUsing("Microsoft.Extensions.Logging")
            .WithUsing("Microsoft.Extensions.DependencyInjection")
            .WithUsing("Microsoft.Data.SqlClient")
            .WithUsing("Microsoft.Extensions.Configuration")
            .WithUsing("System.Diagnostics")
            .WithUsing("System.Data")
            .WithUsing("Dapper")
            .AddMembers(ns.AddMembers(constants.Build()).AddMembers(inter.Build()).AddMembers(impl.Build()).AddMembers(ServiceExtension()))
            .NormalizeWhitespace()
            .ToFullString();

        File.WriteAllText("../../../../Repository/Generated.cs", PrependWithAutoGeneratedComment(code));
    }

    private static ClassDeclarationSyntax ServiceExtension()
    {
        var tree = CSharpSyntaxTree.ParseText($$"""
        [GeneratedCode("Repository.Generator", "1.0.0.0")]
        public static partial class ServiceCollectionExtensions
        {
            public static IServiceCollection AddRepository(this IServiceCollection services, string connectionStringName) => services.AddSingleton<IRepository>(p => new Repository(new SqlConnection(p.GetRequiredService<IConfiguration>().GetConnectionString(connectionStringName))));
        }
        """);
        var root = (CompilationUnitSyntax)tree.GetRoot();
        return (root.Members.First() as ClassDeclarationSyntax)!;
    }

    private static string Convert(string? sqlType) => sqlType?.Split('(').FirstOrDefault() switch
    {
        "smallint" => "short",
        "int" => "int",
        "varchar" => "string",
        _ => throw new ArgumentException("Unexpected type", sqlType)
    };

    private string PrependWithAutoGeneratedComment(string code) =>
        """
        //------------------------------------------------------------------------------
        // <auto-generated>
        //     This code was generated by a tool.
        //
        //     Changes to this file may cause incorrect behavior and will be lost if
        //     the code is regenerated.
        // </auto-generated>
        //------------------------------------------------------------------------------

        #nullable enable

        """ + code;
}

file record AppSettings(Dictionary<string, string> ConnectionStrings);

file record DescribeUndeclaredParameters
{
    public string Name { get; init; } = default!;
    public string SuggestedSystemTypeName { get; init; } = default!;
}
file record DescribeFirstResultSetResponse
{
    public string Name { get; init; } = default!;
    public string SystemTypeName { get; init; } = default!;
    public bool IsNullable { get; init; }
}


file static class SyntaxExtensions
{
    public static CompilationUnitSyntax WithUsing(this CompilationUnitSyntax ns, string assembly) => ns.AddUsings(CreateUsingDirective(assembly));

    private static UsingDirectiveSyntax CreateUsingDirective(string usingName)
    {
        NameSyntax? qualifiedName = null;

        foreach (var identifier in usingName.Split('.'))
        {
            var name = SyntaxFactory.IdentifierName(identifier);

            if (qualifiedName != null)
            {
                qualifiedName = SyntaxFactory.QualifiedName(qualifiedName, name);
            }
            else
            {
                qualifiedName = name;
            }
        }

        return SyntaxFactory.UsingDirective(qualifiedName ?? throw new ArgumentNullException(nameof(usingName)));
    }
}

file class RecordBuilder
{
    private RecordDeclarationSyntax _declarationSyntax;

    public RecordBuilder(string name)
    {
        var tree = CSharpSyntaxTree.ParseText($"[GeneratedCode(\"Repository.Generator\", \"1.0.0.0\")]public record {name} {{}}");
        var root = (CompilationUnitSyntax)tree.GetRoot();
        _declarationSyntax = (root.Members.First() as RecordDeclarationSyntax)!;
    }

    private PropertyDeclarationSyntax ParseProperty(string code)
    {
        var tree = CSharpSyntaxTree.ParseText($"public record Demo {{ {code} }}");
        var root = (CompilationUnitSyntax)tree.GetRoot();
        var cls = (root.Members.First() as RecordDeclarationSyntax)!;
        return (cls.Members.First() as PropertyDeclarationSyntax)!;
    }

    public void Add(string name, string type, bool nullable)
    {
        name = name.TrimStart('@');
        var initializer = type == "string" ? "=default!;" : string.Empty;
        _declarationSyntax = _declarationSyntax.AddMembers(ParseProperty(nullable
            ? $"public {type}? {name} {{ get; init; }}"
            : $"public {type} {name} {{ get; init; }}{initializer}"));
    }

    public RecordDeclarationSyntax Build() => _declarationSyntax;
}

file class ImplementationBuilder
{
    private ClassDeclarationSyntax _declarationSyntax;

    public ImplementationBuilder()
    {
        var tree = CSharpSyntaxTree.ParseText($$"""
        [GeneratedCode("Repository.Generator", "1.0.0.0")]
        internal class Repository: IRepository
        {
            private static readonly ActivitySource ActivitySource = new(nameof(Repository));
            private readonly IDbConnection _connection;
            private readonly ILogger _logger;

            public Repository(IDbConnection connection, ILogger? logger = null)
            {
                _connection = connection ?? throw new ArgumentNullException(nameof(connection));
                _logger = logger ?? NullLogger.Instance;
            }
        }
        """);
        var root = (CompilationUnitSyntax)tree.GetRoot();
        _declarationSyntax = (root.Members.First() as ClassDeclarationSyntax)!;
    }

    private static MethodDeclarationSyntax ParseMethodDeclaration(string code)
    {
        var tree = CSharpSyntaxTree.ParseText(code);
        var root = (CompilationUnitSyntax)tree.GetRoot();
        var cls = (root.Members.First() as ClassDeclarationSyntax)!;
        return (cls.Members.First() as MethodDeclarationSyntax)!;
    }

    public void Add(string name, bool hasResponse, bool hasRequest)
    {
        if (hasResponse && hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($$"""
            class Repository
            {
                public async Task<IEnumerable<{{name}}Response>> {{name}}Async({{name}}Request request, CancellationToken token = default)
                {
                    using var activity = ActivitySource.StartActivity(nameof(Constants.{{name}}), ActivityKind.Client);
                    activity?.SetTag("Request", request);
                    using (_logger.BeginScope(new List<KeyValuePair<string, object>> { new("Name", "{{name}}"), new("Request", request) }))
                    {
                        var timer = Stopwatch.StartNew();
                        try
                        {
                            var result = await _connection.QueryAsync<{{name}}Response>(new CommandDefinition(Constants.{{name}}, request, cancellationToken: token));
                            if (_logger.IsEnabled(LogLevel.Debug))
                            {
                                _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                            }

                            activity?.SetStatus(ActivityStatusCode.Ok);
                            return result;
                        }
                        catch (Exception ex)
                        {
                            activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                            if (_logger.IsEnabled(LogLevel.Warning))
                            {
                                _logger.LogWarning("{Message}", ex.Message);
                            }
                            throw;
                        }
                    }
                }
            }
            """));
        } else if (hasResponse && !hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($$"""
            class Repository
            {
                public async Task<IEnumerable<{{name}}Response>> {{name}}Async(CancellationToken token = default)
                {
                    using var activity = ActivitySource.StartActivity(nameof(Constants.{{name}}), ActivityKind.Client);
                    using (_logger.BeginScope(new List<KeyValuePair<string, object>> { new("Name", "{{name}}") }))
                    {
                        var timer = Stopwatch.StartNew();
                        try
                        {
                            var result = await _connection.QueryAsync<{{name}}Response>(new CommandDefinition(Constants.{{name}}, cancellationToken: token));
                            if (_logger.IsEnabled(LogLevel.Debug))
                            {
                                _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                            }

                            activity?.SetStatus(ActivityStatusCode.Ok);
                            return result;
                        }
                        catch (Exception ex)
                        {
                            activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                            if (_logger.IsEnabled(LogLevel.Warning))
                            {
                                _logger.LogWarning("{Message}", ex.Message);
                            }
                            throw;
                        }
                    }
                }
            }
            """));
        } else if (!hasResponse && hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($$"""
            class Repository
            {
                public async Task {{name}}Async({{name}}Request request, CancellationToken token = default)
                {
                    using var activity = ActivitySource.StartActivity(nameof(Constants.{{name}}), ActivityKind.Client);
                    activity?.SetTag("Request", request);
                    using (_logger.BeginScope(new List<KeyValuePair<string, object>> { new("Name", "{{name}}"), new("Request", request) }))
                    {
                        var timer = Stopwatch.StartNew();
                        try
                        {
                            await _connection.ExecuteAsync(new CommandDefinition(Constants.{{name}}, request, cancellationToken: token));
                            if (_logger.IsEnabled(LogLevel.Debug))
                            {
                                _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                            }

                            activity?.SetStatus(ActivityStatusCode.Ok);
                        }
                        catch (Exception ex)
                        {
                            activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                            if (_logger.IsEnabled(LogLevel.Warning))
                            {
                                _logger.LogWarning("{Message}", ex.Message);
                            }
                            throw;
                        }
                    }
                }
            }
            """));
        } else if (!hasResponse && !hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($$"""
            class Repository
            {
                public async Task {{name}}Async(CancellationToken token = default)
                {
                    using var activity = ActivitySource.StartActivity(nameof(Constants.{{name}}), ActivityKind.Client);
                    using (_logger.BeginScope(new List<KeyValuePair<string, object>> { new("Name", "{{name}}") }))
                    {
                        var timer = Stopwatch.StartNew();
                        try
                        {
                            await _connection.ExecuteAsync(new CommandDefinition(Constants.{{name}}, cancellationToken: token));
                            if (_logger.IsEnabled(LogLevel.Debug))
                            {
                                _logger.LogDebug("done in {Elapsed}", timer.Elapsed);
                            }

                            activity?.SetStatus(ActivityStatusCode.Ok);
                        }
                        catch (Exception ex)
                        {
                            activity?.SetStatus(ActivityStatusCode.Error, ex.Message);
                            if (_logger.IsEnabled(LogLevel.Warning))
                            {
                                _logger.LogWarning("{Message}", ex.Message);
                            }
                            throw;
                        }
                    }
                }
            }
            """));
        }

        // _declarationSyntax = _declarationSyntax.AddMembers();
    }

    public ClassDeclarationSyntax Build() => _declarationSyntax;
}

file class InterfaceBuilder
{
    private InterfaceDeclarationSyntax _declarationSyntax;

    public InterfaceBuilder()
    {
        var tree = CSharpSyntaxTree.ParseText("[GeneratedCode(\"Repository.Generator\", \"1.0.0.0\")] public interface IRepository {}");
        var root = (CompilationUnitSyntax)tree.GetRoot();
        _declarationSyntax = (root.Members.First() as InterfaceDeclarationSyntax)!;
    }

    private static MethodDeclarationSyntax ParseMethodDeclaration(string code)
    {
        var tree = CSharpSyntaxTree.ParseText(code);
        var root = (CompilationUnitSyntax)tree.GetRoot();
        var cls = (root.Members.First() as InterfaceDeclarationSyntax)!;
        return (cls.Members.First() as MethodDeclarationSyntax)!;
    }

    public void Add(string name, bool hasResponse, bool hasRequest)
    {
        if (hasResponse && hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($"interface IRepository {{ Task<IEnumerable<{name}Response>> {name}Async({name}Request request, CancellationToken token = default); }}"));
        } else if (hasResponse && !hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($"interface IRepository {{ Task<IEnumerable<{name}Response>> {name}Async(CancellationToken token = default); }}"));
        } else if (!hasResponse && hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($"interface IRepository {{ Task {name}Async({name}Request request, CancellationToken token = default); }}"));
        } else if (!hasResponse && !hasRequest)
        {
            _declarationSyntax = _declarationSyntax.AddMembers(ParseMethodDeclaration($"interface IRepository {{ Task {name}Async(CancellationToken token = default); }}"));
        }
    }

    public InterfaceDeclarationSyntax Build() => _declarationSyntax;
}

file class ConstantsBuilder
{
    private ClassDeclarationSyntax _declarationSyntax;

    public ConstantsBuilder()
    {
        var tree = CSharpSyntaxTree.ParseText("[GeneratedCode(\"Repository.Generator\", \"1.0.0.0\")] internal static class Constants {}");
        var root = (CompilationUnitSyntax)tree.GetRoot();
        _declarationSyntax = (root.Members.First() as ClassDeclarationSyntax)!;
    }

    public void Add(string key, string value)
    {
        _declarationSyntax = _declarationSyntax.AddMembers(SyntaxFactory.FieldDeclaration(
                SyntaxFactory.VariableDeclaration(
                        SyntaxFactory.PredefinedType(
                            SyntaxFactory.Token(SyntaxKind.StringKeyword)
                        )
                    )
                    .WithVariables(
                        SyntaxFactory.SingletonSeparatedList(
                            SyntaxFactory.VariableDeclarator(
                                    SyntaxFactory.Identifier(key)
                                )
                                .WithInitializer(
                                    SyntaxFactory.EqualsValueClause(
                                        SyntaxFactory.LiteralExpression(
                                            SyntaxKind.StringLiteralExpression,
                                            SyntaxFactory.Literal(value)
                                        )
                                    )
                                )
                        )
                    )
            )
            .WithModifiers(
                SyntaxFactory.TokenList(
                    new[] { SyntaxFactory.Token(SyntaxKind.PublicKeyword), SyntaxFactory.Token(SyntaxKind.ConstKeyword) }
                )
            ));
    }

    public ClassDeclarationSyntax Build() => _declarationSyntax;
}