using the TSqlParser

Question

I'm attempting to parse SQL using the TSql100Parser provided by microsoft. Right now I'm having a little trouble using it the way it seems to be intended to be used. Also, the lack of documentation doesn't help. (example: http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx )

When I run a simple SELECT statement through the parser it returns a collection of TSqlStatements which contains a SELECT statement. Trouble is, the TSqlSelect statement doesn't contain attributes such as a WHERE clause, even though the clause is implemented as a class. http://msdn.microsoft.com/en-us/library/microsoft.data.schema.scriptdom.sql.whereclause.aspx The parser does recognise the WHERE clause as such, looking at the token stream. So, my question is, am I using the parser correctly? Right now the token stream seems to be the most useful feature of the parser...

My Test project:

public static void Main(string[] args)
{
    var parser = new TSql100Parser(false);

            IList<ParseError> Errors;
            IScriptFragment result = parser.Parse(
                new StringReader("Select col from T1 where 1 = 1 group by 1;" +
                    "select col2 from T2;" +
                    "select col1 from tbl1 where id in (select id from tbl);"),
                    out Errors);

            var Script = result as TSqlScript;

            foreach (var ts in Script.Batches)
            {
                Console.WriteLine("new batch");

                foreach (var st in ts.Statements)
                {
                    IterateStatement(st);
                }
            }
}

static void IterateStatement(TSqlStatement statement)
{
            Console.WriteLine("New Statement");

            if (statement is SelectStatement)
            {
                PrintStatement(sstmnt);
            }
}

Solution 1

Yes, you are using the parser correctly.

As Damien_The_Unbeliever points out, within the SelectStatement there is a QueryExpression property which will be a QuerySpecification object for your third select statement (with the WHERE clause).

This represents the 'real' SELECT bit of the query (whereas the outer SelectStatement object you are looking at has just got the 'WITH' clause (for CTEs), 'FOR' clause (for XML), 'ORDER BY' and other bits)

The QuerySpecification object is the object with the FromClauses, WhereClause, GroupByClause etc.

So you can get to your WHERE Clause by using:

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

which has a SearchCondition property etc. etc.

Solution 2

Quick glance around would indicate that it contains a QueryExpression, which could be a QuerySpecification, which does have the Where clause attached to it.

Solution 3

if someone lands here and wants to know how to get the whole elements of a select statement the following code explain that:

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);

Solution 4

I had to split a SELECT statement into pieces. My goal was to COUNT how many record a query will return. My first solution was to build a sub query such as

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

The problem was that in this case the order clause raises the error "The ORDER BY clause is not valid in views, inline functions, derived tables, sub-queries, and common table expressions, unless TOP or FOR XML is also specified"

So I built a parser that split a SELECT statment into fragments using the TSql100Parser class.

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;
    }
}

And to use this class simply:

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);

View more solutions

Comments

[Opflash]

Thanks! Had a hard time finding useful information on the topic so this answer will probably be useful to a lot of other devs in distress too.

[Bora]

@doza Awesome help. This cast saved me hours of working through tokens manually.

[Demodave]

There is no "spec.FromClauses" option

[Demodave]

How do you put your sql statement into the (SelectStatement)statement first?