How to Parse SQL Code in C# and Generate a Tree Structure?

SQL code parsing in C#

Question: How to use C# to parse SQL code and generate a tree structure or any other structure? The parser should also generate a correct tree structure of the nested structure and return the statement types represented by the nodes in the tree structure. For example, if a node contains a loop condition, the node should be returned as "loop type".

Answer:

For Transact-SQL (Microsoft SQL Server), you can use the Microsoft.SqlServer.Management.SqlParser.Parser namespace, which is in SQL Server It is provided in the included assembly Microsoft.SqlServer.Management.SqlParser.dll and can be freely distributed.

The following is an example method to parse T-SQL as a string into a series 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 properties referenced above.

Token is this enumeration:

• [Tokens Enum (Microsoft.SqlServer.Management.SqlParser.Parser)](https://docs.microsoft.com/en -us/dotnet/api/ microsoft.sqlserver.management.sqlparser.parser.tokens?view=sqlparser-7.3)

and includes TOKEN_BEGIN, TOKEN_COMMIT, TOKEN_EXISTS and other constants.

Update: is now a separate nuget package: [Microsoft.SqlServer.Management.SqlParser](https://www.nuget.org/packages/Microsoft.SqlServer.Management. SqlParser).

The above is the detailed content of How to Parse SQL Code in C# and Generate a Tree Structure?. For more information, please follow other related articles on the PHP Chinese website!