當學會了如何透過 SQL Server 操作 JSON 欄位資料,接著就是去瞭解如何透過 C# 取得從 SQL Server 回傳的 JSON 資料。這篇文章將會提及透過 ADO.NET 讀取 JSON 資料的方法,以及讀取時的注意事項。
準備環境
這篇文章我會準備一個範例資料庫,然後建立一個 .NET 專案:
-
下載 AdventureWorks 範例資料庫
這邊我是建立經量型的 AdventureWorksLT2019.bak
回來用,資料庫名稱為 AdventureWorksLT2019
!
-
建立本文所需範例專案
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
-
先安裝必要的 NuGet 套件
dotnet add package Microsoft.Data.SqlClient
-
使用 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;
-
建立 SqlConnection 資料庫連線物件
using SqlConnection conn = new SqlConnection(builder.ConnectionString);
記得加上 using
關鍵字,否則會有 Connection Leaks 的問題發生!
-
建立 SqlCommand 並透過 ExecuteReader 取得結果
我使用以下 T-SQL 為例進行示範:
SELECT TOP (3) ProductID, Name, ProductNumber
FROM [SalesLT].[Product]
FOR JSON PATH
首先,先拿到 SSMS 執行,你會得到以下結果,你可以發現結果集(ResultSet)欄位的標題無法自訂,他會產生一個亂數的欄位名稱,並且回傳一筆資料:
不過,因為我們只回傳前 3 筆資料,資料量相對較小。如果我把 TOP (3)
移除的話,回應的資料筆數就會被拆解成 12 筆,每筆 2033
字元,你可以從 SSMS 看出這個狀況:
所以,我們的程式碼必須對這個狀況作處理,你必須使用 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
相關連結