将Array<SqlParameter>与SqlParser.Parser的输出匹配？

Question

背景

作为一个面向数据库的应用程序的一部分，为了桥接多个客户端数据库，我们发现有必要构建和传递带参数的查询到一个EntityFramework应用程序中。我们通过非常标准的语法来实现：

context.Database.SqlQuery<ReturnModel>(queryString, sqlParameters)

queryString是一个字符串，sqlParameters是SqlParameter对象数组。

测试

随着我们在应用程序中增加更多的自动化测试，我发现使用SqlParser作为“离线”测试工具很有帮助。它无法捕获所有问题，但我可以这样做：

var parseResults = Parser.Parse(queryString);
Assert.That(parseResults.Errors, Is.Empty);

请确保我们没有将任何语法错误引入到参数化查询字符串中。

我还希望能够验证我们在生成的参数列表中没有丢失任何参数；即查询字符串中预期的参数，但未提供。是否有任何方法可以将 sqlParameters 与 parseResults 中的某些内容进行匹配以实现此目的，或者利用 Microsoft.SqlServer.Management 库的其他功能？

- Glazius

Answer 1

SqlParser 是一个经典的解析器，具有访问者接口以处理结果。唯一的问题是公共文档似乎与发布不符，并且通常缺乏任何示例，以至于几乎没有用处。我假设在这里使用的解析器是可以在 Microsoft.SqlServer.SqlParser NuGet package 中找到的。早期版本是作为独立安装分发的，并且它们不使用相同的类型。

对于简单查询，我们可以通过访问所有变量声明和引用，并消除对本地声明变量的引用来获取所有参数的列表 - 这些必须是参数。

class ParameterVisitor : SqlCodeObjectRecursiveVisitor {
    HashSet<string> referencedVariables = new HashSet<string>();
    public override void Visit(SqlScalarVariableRefExpression codeObject) {
        referencedVariables.Add(codeObject.VariableName);
    }

    HashSet<string> declaredVariables = new HashSet<string>();
    public override void Visit(SqlVariableDeclaration codeObject) {
        declaredVariables.Add(codeObject.Name);
    }

    public override void Visit(SqlBatch codeObject) {
        base.Visit(codeObject);
        parameters = referencedVariables.Except(declaredVariables).ToList();
    }

    List<string> parameters;
    public IEnumerable<string> Parameters => parameters;
}

被用作（例如）：

internal static class ParseResultsExtensions {
    public static IEnumerable<string> GetParameters(this ParseResult p) {
        var pv = new ParameterVisitor();
        p.Script.Accept(pv);
        return pv.Parameters;
    }
}

string queryString = @"DECLARE @notAParameter INT; SELECT @c, @b, @a, @notAParameter";
var myParameterCollection = new[] {
    new SqlParameter("@a", SqlDbType.Int),
    new SqlParameter("@b", SqlDbType.Int),
    new SqlParameter("@c", SqlDbType.Int),
};

ParseResult parseResults = Parser.Parse(queryString);
Assert.That(parseResults.Errors, Is.Empty);

var expected = myParameterCollection.Select(p => p.ParameterName);
var actual = parseResults.GetParameters();
Assert.That(actual, Is.EquivalentTo(expected));

加入更多有用的断言，使其更为详尽。

令人沮丧的是，SqlScalarVariableRefExpression 有一个 BoundVariable 属性，似乎能够将引用链接到它们的声明。不幸的是，使用这个属性需要使用 BinderProvider，它会从实际的数据库中获取完整的元数据（以便将标识符绑定到数据库对象）。文档对此的介绍过于简略，无法解码如何在本地解析时适当地使用它。

此代码也不完整，因为它只处理标量变量 - 添加对表变量的支持留给读者作为练习。

- Jeroen Mostert

---

这个问题太小了，不值得放在答案里，但是这里有一点低效：HashSet没有实现Except，只有ExceptWith，所以我们得到的是默认的Enumerable.Except实现，其中包含它自己的集合逻辑。实际上这并不重要，因为你不太可能使用那么多参数，以至于这成为瓶颈（而且SQL Server最多允许“仅”2100个参数）。 - Jeroen Mostert

我很欣赏这个结构，它非常接近我想要的！但似乎访问时遇到了一个 SqlNullScalarExpression: "coalesce((select top 1 Id from DataTable where Id in (@ParamList0) and FkId = a.FkId order by Id), 0)"，并且找不到任何子级进行更深入的操作。我可能需要重新构建它。 - Glazius

codeObject.Tokens.Where(t => t.Type == Tokens.TOKEN_VARIABLE.ToString()).Select (t => t.Text) 在“parent”SqlBatch访问中似乎提供了一个被引用变量的列表，而不需要深入解析树，尽管我意识到Tokens枚举不被认为是未来安全的。 - Glazius

@Glazius：是的，COALESCE和NULLIF中的子查询（但不是 ISNULL）似乎没有进一步分解（或者至少在解析树中没有显示）。对我来说，这看起来像是个bug。使用其他一些函数，例如REVERSE，会产生一个具有子元素的SqlBuiltinScalarFunctionCallExpression，即使您使用错误数量的参数调用该函数，甚至如果实际上不存在这样的函数，例如UNICORN((SELECT ..))！ 这表明有各种解决方法；使用令牌列表可能是最好的方法之一。 - Jeroen Mostert