Parsing nested blocks in SQL using C#

Question

i want to perform analysis on SQL code. In this i would like to separate the blocks in SQL code. for example, in the code below

for condition1 loop
stmt1;
for condition2 loop
stmt2;
end loop;
end loop;

i want to separate these two blocks and create a data structure which will contain outer loop block and will also have inner loop block.

which is the best way to do so? is there any parser available which can handle the nested loops in the best possible way?

Answer 1

I would suggest composit pattern for this kind of blocks (and also for SELECT, CASE, WITH and similar).

public interface ICodeEntity { }
public interface IObjectEntity { }
public class Column: IObjectEntity
{
    public string name;
    public System.Data.DbType type;
}
public class Table: IObjectEntity
{
    public List<Column> columns = new List<Column>();
    public string alias;
}
public class Where : ICodeEntity { }
public class GroupBy : ICodeEntity { }
public class OrderBy : ICodeEntity { }
public class Select : Table, ICodeEntity
{
    public List<Table> joinList = new List<Table>();
    public Where where;
    public GroupBy groupBy;
    public OrderBy orderBy;
}
public class Condition : ICodeEntity { }
public class If : ICodeEntity
{
    public Condition condition;
    public List<ICodeEntity> codeList = new List<ICodeEntity>();
}

and somewhere in the program:

If if1 = new If();
if1.codeList.Add(new If());

see related links:
CodeProject: Illustrated GOF Design Patterns in C#
data&object factory: Composite pattern