Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Parsing SQL code in C# [closed]

Tags:

c#

sql

I want to parse SQL code using C#.

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

It should also return which kind of statement the node of this tree represents.

For example, if the node contains a loop condition then it should return 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?

like image 308
Archie Avatar asked Feb 26 '09 04:02

Archie


People also ask

What is SQL in C?

Structured Query Language (SQL) is the language used in relational database management systems (RDBMS) to query, update, and delete data. SQL is a standard query language for RDBMS. SQL language's queries are also known as SQL commands or SQL statements.

Can you use SQL with C?

You can code SQL statements in a C or C++ program wherever you can use executable statements. Each SQL statement in a C or C++ program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.

How SQL queries are parsed?

SQL Parsing The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which means that only the application, and not the database itself, can reduce the number of parses.

How do you parse in SQL?

PARSE() function in SQL ServerPARSE() function can convert any string value to Numeric or Date/Time format. If passed string value cannot be converted to Numeric or Date/Time format, it will result to an error. PARSE() function relies on Common Language Runtime to convert the string value.


2 Answers

Specifically for Transact-SQL (Microsoft SQL Server) you can use the Microsoft.SqlServer.Management.SqlParser.Parser namespace available in Microsoft.SqlServer.Management.SqlParser.dll, an assembly included with SQL Server and which can be freely distributed.

Here's an example method for parsing T-SQL as a string into a sequence of tokens:

IEnumerable<TokenInfo> ParseSql(string sql) {     ParseOptions parseOptions = new ParseOptions();     Scanner scanner = new Scanner(parseOptions);      int state = 0,         start,         end,         lastTokenEnd = -1,         token;      bool isPairMatch, isExecAutoParamHelp;      List<TokenInfo> tokens = new List<TokenInfo>();      scanner.SetSource(sql, 0);      while ((token = scanner.GetNext(ref state, out start, out end, out isPairMatch, out isExecAutoParamHelp)) != (int)Tokens.EOF)     {         TokenInfo tokenInfo =             new TokenInfo()             {                 Start = start,                 End = end,                 IsPairMatch = isPairMatch,                 IsExecAutoParamHelp = isExecAutoParamHelp,                 Sql = sql.Substring(start, end - start + 1),                 Token = (Tokens)token,             };          tokens.Add(tokenInfo);          lastTokenEnd = end;     }      return tokens; } 

Note that the TokenInfo class is just a simple class with the above-referenced properties.

Tokens is this enumeration:

  • Tokens Enumeration (Microsoft.SqlServer.Management.SqlParser.Parser)

and includes constants like TOKEN_BEGIN, TOKEN_COMMIT, TOKEN_EXISTS, etc.

like image 163
Andrey Belykh Avatar answered Sep 22 '22 13:09

Andrey Belykh


Scott Hanselman recently featured the Irony project which includes a sample SQL parser.

like image 27
user423430 Avatar answered Sep 25 '22 13:09

user423430