短小精悍的.NET ORM神器 -- Dapper

應該有很多人像我一樣,對LINQ的依賴已經到達"LINQ or Die!"(不LINQ,吾寧死)的地步,到了需要存取DB的場合,打死也不想再走ADO.NET + DataTable、DataRow的回頭路。不過,在專案引用EntityFramework或其他ORM解決方案(NHibernation、SubSonic...),固然嚴謹紮實,卻也多出額外工作--要依照Schema在專案定義Entity物件、資料庫變更時要記得同步更新Entity定義,遇到多TABLE JOIN查詢得另外宣告自訂類別承接查詢結果(我還為此寫過潛盾機)。對於要求嚴謹精準的中大型系統,這類準備工作屬無法避免的代價,但在一些力求快速輕巧的開發情境(例如: 轉檔工具、範例程式、單純但大量的報表需求...),所有對資料庫的存取(Table、View、Stored Procedure)需預先定義,還必須隨時保持與資料庫一致,引用EF之類的架構便顯笨重。

前陣子提到用TVP傳WHERE IN參數的做法,在FB專頁得到網友Lane Kuo的回饋(在此致謝),得知好物一枚 -- Dapper(英文原義是短小精悍,用過即知Dapper不負其名),一個精簡小巧的.NET ORM工具,不需在專案裡新增DB Table、View或Stored Procuedure定義,只要取得IDbConnection(SqlConnection、OracleConnection、MySqlConnection...都適用),就能立即享受接近EF、LINQ to SQL等ORM架構的便利,最重要的是能用LINQ把玩資料,這才是上流社會的程式寫法呀!

不囉嗦,打開NuGet就能找到它:

試用之後,感動到直起雞皮疙瘩,這就是我一直在尋找的,好吃又不黏牙的DB LINQ解決方案~

以下整理Dapper的特色:

第一點絕對要大推! 之前在公司推廣LINQ/EF,最常被挑戰的罩門 -- "過去不管再複雜的SELECT FROM WHERE,丟給ADO.NET就能拿到DataTable;弄了LINQ/EF之後,不定義Entity或自訂類別就收不到結果。幹! 你知道我的系統裡有多少個花式SELECT嗎?"

我一直覺得這是由傳統ADO.NET開發邁向LINQ/EF世界的最大阻礙,也動過腦筋想讓ExecuteStoreQuery<T>的T接受dynamic,而Dapper實現了!

            using (var cn = new SqlConnection(cnStr))
            {
              //1) 不需要定義POCO物件,直接SELECT結果轉成.NET物件集合!(酷)
              //   注意: 結果為IEnumerable<dynamic>,會喪失強型別優勢
                //2) 可宣告及傳入具名參數
                var list = cn.Query(
              "SELECT * FROM Products WHERE CategoryID=@catg", new { catg = 2 });
                foreach (var item in list)
                {
                    Console.WriteLine("{0}.{1}({2})",
                        item.ProductID, item.ProductName, item.QuantityPerUnit);
                }
            }

另外,Dapper在SQL語法裡可使用具名參數(如@catg),不像ExecuteStoreQuery只能用{0}、{1},可讀性較佳。

執行結果:

3.Aniseed Syrup(12 - 550 ml bottles)
4.Chef Anton's Cajun Seasoning(48 - 6 oz jars)
5.Chef Anton's Gumbo Mix(36 boxes)
6.Grandma's Boysenberry Spread(12 - 8 oz jars)
8.Northwoods Cranberry Sauce(12 - 12 oz jars)
15.Genen Shouyu(24 - 250 ml bottles)
44.Gula Malacca(20 - 2 kg bags)
61.Sirop d'erable(24 - 500 ml bottles)
63.Vegie-spread(15 - 625 g jars)
65.Louisiana Fiery Hot Pepper Sauce(32 - 8 oz bottles)
66.Louisiana Hot Spiced Okra(24 - 8 oz jars)
77.Original Frankfurter grune Sose(12 boxes)

使用dynamic物件固然方便,但會喪失強型別在編譯時期的防錯優勢,因此Dapper當然也支援將查詢結果應對到自訂類別。此外,以下範例一併示範Dapper能直接將參數陣列展開成WHERE col IN (@arg1, @arg2, @arg3)的特異功能,相當方便。

        public class SimpProduct
        {
            public int ProductID { get; set; }
            public string ProductName { get; set; }
        }
 
        private static void Test()
        {
            using (var cn = new SqlConnection(cnStr))
            {
                //1) 將SELECT結果轉成指定的型別(屬性與欄位名稱要一致)
                //2) 直接傳數字陣列作為WHERE IN比對參數
                //   =>自動轉成WHERE col in (@arg1,@arg2,@arg3)
                var list = cn.Query<SimpProduct>(
                    "SELECT * FROM Products WHERE CategoryID IN @catgs", 
                    new { catgs = new int[] { 1, 4 } });
                foreach (var item in list)
                {
                    Console.WriteLine("{0}.{1}",
                        item.ProductID, item.ProductName);
                }
            }
        }

執行結果:

1.Chai
2.Chang
11.Queso Cabrales
12.Queso Manchego La Pastora
24.Guarana Fantastica
31.Gorgonzola Telino
32.Mascarpone Fabioli
33.Geitost
34.Sasquatch Ale
35.Steeleye Stout
38.Cote de Blaye
39.Chartreuse verte
43.Ipoh Coffee
59.Raclette Courdavault
60.Camembert Pierrot
67.Laughing Lumberjack Lager
69.Gudbrandsdalsost
70.Outback Lager
71.Flotemysost
72.Mozzarella di Giovanni
75.Rhonbrau Klosterbier
76.Lakkalikoori

除了查詢,Dapper提供.Execute()執行SQL資料更新,最特別的是它可以一次傳進多組參數,用不同參數重複執行同一SQL操作,批次作業時格外有用。

            using (var cn = new SqlConnection(cnStr))
            {
                //1) 可執行SQL資料更新指令,支援參數
                //2) 以陣列方式提供多組參數,可重複執行同一SQL指令
                cn.Execute(@"INSERT INTO Region VALUES (@id, @desc)",
                    new[] {
                        new { id = 5, desc = "Taiwan" },
                        new { id = 6, desc = "Mars" }
                    });
            }

Dapper還可以在命令中一次包含多組SELECT,透過QueryMultiple()後再以Read()或Read<T>分別取出查詢結果。

        public class SimpCust
        {
            public string ContactName { get; set; }
            public string ContactTitle { get; set; }
        }
        private static void Test4()
        {
            using (var cn = new SqlConnection(cnStr))
            {
                //一次執行多組查詢,分別取回結果
                var multi = cn.QueryMultiple(@"
SELECT * FROM Customers WHERE CustomerId = @id
SELECT * FROM Orders WHERE CustomerId = @id
", new { id = "ALFKI" });
                var cust = multi.Read<SimpCust>().First();
                Console.WriteLine("{0} / {1}", cust.ContactName, cust.ContactTitle);
                var ords = multi.Read(); //取回IEnumerable<dynamic>
                Console.WriteLine("Orders Count = {0}", ords.Count());
            }
        }

執行結果:

Maria Anders / Sales Representative
Orders Count = 6

至於StoredProcedure,一樣可以透過Dapper Query()查詢及使用Execute()執行,直接取回SELECT結果或使用Output參數都難不倒它。

            using (var cn = new SqlConnection(cnStr))
            {
                //呼叫StoredProcedure查詢資料
                var res = 
                    cn.Query("dbo.CustOrderHist", new { CustomerID = "ALFKI" }, 
                             commandType: CommandType.StoredProcedure);
                foreach (var item in res)
                {
                    Console.WriteLine("{0} = {1}", item.ProductName, item.Total);
                }
                //取回ReturnValue及Output參數
/*
CREATE PROCEDURE AddOne
    @n INT, @r INT OUPUT
AS 
BEGIN 
SET @r = @n + 1
RETURN 1024
END
*/
                var p = new DynamicParameters();
                p.Add("@n", 1);
                p.Add("@r", dbType: DbType.Int32, 
                    direction: ParameterDirection.Output);
                p.Add("@rtn", dbType: DbType.Int32, 
                    direction: ParameterDirection.ReturnValue);
                cn.Execute("dbo.AddOne", p, 
                    commandType: CommandType.StoredProcedure);
                Console.WriteLine("@r = {0}, return = {1}",
                    p.Get<int>("@r"), p.Get<int>("@rtn"));
            }
        }

執行結果:

Aniseed Syrup = 6
Chartreuse verte = 21
Escargots de Bourgogne = 40
Flotemysost = 20
Grandma's Boysenberry Spread = 16
Lakkalikoori = 15
Original Frankfurter grune Sose = 2
Raclette Courdavault = 15
Rossle Sauerkraut = 17
Spegesild = 2
Vegie-spread = 20
@r = 2, return = 1024

除了呼叫應用的便利性,Dapper很強調效能,在一些實測中明顯勝過EF及其他ORM架構。

【結論】

Dapper的輕巧犀利令人驚豔,讚嘆之餘頗有相見恨晚之感,不過現在知道也不算遲。未來中大型專案我想仍會維持預先定義Entity、Model、ViewModel,力求嚴謹分明的原則,但在一些需要巷戰搶灘近身肉博的場合,Dapper將會是我的好伙伴!

歡迎推文分享:
Published 15 May 2014 06:30 AM 由 Jeffrey
Filed under: ,
Views: 69,276



意見

# Super said on 16 May, 2014 01:45 AM

果然是神器! 真是相見恨晚, 令人喜極而泣呀!!

# 我是誰 said on 11 April, 2016 09:54 PM

2016 了...我現在才發現,QQ。

# Maxi said on 13 May, 2016 04:23 AM

2016 了...我現在才發現,再忝一人

# 相見恨晚 said on 18 July, 2016 05:04 AM

Hello 黑大,

如果以三層式架構來規劃 DAL.BLL.UI

在Dapper中您建議SQL語法放在哪一層呢 (有點困惑),謝謝!

# Jeffrey said on 18 July, 2016 08:50 AM

to 相見恨晚,Dapper涉及邏輯與資料庫高度相關,依關注點分離原則,不適合放在BLL或UI,寫在DAL是較佳選擇。

# Alex said on 23 November, 2016 08:33 PM

批次插入作業 我是利用List<DynamicParameters>

           var dynamicParametersList = new List<DynamicParameters>();

           foreach(var region in regionList)

           {

                       dynamicParametersList.Add(new DynamicParameters(){

                              id = region.Id,

                              desc = region.Desc

                       });

           }

           using (var cn = new SqlConnection(cnStr))

           {

               cn.Execute(@"INSERT INTO Region VALUES (@id, @desc)", dynamicParametersList);

           }

# Jeffrey said on 23 November, 2016 09:10 PM

to Alex, List<DynamicParameters>這招不錯,感謝分享。

# 無名 said on 25 August, 2017 07:43 AM

黑大您好 想問一下是否Like 也有像 In一樣的參數化查詢

像您上面寫的

conn.Query("Select ProductName Fron Products Where ProductName like @ProductName ",new { ProductName = new string[] { "蘋果%","哈密瓜%" } }))

類似這樣

找了一陣子好像都沒有如此用法

# Jeffrey said on 25 August, 2017 09:56 AM

to 無名,你的需求非屬典型應用,應無現成方法可用,只能靠自己組裝,怛不算太難寫,可參考這篇:blog.darkthread.net/post-2015-08-17-where-1-1-and-performance.aspx

# 無名 said on 27 August, 2017 09:30 PM

感謝黑大指點

看來沒有捷徑可走..

# cheng said on 13 September, 2017 10:36 AM

黑大您好, 請問我一個資料表,有將近200多個欄位,假設使用者會撈50個欄位出來,若後續使用者要增加撈進model的欄位,是否只能手動model中新增該屬性,使dapper用強行別自行對應?

# Jeffrey said on 13 September, 2017 08:37 PM

to cheng, 有一些依據 SQL 查詢結果快速產生 Model 的小技巧可節省手工,例如使用 LINQPad: kevintsengtw.blogspot.tw/.../dapper-linqpad-sql-command.html

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<May 2014>
SunMonTueWedThuFriSat
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication