Parsing SQL code in C#

Question

i want to parse the SQL code using C#.

Specifically is there any parser freely available which can parse the SQL code and generate a tree or any other structure out of it? it should also generate proper tree for nested structures.

As well as it should return to me which kind of statement the node of this tree represents.

e.g. if the node contains loop condition then it should return to me that this is a "loop type" of a node.

Or is there any way by which i can parse the code in C# and generate a tree of the type i want?

Thanks

Answer 1

This is what you are after: http://www.temporal-wave.com/index.php?option=com_content&view=article&id=48:sqlparser&catid=35:products&Itemid=53

(only $12,000!)

Answer 2

Use Microsoft Entity Framework (EF)

It has a "Entity SQL" parser which builds an expression tree

using System.Data.EntityClient;
...
EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
var queryExpression = cmd.Expression;
....
conn.Close();

Or something like that, check it out on MSDN

And it's all on Ballmers tick :-)

There is also one on codeproject

http://www.codeproject.com/KB/dotnet/SQL_parser.aspx

Good luck

Answer 3

Try ANTLR - There are a bunch of SQL grammars on there.

Answer 4

Try GOLD Parser, it's a powerful and easy to learn BNF engine. You can search the grammars already made for what you want (ie: SQL ANSI 89 Grammar).

I started using this for HQL parsing (the NHibernate query language, very similar to SQL), and it's awesome. Now Fabio Maulo (NH project leader) is working on that, you can view how the code parsing is being done in the uNHAddins Project (in a folder called Artorius)

Answer 5

The Temporal Wave parser is aimed at embedding within professional development tools, databases and so on. Anyone who thinks that a parser for the entire T-SQL language spec can be written in a week, or for less than $12K of development time has obviously never tried to write one or is paid extremely poorly :-)

There are plenty of half baked things out there, and they will do the job for home made tools and so on but they won't be useful for anything that needs to parse the entire language. You should also be aware that the free grammars for the parsing tools are free for a reason and that is they will need a lot of time and attention to make them do what you want. Again, if all you want is to look at the parameters of stored procedures you will find things that fit your needs well enough.

Answer 6

VSTS 2008 Database Edition GDR includes assemblies that handle SQL parsing and script generation that you can reference from your project. Database Edition uses the parser to parse the script files to represent in-memory model of your database and then uses the script generator to generate SQL scripts from the model. I think there are just two assemblies you need to have and reference in your project. If you don't have the database edition, you may install the trial version to get the assemblies or there might be another way to have them without installing the database edition. Check out the following link. Data Dude:Getting to the Crown Jewels .

Answer 7

You may take a look at a commerical component: general sql parser at http://www.sqlparser.com It supports SQL syntax of Oracle, T-SQL, DB2 and MySQL.

Answer 8

I am trying to use the following snippet in a .NET 3.5 app. However, it seems the the expression property of the EntityCommand object is no longer available. Did it get deprecated? Perhaps I am missing a reference. Please reply if you have any information.

using System.Data.EntityClient; 
... 
EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString); 
conn.Open(); 
EntityCommand cmd = conn.CreateCommand(); 
cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t"; 
var queryExpression = cmd.Expression; 
.... 
conn.Close();