SqlServer SqlParser 介紹及使用示例

Intro

最近發現在 Nuget 上有一個 SqlServer 的 SqlParser，利用 SqlParser 我們做到可以解析 SQL 的每一部分 ，nuget 包是公開的，可以拿來即用，只是缺少使用示例，很多功能需要自己去摸索

Nuget 包是 https://www.nuget.org/packages/Microsoft.SqlServer.Management.SqlParser/

下面我們來看使用示例吧

Sample

來看一個簡單的使用示例：

var?sqlText?=?"SELECT?TOP?100?*?FROM?dto.tabUsers?WHERE?Id?>?10?ORDER?BY?Id?DESC"; var?result?=?Parser.Parse(sqlText); Console.WriteLine(result.BatchCount); Console.WriteLine(result.Script.Sql);Console.WriteLine("-------------------------------"); IterateSqlNode(result.Script);

上面的 IterateSqlNode 方法是一個遍歷解析結果的一個方法，定義如下：

static?void?IterateSqlNode(SqlCodeObject?sqlCodeObject,?int?indent=0) {if?(sqlCodeObject.Children?==?null)?return;foreach?(var?child?in?sqlCodeObject.Children){Console.WriteLine($"{new?string('?',?indent)}Sql:{child.Sql},?Type:{child.GetType().Name}");IterateSqlNode(child,?indent+2);} }

上面示例的輸出結果如下：

從上面的輸出結果，我們大概可以看得出來一個 SELECT 查詢的 SQL 組成部分大概有以下部分：

• SqlBatch

• • SqlSelectSpecification

  • SqlSelectClause

  • SqlFromClause

  • SqlWhereClause

  • SqlQuerySpecification

  • SqlOrderByClause

  • SqlSelectStatement

每一個 SQL 語句可能會有多個語句，所以最外層是一個 SqlBatch，如果只有一個語句就對應著一個 SqlBatch，如果是一個 SELECT 查詢就是一個 SqlSelectStatement，由 SqlQuery(SqlSelectClause/SqlFromClause/SqlWhereClause)和 SqlOrderBy 組成，還有一些 GroupByClause/HavingClause 等從句，可以自己去嘗試一下

Practice

接著我們再來看一個實例，我們的帶分頁的列表查詢接口有幾個方法內部都是兩個方法，一個查詢列表，一個查詢總數，這樣的查詢大家是如何處理的呢？

我覺得有些繁瑣，合成一個查詢就好了，我嘗試著利用 SqlParser 來分析 SQL 語句，根據列表查詢的 SQL 自動生成一個查詢總數的 SQL，來看下面這個例子：

//?查詢列表?SQL sqlText?=?@" SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10 ORDER?BY?u.Id?DESC OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY "; IterateSqlNode(Parser.Parse(sqlText).Script);//?根據查詢列表的?SQL?生成?GET?COUNT?的?SQL static?string?GetCountSql(string?sql) {var?result?=?Parser.Parse(sql);if?(result.Script?is?null){throw?new?ArgumentException("Invalid?query",?nameof(sql));}var?sqlQuery?=?result.Script.Batches[0].Children.OfType<SqlSelectStatement>().FirstOrDefault()?.Children.OfType<SqlSelectSpecification>().FirstOrDefault()?.Children.OfType<SqlQuerySpecification>().FirstOrDefault();if?(sqlQuery?is?null){throw?new?ArgumentException("Invalid?query",?nameof(sql));}return?$@"SELECT?COUNT(1)?{sqlQuery.FromClause.Sql}?{sqlQuery.WhereClause.Sql}"; }

上面這個 SQL 是一個比較典型的我們常用的列表查詢 SQL，有的會更簡單一些只需要一個表，有些查詢條件會比較復雜一些，上面代碼輸出結果如下（內容有點長）：

Sql: SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10 ORDER?BY?u.Id?DESC OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY ,?Type:SqlBatchSql:SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10 ORDER?BY?u.Id?DESC OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY,?Type:SqlSelectStatementSql:SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10 ORDER?BY?u.Id?DESC OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY,?Type:SqlSelectSpecificationSql:SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10,?Type:SqlQuerySpecificationSql:SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From],?Type:SqlSelectClauseSql:u.Id?AS?UserId,?Type:SqlSelectScalarExpressionSql:u.Id,?Type:SqlColumnOrPropertyRefExpressionSql:u.Id,?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:Id,?Type:SqlIdentifierSql:UserId,?Type:SqlIdentifierSql:u.[Name]?AS?UserName,?Type:SqlSelectScalarExpressionSql:u.[Name],?Type:SqlColumnOrPropertyRefExpressionSql:u.[Name],?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:[Name],?Type:SqlIdentifierSql:UserName,?Type:SqlIdentifierSql:u.City?AS?[From],?Type:SqlSelectScalarExpressionSql:u.City,?Type:SqlColumnOrPropertyRefExpressionSql:u.City,?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:City,?Type:SqlIdentifierSql:[From],?Type:SqlIdentifierSql:FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id,?Type:SqlFromClauseSql:dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id,?Type:SqlQualifiedJoinTableExpressionSql:dbo.tabUsers?AS?u?WITH(NOLOCK),?Type:SqlTableRefExpressionSql:dbo.tabUsers,?Type:TwoPartObjectIdentifierSql:dbo,?Type:SqlIdentifierSql:tabUsers,?Type:SqlIdentifierSql:u,?Type:SqlIdentifierSql:NOLOCK,?Type:SqlTableHintSql:dbo.tabUserRoles?AS?r?WITH(NOLOCK),?Type:SqlTableRefExpressionSql:dbo.tabUserRoles,?Type:TwoPartObjectIdentifierSql:dbo,?Type:SqlIdentifierSql:tabUserRoles,?Type:SqlIdentifierSql:r,?Type:SqlIdentifierSql:NOLOCK,?Type:SqlTableHintSql:ON?r.UserId=?u.Id,?Type:SqlConditionClauseSql:r.UserId=?u.Id,?Type:SqlComparisonBooleanExpressionSql:r.UserId,?Type:SqlColumnOrPropertyRefExpressionSql:r.UserId,?Type:TwoPartObjectIdentifierSql:r,?Type:SqlIdentifierSql:UserId,?Type:SqlIdentifierSql:u.Id,?Type:SqlColumnOrPropertyRefExpressionSql:u.Id,?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:Id,?Type:SqlIdentifierSql:WHERE?u.Id>10,?Type:SqlWhereClauseSql:u.Id>10,?Type:SqlComparisonBooleanExpressionSql:u.Id,?Type:SqlColumnOrPropertyRefExpressionSql:u.Id,?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:Id,?Type:SqlIdentifierSql:10,?Type:IntegerLiteralExpressionSql:ORDER?BY?u.Id?DESC OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY,?Type:SqlOrderByClauseSql:u.Id?DESC,?Type:SqlOrderByItemSql:u.Id,?Type:SqlColumnOrPropertyRefExpressionSql:u.Id,?Type:TwoPartObjectIdentifierSql:u,?Type:SqlIdentifierSql:Id,?Type:SqlIdentifierSql:OFFSET?0?ROWS?FETCH?NEXT?10?ROWS?ONLY,?Type:SqlOffsetFetchClauseSql:0,?Type:IntegerLiteralExpressionSql:10,?Type:IntegerLiteralExpression

輸出的查詢 COUNT 的 SQL 語句如下：

SELECT?COUNT(1)?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id?WHERE?u.Id>10

看上去還是比較符合預期的，另外測試了幾種稍微比較復雜的情況也都是可以滿足我們的需要的

可以自動生成了 COUNT SQL 之后，我們就可以封裝一個方法只需要傳一個列表查詢的接口就可以了

大概實現如下：

public?async?Task<List<T>>?PageListWithTotalAsync<T>(string?sql,?PageSearchWithTotalDto?param) {var?countSql?=?GetCountSql(sql);var?execSql?=?$@" SET?@TotalCount=({countSql}); {sql} OFFSET?@Offset?ROWS?FETCH?NEXT?@PageSize?ROWS?ONLY";var?parameter?=?new?DynamicParameters(param);parameter.Output(param,?x?=>?x.TotalCount);using?var?conn?=?new?SqlConnection("");return?(await?conn.QueryAsync<T>(execSql,?parameter)).ToList(); }

PageSearchWithTotalDto 是一個 Model，定義如下：

public?class?PageSearchDto {private?int?_pageNumber?=?1;private?int?_pageSize?=?20;public?int?PageNumber{get?=>?_pageNumber;set{if?(value?>?0){_pageNumber?=?value;}}}public?virtual?int?PageSize{get?=>?_pageSize;set{if?(value?>?0){_pageSize?=?value;}}}public?int?Offset?=>?(PageNumber?-?1)?*?PageSize; }public?class?PageSearchWithTotalDto?:?PageSearchDto {public?int?TotalCount?{?get;?set;?} }

調用方式示例如下：

var?sql?=?@"SELECT?u.Id?AS?UserId,?u.[Name]?AS?UserName,?u.City?AS?[From]?FROM?dbo.tabUsers?AS?u?WITH(NOLOCK) INNER?JOIN?dbo.tabUserRoles?AS?r?WITH(NOLOCK)?ON?r.UserId=?u.Id WHERE?u.Id>10 ORDER?BY?u.Id?DESC";var?search?=?new?PageSearchWithTotalDto() {PageNum=1,PageSize=10, }; var?list?=?await?PageListWithTotalAsync(sql,?search); Console.WriteLine(search.TotalCOunt);

相比之前的代碼，已經簡潔了不少，又有一大波重復代碼可以消滅了，舒服~~

More

使用 SqlParser 來自動生成語句這種方案實際上并沒有應用到我們的項目中，但是我覺得這個不一樣的思路也許對你有所幫助，在你需要解析 SQL 的時候可以考慮一下這個 SqlParser。

前面的示例可以從 Github 上獲取 https://github.com/WeihanLi/SamplesInPractice/blob/master/SqlParserSample/Program.cs，有人在 Github 上提了一個關于開源這個 SqlParser 的 issue，有需要可以關注一下 https://github.com/microsoft/sqltoolsservice/issues/623

References

• https://github.com/WeihanLi/SamplesInPractice/blob/master/SqlParserSample/Program.cs

• https://www.nuget.org/packages/Microsoft.SqlServer.Management.SqlParser/

• https://github.com/microsoft/sqltoolsservice/issues/623

總結

以上是生活随笔為你收集整理的SqlServer SqlParser 介绍及基本使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。