# Kong.SqlParser

**Repository Path**: zoco0734/Kong.SqlParser

## Basic Information

- **Project Name**: Kong.SqlParser
- **Description**: T-SQL语句解析,基于微软Microsoft.SqlServer.Management.SqlParser组件的研究项目，可解析SQL语句中所使用的数据对象（表/视图）
- **Primary Language**: C#
- **License**: MIT
- **Default Branch**: dev
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2023-12-02
- **Last Updated**: 2024-06-06

## Categories & Tags

**Categories**: Uncategorized

**Tags**: SqlParser, NetCore

## README

# Kong.SqlParser

### 介绍
T-SQL语句解析,基于微软的Microsoft.SqlServer.Management.SqlParser组件的研究项目。组件无需依赖SqlServer数据库即可达到对SQL语法的校验与分析，支持解析SQL中的结构如：Select,From,Where,GroupBy,OrderBy等基本组成部分。目前正初步研究中...
### 软件架构
- 框架：.NetCore 6.0
- 语言：C# 10
- 依赖包：[Microsoft.SqlServer.Management.SqlParser (170.9.0)](https://www.nuget.org/packages/Microsoft.SqlServer.Management.SqlParser)
### 使用示例
#### 一，解析SQL中所用到的数据对象（表/视图）
##### 1.简单SQL解析
```C#
var sql = "SELECT * FROM dbo.AgentA T1 WHERE T1.ID = 1";
var output = ParserUtils.ParseDataObjects(sql);
Console.WriteLine(JsonSerializer.Serialize(output));
/* 输出:
    {
        "IsSuccess": true,
        "Message": "",
        "Data": [
            {
                "SchemaName": "dbo",
                "ObjectName": "AgentA",
                "Alias": "T1"
            }
        ]
    }
*/
```
##### 2.多表连接SQL解析
```C#
var sql = "SELECT * FROM AgentA INNER JOIN AgentB on AgentB.MID = AgentA.ID ";
var output = ParserUtils.ParseDataObjects(sql);
Console.WriteLine(JsonSerializer.Serialize(output));
/* 输出:
    {
        "IsSuccess": true,
        "Message": "",
        "Data": [
            {
                "SchemaName": null,
                "ObjectName": "AgentA",
                "Alias": null
            },
            {
                "SchemaName": null,
                "ObjectName": "AgentB",
                "Alias": null
            }
        ]
    }
*/
```
##### 3.多模式多表SQL解析
```C#
var sql = "SELECT * FROM dbo1.AgentA INNER JOIN dbo2.AgentB t2 on t2.MID = AgentA.ID";
var output = ParserUtils.ParseDataObjects(sql);
Console.WriteLine(JsonSerializer.Serialize(output));
/* 输出:
    {
        "IsSuccess": true,
        "Message": "",
        "Data": [
            {
                "SchemaName": "dbo1",
                "ObjectName": "AgentA",
                "Alias": null
            },
            {
                "SchemaName": "dbo2",
                "ObjectName": "AgentB",
                "Alias": "t2"
            }
        ]
    }
*/
```
##### 4.包含注释的SQL解析
```C#
var sql = @"
SELECT
    * 
FROM
    AgentA
    INNER JOIN AgentB ON AgentB.MID = AgentA.ID 
    --INNER JOIN AgentC ON AgentC.MID = AgentA.ID 	    
    INNER JOIN AgentE ON AgentE.MID = AgentA.ID
";
var output = ParserUtils.ParseDataObjects(sql);
Console.WriteLine(JsonSerializer.Serialize(output));
/* 输出:
    {
        "IsSuccess": true,
        "Message": "",
        "Data": [
            {
                "SchemaName": null,
                "ObjectName": "AgentA",
                "Alias": null
            },
            {
                "SchemaName": null,
                "ObjectName": "AgentB",
                "Alias": null
            },
            {
                "SchemaName": null,
                "ObjectName": "AgentE",
                "Alias": null
            }
        ]
    }
*/
```
##### 5.SQL语法正确性解析
```C#
    var sql = "SELECT  FROM dbo1.AgentA INNER JOIN dbo2.AgentB t2 on t2.MID = AgentA.ID";
    var output = ParserUtils.ParseDataObjects(sql);
    Console.WriteLine(JsonSerializer.Serialize(output, new JsonSerializerOptions() { Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping }));
    /* 输出:
        {
            "IsSuccess": false,
            "Message": "“FROM”附近有语法错误。",
            "Data": null
        }
     */
```