和客戶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)