應該有很多人像我一樣,對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將會是我的好伙伴!


Comments

# by Super

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

# by 我是誰

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

# by Maxi

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

# by 相見恨晚

Hello 黑大, 如果以三層式架構來規劃 DAL.BLL.UI 在Dapper中您建議SQL語法放在哪一層呢 (有點困惑),謝謝!

# by Jeffrey

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

# by Alex

批次插入作業 我是利用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); }

# by Jeffrey

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

# by 無名

黑大您好 想問一下是否Like 也有像 In一樣的參數化查詢 像您上面寫的 conn.Query("Select ProductName Fron Products Where ProductName like @ProductName ",new { ProductName = new string[] { "蘋果%","哈密瓜%" } })) 類似這樣 找了一陣子好像都沒有如此用法

# by Jeffrey

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

# by 無名

感謝黑大指點 看來沒有捷徑可走..

# by cheng

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

# by Jeffrey

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

# by Karen

黑大您好,假設一隻程式中有好幾個花式select,請問這些映對的類別,通常放置何處呢?

# by Jeffrey

to Keren, 你是指依據查詢結果產生的類別定義嗎? 我習慣在專案開一個Entities(或用Poco/Dto之類的名稱)資料夾用來擺放這些類別集中管理。

# by kaern

您好,想再請問,動態的where條件該如何組合? 上網查到需要用到dapper.sqlbuilder的組件,但想再了解,是否一定要用到這個組件,還是用原本的dapper就能達到我要的效果?

# by Jeffrey

to keren, 以下是我慣用的動態 WHERE 寫法:http://blog.darkthread.net/post-2015-08-17-where-1-1-and-performance.aspx

# by Hao

黑大您好,請問要開發MVC架構的網站用dapper合適嗎?(之前只有Web form+ADO.NET的經驗)謝謝。

# by Jeffrey

to Hao, ASP.NET MVC 處理資料庫目前的主流選擇不外乎EF、Dapper、ADO.NET,我自己是EF+Dapper混用(單筆更新走EF或自製ORM、查詢走Dapper),對此我另外也寫過文章 https://blog.darkthread.net/blog/linq-or-direct-sql/ 希望對你有所幫助。

# by dpi

2018 才發現如此好物,感謝暗黑大 (好想挖洞把自己埋了) 目前 CURD 就直接用 EF,有 JOIN 的 SELECT 改用 Dapper 直覺多了,好讚

# by Andrew

正在研究 var p = new DynamicParameters(); p.Add("@n", 1); 的p怎麼放到 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" } }); } 的 new[] { new { id = 5, desc = "Taiwan" }, new { id = 6, desc = "Mars" } } 這段...2019感謝有你XD...測試去

# by 奈特

在某BLOG 看到一模一樣的文章 (連圖都一樣),雖然有貼出處,但不知道是否有經過授權? http://ishellworld.blogspot.com/2017/11/net-orm-dapper.html

# by Jeffrey

to 奈特,感謝通報。

# by Nick

2021年了,現在才發現…

# by Sam

2021年了,現在才發現 too

# by 恐龍

2021 年還在發現中 +1

# by FeLiZ

2021年發現 +1 ...但有發現總是好的,謝謝黑暗大

# by otaku119

板主你好: 正在使用DAPPER來存取DB, 有個觀念想請教板主 個人需要把所有DB內的各table資料讀取出來 var sql = "SELECT * FROM WP0ACCH"; var results = FBconn.Query<WP0ACCH>(sql).ToList(); 因為table數量眾多,有無一種方式可以把上述的 FBconn.Query<WP0ACCH>(sql).ToList(); 變成: FBconn.Query<AAAA>(sql).ToList(); 動態的去接收各table的object? 能否請板主指導一下關鍵字 謝謝

# by Jeffrey

to otaku119,一定要用強型別物件接收查詢結果嗎?若查到的資料最後要轉 CSV 或 JSON,可以寫 cn.Query(...) 就好不用指定型別,當成 dynamic 或 DappeRow 型別處理。 "T1,T2,T3".Split(',').ToList().ForEach(tbl => DumpJson(tbl, cn.Query("SELECT * FROM " + tbl).ToList())); //註:因寫死 Table Name 無 SQL Injection 疑慮,可串接 SQL 指令 補充從 DapperRow 取得欄位名稱的方法:https://blog.darkthread.net/blog/enum-columns-from-dapper-result/

# by otaku119

感謝板主提供方向 我這邊的目的,是希望從firebird的DB,複製裡面的table到SQL server上 firebird的部份用dapper來讀取,所以參考板主指導改寫如下: string table = "WP0ACCH"; var sql = "SELECT * FROM " + table; IEnumerable<dynamic> tables = FBconn.Query(sql); tables.Take(1).ToList().ForEach(f => { Console.WriteLine("tablef=" + f.ACCH001 + ",2=" + f.ACCH002); }); 現在又產生一個新的問題,想請板主能指教 我EF已經有產生對應的DBset<WP0ACCH> 請問我要怎麼動態的去新增進去 讓我可以 using (wEntities w = new wEntities()) { results.ForEach(o => { w.WP0ACCH.Add(o); }); w.SaveChanges(); } 這裡面的WP0ACCH,能動態的變更?

# by Jeffrey

to otaku119, 你搬資料時其實用不到強型別的特性,不如就用 Hard-Coding 加 dynamic 簡單帶過,wEntities 寫個 public dynamic GetDbSet(string tblName) { switch (tblName) { case "WP0ACCH": return this.WP0ACCH; ... } } 然後 o => { w.GetDbSet(tblName).Add(o); } 不想寫死 switch case,可改成用 Reflection。

# by otaku119

板主你好: 感謝板主提點,讓我茅塞頓開 最快的解決辦法就是如同板主指導的,寫死switch private IEnumerable<dynamic> getFbDbResult(FbConnection fBconn, string tblName, WSTSTDEntities w) { string sql = "SELECT * FROM " + tblName; IEnumerable<dynamic> result = null; switch (tblName) { case "WP0ACCH": var query = fBconn.Query<WP0ACCH>(sql); var qq = (from a in query join b in w.WP0ACCH on new { aa=a.ACCH001.TrimEnd() , ab=a.ACCH002.TrimEnd() } equals new { aa=b.ACCH001.TrimEnd(), ab=b.ACCH002.TrimEnd() } into psb from b in psb.DefaultIfEmpty() select new { a,b }).ToList(); result = qq.Where(r=>r.b==null) break; default: result = null; break; } return result; } 關鍵在fBconn.Query<WP0ACCH>(sql); 如果讓DAPPER自己動態產生,回傳的是DAPPERROW類型 而這樣就無法直接複製到目的DB (當然可以用clone的方式,但是這樣會比用switch更複雜、更有問題) 另外資料的複製也要透過where的條件過濾,所以結論還是要在switch裡面寫死 謝謝板主的指導

# by 阿元

2022 年末才發現的我,會不會太晚 🤣

# by 菜B8

2023開始工作了才發現,感續黑大好文,2022年的資*會培訓班只教ADO.NET跟EF

# by 菜鳥A

請問DAPPER如何指定Schema Name?? 網上查的是直接在Model層定義 但需要動態指定Schema Name該怎麼做?

# by Jeffrey

to 菜鳥A,不太懂指定 Schema Name 的意思,能用具體例子說明?

# by 菜鳥A

string querystr = $"SELECT A.Col1, B.Col3, C.Col5, FROM TableA A JOIN TableB B ON A.Key = B.BaseId JOIN TableC C ON B.CarId = C.CarId WHERE C.STATUS = 1 AND A.Col3 = 0 AND B.Name=:Name "; var parameters = new DynamicParameters(); parameters.Add("@Name", Name); List<ModelA> MdlList = conn.Query<ModelA>(querystr, parameters).ToList(); 在這個Query字串中想指定Schema的Name, 怎麼寫比較好呢?? 除了將string改為以下加入Schema的Name以外, 有辦法拉到建立conn的那層指定嗎?? string querystr = $"SELECT A.Col1, B.Col3, C.Col5, FROM {Schema_Name}.TableA A JOIN {Schema_Name}.TableB B ON A.Key = B.BaseId JOIN {Schema_Name}.TableC C ON B.CarId = C.CarId WHERE C.STATUS = 1 AND A.Col3 = 0 AND B.Name=:Name ";

# by Jeffrey

to 菜鳥A,所以你的情境是有好幾個不同的 Schema,下面包含同樣規格的 Table? 目標資料庫是 SQL,同一個 Database 有多個 Schema?還是 Oracle 中的 Schema?

# by 菜鳥A

to Jeffrey 是多個Schema沒錯 同時也是Oracle 中的 Schema

# by Jeffrey

to 菜鳥A,用 `ALTER SESSION SET CURRENT_SCHEMA = <Schema_Name>;` 可以簡化嗎?

# by 菜鳥A

To Jeffrey 測試可以, 謝謝

Post a comment