tags:

views:

387

answers:

2

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);
            }
}
+1  A: 

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

Damien_The_Unbeliever
+7  A: 

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.

doza
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.
JC Denton