使用TSqlParser

是的,您正确地使用了解析器。

正如不信者Damien_所指出的,在SelectStatement中有一个QueryExpression属性,它将是 查询规范 对象作为第三个select语句(带有WHERE子句)。

这表示查询的“真实”选择位(而您正在查看的外部SelectStatement对象刚刚获得了“WITH”子句(对于CTE)、“for”子句(对于XML)、“ORDER BY”和其他位)

查询规范 从句 , where子句 ,

因此,您可以使用以下命令访问WHERE子句:

((QuerySpecification)((SelectStatement)statement).QueryExpression).WhereClause

它具有SearchCondition属性等。

快速浏览一下就会发现它包含一个QueryExpression,可能是一个QuerySpecification,它确实附加了Where子句。

如果有人来到这里并想知道如何获取select语句的全部元素,下面的代码将对此进行解释:

QuerySpecification spec = (QuerySpecification)(((SelectStatement)st).QueryExpression);
StringBuilder sb = new StringBuilder();
sb.AppendLine("Select Elements");
foreach (var elm in spec.SelectElements)                            
  sb.Append(((Identifier)((Column)((SelectColumn)elm).Expression).Identifiers[0]).Value);

sb.AppendLine();

sb.AppendLine("From Elements");
foreach (var elm in spec.FromClauses)
  sb.Append(((SchemaObjectTableSource)elm).SchemaObject.BaseIdentifier.Value);
sb.AppendLine();

sb.AppendLine("Where Elements");
BinaryExpression binaryexp = (BinaryExpression)spec.WhereClause.SearchCondition;
sb.Append("operator is " + binaryexp.BinaryExpressionType);
if (binaryexp.FirstExpression is Column)
  sb.Append(" First exp is " + ((Identifier)((Column)binaryexp.FirstExpression).Identifiers[0]).Value);

if (binaryexp.SecondExpression is Literal)
  sb.Append(" Second exp is " + ((Literal)binaryexp.SecondExpression).Value);

我不得不把SELECT语句分成几部分。我的目标是计算一个查询将返回多少条记录。我的第一个解决方案是构建一个子查询,如

SELECT COUNT(*) FROM (select id, name from T where cat='A' order by id) as QUERY

问题在于,在本例中,order子句引发错误“order BY子句在视图、内联函数、派生表、子查询和公共表表达式中无效,除非还指定了TOP或FOR XML”

using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.ScriptDom;
using System.IO;
...
public class SelectParser
{
    public string Parse(string sqlSelect, out string fields, out string from, out string groupby, out string where, out string having, out string orderby)
    {
        TSql100Parser parser = new TSql100Parser(false);
        TextReader rd = new StringReader(sqlSelect);
        IList<ParseError> errors;
        var fragments = parser.Parse(rd, out errors);

        fields = string.Empty;
        from = string.Empty;
        groupby = string.Empty;
        where = string.Empty;
        orderby = string.Empty;
        having = string.Empty;

        if (errors.Count > 0)
        {
            var retMessage = string.Empty;
            foreach (var error in errors)
            {
                retMessage += error.Identifier + " - " + error.Message + " - position: " + error.Offset + "; ";
            }

            return retMessage;
        }

        try
        {
            // Extract the query assuming it is a SelectStatement
            var query = ((fragments as TSqlScript).Batches[0].Statements[0] as SelectStatement).QueryExpression;

            // Constructs the From clause with the optional joins
            from = (query as QuerySpecification).FromClauses[0].GetString();

            // Extract the where clause
            where = (query as QuerySpecification).WhereClause.GetString();

            // Get the field list
            var fieldList = new List<string>();
            foreach (var f in (query as QuerySpecification).SelectElements)
                fieldList.Add((f as SelectColumn).GetString());
            fields = string.Join(", ", fieldList.ToArray());

            // Get The group by clause
            groupby = (query as QuerySpecification).GroupByClause.GetString();

            // Get the having clause of the query
            having = (query as QuerySpecification).HavingClause.GetString();

            // Get the order by clause
            orderby = ((fragments as TSqlScript).Batches[0].Statements[0] as SelectStatement).OrderByClause.GetString();
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }

        return string.Empty;
    }
}


public static class Extension
{
    /// <summary>
    /// Get a string representing the SQL source fragment
    /// </summary>
    /// <param name="statement">The SQL Statement to get the string from, can be any derived class</param>
    /// <returns>The SQL that represents the object</returns>
    public static string GetString(this TSqlFragment statement)
    {
        string s = string.Empty;
        if (statement == null) return string.Empty;

        for (int i = statement.FirstTokenIndex; i <= statement.LastTokenIndex; i++)
        {
            s += statement.ScriptTokenStream[i].Text;
        }

        return s;
    }
}

要简单地使用这个类:

string fields, from, groupby, where, having, orderby;
SelectParser selectParser = new SelectParser(); 
var retMessage = selectParser.Parse("SELECT * FROM T where cat='A' Order by Id desc",
   out fields, out from, out groupby, out where, out having, out orderby);