The Will Will Web

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

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

📅 2023/01/09 19:25 📁 .NET Framework, .NET Core, SQL Server

當學會了如何透過 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

相關連結

留言評論