How to parse SQL files using C# ?

Question

Hi,

Can anyone please help me?

I am having bunch of .sql files. I want to parse (validate) those files before executing them on server.

I have many online sites which parse the code. But I want to parse the code using C#.

So please can you guide. If there is any tool, dll I need to use.

I just want to parse the file and not execute it

Answer 1

Antler Parser Generator is your best bet. There might be a pre-defined grammar for the version of SQL you want to parse already.

Answer 2

A quick google threw up http://www.antlr.org/ with a plugin for ms sql or one of the other sql dialects

Answer 3

Visual Studio used to have something called Visual Studio for Database Professionals. It was later rolled into Team Developer.

Anyway, they "compiled" the sql code by executing it in a database and testing the results. I imagine if that's how they were verifying it worked, it's probably the best way for you to do as well.

Answer 4

A way to do it is to execute it on the server, except with SET PARSEONLY ON (so that it's parsed but not executed): I think that's how the MS Query Analyzer does it.

Answer 5

You can have procedure which can get the query as input and
in SQL Server side you can have a SET PARSEONLY ON statement and then "run" the query. The query won't actually run, but any errors will be reported.

Answer 6

You should ask yourself twice (or maybe even three times :-)) if you really want to do this.

Even if you find a solution, that seems to work for the files you have now, there will probably be all sorts of corner cases that wont be covered. The suggestion of having a local database engine parse it, is sort-of workable, but there can still be many small syntax differences between what you do locally and what happens on the server.

My advice is to let the server parse and validate it - it's basically the only way to be really sure.

Answer 7

Here is a .NET SQL Parser that can help you validate SQL syntax offline in your C# program. http://www.sqlparser.com

Anyway, It's a commerical product.

Answer 8

The DMS Software Reengineering Toolkit has SQL parsers, used to build many SQL-based tools.

One such tool is a FormatterFormatter, which parses the source text and formats the result nicely. If the formatter fails to parse the source, it produces a nonzero process status.

You could simply "format" a file; if the formatting fails, it isn't legal syntax.

These formatters are commercial products.