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?
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.
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.
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.
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.
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:
and includes constants like TOKEN_BEGIN
, TOKEN_COMMIT
, TOKEN_EXISTS
, etc.
Scott Hanselman recently featured the Irony project which includes a sample SQL parser.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With