Visual studio compile-time SQL validation

Question

If I've got a Dapper.NET query like so:

conn.Execute("insert into My_Table values ('blah', 'blah, 'blah', 'blah')");

How can I force visual studio to do compile-time verification of this query against a certain database schema? I'm aware there are libraries that can do query validation (provided a string and connection), but what's the correct tool for the job here?

Extend Roslyn to examine strings I mark as query strings (with a syntax similar to the familiar @"Unescaped string")? Custom pre-processing?

Or am I asking the wrong question? Would it be safer to wrap all query logic in stored procedures within my database project (which gets me validation of the query itself)? Now that I write that down I think I'll actually go with that solution, but I'm still curious about the above. I'd like to be able to write:

 conn.Execute(#"insert into My_Table values ('blah',
 'blah, 'blah', 'blah')"); //Hashtag to mark as query

And have the compiler validate the string against a given database schema.

Answer 1

One option would be to write a Roslyn analyzer. What the analyzer would do is to find all calls to functions like Execute() and if their parameter was a constant string, validate that using the library you mentioned.

When implementing the analyzer, one issue you'll encounter is how to specify the database schema to verify against. Unless you want to somehow hardcode it in your analyzer, it seems the way to do it is to use "additional files" (which currently requires hand-editing the csproj of any project where you want to use the analyzer).

The analyzer could look like this (note that you will likely need to modify the code to make it more robust):

public override ImmutableArray<DiagnosticDescriptor> SupportedDiagnostics
    => ImmutableArray.Create(BadSqlRule, MissingOptionsFileRule);

public override void Initialize(AnalysisContext context)
{
    context.RegisterCompilationStartAction(AnalyzeCompilationStart);
    context.RegisterCompilationAction(AnalyzeCompilation);
    context.RegisterSyntaxNodeAction(AnalyzeNode, SyntaxKind.InvocationExpression);
}

private Config config = null;

private void AnalyzeCompilationStart(CompilationStartAnalysisContext context)
{
    var configFile = context.Options.AdditionalFiles
        .SingleOrDefault(f => Path.GetFileName(f.Path) == "myconfig.json");

    config = configFile == null
        ? null
        : new Config(configFile.GetText(context.CancellationToken).ToString());
}

private void AnalyzeCompilation(CompilationAnalysisContext context)
{
    if (config == null)
        context.ReportDiagnostic(Diagnostic.Create(MissingOptionsFileRule, Location.None));
}

private void AnalyzeNode(SyntaxNodeAnalysisContext context)
{
    if (config == null)
        return;

    var node = (InvocationExpressionSyntax) context.Node;

    var symbol = (IMethodSymbol) context.SemanticModel.GetSymbolInfo(
        node, context.CancellationToken).Symbol;

    // TODO: properly check it's one of the methods we analyze
    if (symbol.ToDisplayString().Contains(".Execute(string"))
    {
        var arguments = node.ArgumentList.Arguments;
        if (arguments.Count == 0)
            return;

        var firstArgument = arguments.First().Expression;

        if (!firstArgument.IsKind(SyntaxKind.StringLiteralExpression))
            return;

        var sqlString = (string)((LiteralExpressionSyntax) firstArgument).Token.Value;

        if (Verify(config, sqlString))
            return;

        context.ReportDiagnostic(
            Diagnostic.Create(BadSqlRule, firstArgument.GetLocation(), sqlString));
    }
}