和客戶IT單位的.NET架構師聊到ORM利器Dapper，雖然心想著強型別，但系統中的彈性化查詢非常多，取出的欄位又是溢出來的滿，手動建立類別(Class)會掉淚。
這篇筆記SQL Server 2016 結果集以JSON 格式+ Visual Studio(Paste Special)將json格式轉換成class的程式碼，下一篇再來筆記上保哥時介紹的好用工具LINQPad。
首先要確定資料庫是SQL Server 2016(昨天6/1上市)，2016起支援結果集以JSON 格式回傳，使用SQL Server 2016，我們更容易取得各種彈性化查詢的json內容。
實作的步驟主要分為 取得JSON及選擇性貼上成Class
1.準備測試環境:首先在資料庫中建立一個資料表
CREATE TABLE Customers(
ID varchar(11) primary key,
Name nvarchar(10),
Birthday date,
Marriage char(1),
Email varchar(50),
Tel varchar(20),
Salary numeric(13,2),
CreditCard varchar(19))
GO
2.準備測試環境:接著放幾筆資料(資料真的很重要，待會產生JSON的陽光空氣水)
INSERT INTO Customers
VALUES ('A123456789', N'朱立倫', '19610607', 0, 'a01@company.com', '02-77203699', 3000000, '3567-5678-9012-3456')
, ('B123456789', N'蔡英文', '19560831', 1, 'b02@company.com', '03-77203699', 2000000, '4567-1234-5678-9012')
, ('C123456789', N'宋楚瑜', '19420316', 1, 'c03@company.com', '04-77203699', 1000000, '5567-7890-1234-5678')
3.執行T-SQL查詢(回傳JSON格式)，模擬系統中的彈性化查詢。
一定要有1筆資料才會有Json格式回傳! 關鍵字是FOR JSON AUTO
SELECT TOP 1 * FROM Customers FOR JSON AUTO
執行後就可以將結果集以JSON格式回傳
4.接著把內容複製到剪貼簿(對!就是右鍵複製的那個剪貼簿)
{
"ID": "A123456789",
"Name": "朱立倫",
"Birthday": "1961-06-07",
"Marriage": "0",
"Email": "a01@company.com",
"Tel": "02-77203699",
"Salary": 3000000.00,
"CreditCard": "3567-5678-9012-3456"
}
5.打開地表最強IDE，新增一個類別Customer.cs
6.將游標移到class Customer{ } 區塊中，按下編輯(E)、選擇性貼上(Paste Special)、貼上JSON作為類別(J)
Customer類別就出現了每個SQL查詢欄位的屬性
很方便!又快又簡單，彈性將SQL結果集以json格式回傳，再用VS內建的功能選擇性貼上成為class屬性。
小結:
- 很多彈性T-SQL查詢語法在DTO層的我們，轉換使用Dapper會很方便。
- 交換檔案或傳統訊息傳輸文件大多用Excel，還是需要寫小工具再轉換成json，但轉換json的時間其實也可以轉換成class了。
SQL Server 2005之後就開始執行XML，試試看將結果集用XML格式回傳:
XML格式範例1
(語法SELECT TOP 1 * FROM Customers FOR XML AUTO,ELEMENTS)
<Customers>
<ID>A123456789</ID>
<Name>朱立倫</Name>
<Birthday>1961-06-07</Birthday>
<Marriage>0</Marriage>
<Email>a01@company.com</Email>
<Tel>02-77203699</Tel>
<Salary>3000000.00</Salary>
<CreditCard>3567-5678-9012-3456</CreditCard>
</Customers>
XML範例2
(語法SELECT TOP 1 * FROM Customers FOR XML AUTO)
<Customers ID="A123456789"
Name="朱立倫"
Birthday="1961-06-07"
Marriage="0"
Email="a01@company.com"
Tel="02-77203699"
Salary="3000000.00"
CreditCard="3567-5678-9012-3456" />
將資料放在剪貼簿後就可以改用XML資料轉換成Class。
- 目前專案內把訊息格式利用小工具轉換成XML格式，但因為每個欄位是個別的<item>，又有許多不需要放入類別的屬性值，因此不太符合上面兩種單純element或property的格式，還是沒辦法直接轉換，殘念。
- xml雖然也可以貼上成為類別，但總覺得格式排版比較寬，默默暗戀起簡潔一點的json。
今天晚上中華隊2:2踢和柬埔寨，FIFA排名191有點傷心。
參考:
Format Query Results as JSON with FOR JSON (SQL Server)