The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何從 C# 透過 SqlClient 取得 SQL Server 產生的 JSON 資料

當學會了如何透過 SQL Server 操作 JSON 欄位資料,接著就是去瞭解如何透過 C# 取得從 SQL Server 回傳的 JSON 資料。這篇文章將會提及透過 ADO.NET 讀取 JSON 資料的方法,以及讀取時的注意事項。

準備環境

這篇文章我會準備一個範例資料庫,然後建立一個 .NET 專案:

  1. 下載 AdventureWorks 範例資料庫

    這邊我是建立經量型AdventureWorksLT2019.bak 回來用,資料庫名稱為 AdventureWorksLT2019

  2. 建立本文所需範例專案

    mkdir JsonColumnsDemo
    cd JsonColumnsDemo
    
    dotnet new console -n SqlClientDemo
    
    dotnet new sln
    dotnet sln add .\SqlClientDemo\SqlClientDemo.csproj
    
    dotnet new gitignore
    git init
    git add .
    git commit -m "Initial commit"
    

使用 Microsoft.Data.SqlClient (ADO.NET) 讀取 JSON 資料

cd SqlClientDemo
  1. 先安裝必要的 NuGet 套件

    dotnet add package Microsoft.Data.SqlClient
    
  2. 使用 SqlConnectionStringBuilder 建立連接字串

    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    
    builder.DataSource = @".\SQLEXPRESS";
    //builder.UserID = "<your_username>";
    //builder.Password = "<your_password>";
    builder.InitialCatalog = "AdventureWorksLT2019";
    builder.IntegratedSecurity = true;
    builder.MultipleActiveResultSets = true;
    builder.TrustServerCertificate = true;
    
  3. 建立 SqlConnection 資料庫連線物件

    using SqlConnection conn = new SqlConnection(builder.ConnectionString);
    

    記得加上 using 關鍵字,否則會有 Connection Leaks 的問題發生!

  4. 建立 SqlCommand 並透過 ExecuteReader 取得結果

    我使用以下 T-SQL 為例進行示範:

    SELECT TOP (3) ProductID, Name, ProductNumber
    FROM [SalesLT].[Product]
    FOR JSON PATH
    

    首先,先拿到 SSMS 執行,你會得到以下結果,你可以發現結果集(ResultSet)欄位的標題無法自訂,他會產生一個亂數的欄位名稱,並且回傳一筆資料:

    SELECT TOP (3) ProductID, Name, ProductNumber FROM [SalesLT].[Product] FOR JSON PATH

    不過,因為我們只回傳前 3 筆資料,資料量相對較小。如果我把 TOP (3) 移除的話,回應的資料筆數就會被拆解成 12 筆,每筆 2033 字元,你可以從 SSMS 看出這個狀況:

    image

    所以,我們的程式碼必須對這個狀況作處理,你必須使用 StringBuilder 將每一筆回傳的資料串在一起,才是一份完整的 JSON 資料:

    var sql = "SELECT ProductID, Name, ProductNumber FROM [SalesLT].[Product] FOR JSON PATH";
    using var cmd = new SqlCommand(sql, conn);
    
    var jsonResult = new StringBuilder();
    
    conn.Open();
    
    var reader = cmd.ExecuteReader();
    if (!reader.HasRows)
    {
        jsonResult.Append("[]");
    }
    else
    {
        while (reader.Read())
        {
            jsonResult.Append(reader.GetValue(0).ToString());
        }
    }
    
    Console.WriteLine(jsonResult.ToString());
    

    如果回傳的 JSON 結果資料量過大時,可能會回傳更多筆資料,我有測試過,如果用以下 T-SQL 來產生 JSON 結果,就會得到 177 筆結果:

    SELECT * FROM [SalesLT].[Customer] FOR JSON PATH
    

    參考資料: Use FOR JSON output in a C# client app

相關連結

留言評論