c# - 带有别名的 SELECT 的 ScriptDom 解析

Question

我正在寻找使用 ScriptDom 来解析 T-SQL 语句并确保至少指定了两列。我找到了一种提取列名的方法，并且可以从中正确地进行验证。但是，我还想验证该列是否有别名，并且不确定如何检查。

我想确保 select 语句同时指定 MCA 和 MOC 列。

// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";

// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";

_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;

var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
  if (column is SelectScalarExpression) {
    var expression = (column as SelectScalarExpression).Expression;
    if (expression is ColumnReferenceExpression) {
      var columnExpression = expression as ColumnReferenceExpression;
      if (identifier.Identifiers.Count == 1)
      {
        var columnName = identifier.Identifiers.First().Value;
        // I can validate from this extracted column name
      }
    }
  }
}

标签： c#sql.netscriptdom

Answer 1

SelectScalarExpression有一个ColumnName属性，与 处于同一级别ColumnReferenceExpression。如果列上没有别名，则这将为空。

您的循环可能包含如下内容：

if (column is SelectScalarExpression)
{
    var columnAlias = (column as SelectScalarExpression).ColumnName?.Value;

    if (columnAlias == null)
    {
        // evaluate the ColumnReferenceExpression here
    }
}