2015年10月26日 星期一

Dapper - 使用 LINQPad 快速產生相對映 SQL Command 查詢結果的類別

前面幾篇文章都是一直在介紹 Dapper 的相關功能,不過都是屬於基本的應用,Dapper 也是有很多的進階使用方式,不過這邊就不繼續介紹而是讓各位去發掘與找出適合自己專案的做法,這一篇文章基本上跟 Dapper 沒有直接關係,因為產出對映查詢結果類別的這一個功能並非使用 Dapper,因為 Dapper 只是一個優化 ADO.NET 操作 T-SQL 以及對映類別的一個 Utilities,本身並沒有提供什麼神奇的功能,但是很多人從傳統 ADO.NET 操作並且使用 DataSet DataTable 弱型別的方式改為使用 Dapper 以及強型別的時候,普遍都會遇到一個最大的困難…… 「建立類別」。

這一篇文章的內容算是拾人牙慧,只是跟大家說別人有提供了方法可以讓我們能夠快速地從 SQL Command 去建立相對映的類別,雖然不是我寫的方法,但還是希望大家可以及早地從弱型別的地獄中快點逃離出來(雖然這麼說是有貶抑弱型別的意味,但看了這麼多年以及很多人所開發的專案,發現到很多問題都是從濫用 DataSet DataTable 等弱型別開始)。

 


新與舊

很多開發者從一開始接觸程式開發就已經是從物件導向與 ORM 著手,所以都會對於為何有很多公司、企業會有這麼多的 SQL Command 與弱型別操作而感到有些困惑,甚至於會覺得這些公司企業裡有存在這種的程式開發應用而認為是一種資訊落後或是不進步。

我這邊要強調的是,並不是說公司裡仍然使用 SQL Command 或弱型別應用就表示落後或是不進步,這樣的觀感是相當不正確的,各位要知道很多大型的公司企業都已經存在了一段時間,而且很早就已經進行了系統資訊化的開發,所以內部系統就會夾雜不同時期的開發專案,而且還有為數不少的系統是一直持續地運作,只要沒有什麼太大的問題是不會去重新開發的,然後構築這些系統的基礎都是很多前輩辛辛苦苦所建立的程式,這些基礎有很多都已經存活了相當久,既使程式語言的版本已經更迭了很多次,但這些程式仍然持續屹立不搖,也許很多意氣風發的年輕人會覺得這些老程式為何不汰換呢?不汰換的原因有很多,不過最重要的一點就是「穩定」,而且這些有歷史的程式也是很多老系統持續運作的根基,既然穩定又沒有出毛病的話,就沒有去汰換或更換的必要。

不過上述的情況是在既有系統的維護與持續運作的條件之上,但今天如果是要開發一個新系統,還是一樣要用這些有歷史的程式或是元件嗎?其實要看專案的應用,如果還是必須要跟老舊系統有關聯的話,就必須有一定程度的使用,這是無法避免的,但如果今天所要開發的是一個全新的系統,那就真的不要一直固守既有的方式,而是去找一個穩定且更有效率的方法來開發。

 

開發上所遇到的狀況

這一年來我所開發的專案剛好是在既有系統與新架構中間的一個應用,原有的系統是完全使用 ADO.NET 與一堆 DataSet 和 DataTable 的程式操作所建立的,所以 SQL Command 與弱型別的應用是無所不在,但是我所開發的是 ASP.NET Web API 專案,會強調使用強型別,所以我在一剛開始的開發就常常陷入很多困境與矛盾,因為要基於既有系統的內容去做開發,那就要遵循公司既有的底層元件方式去寫程式,但很多時候碰到的既有實作卻是相當挑戰我一直以來的觀念,而且更讓我以及協同開發專案的另外一組團隊所介意的就是效能問題。

因為我必須要使用原本底層元件去對資料庫作存取,以取得資料來說,不論是一筆資料或是一堆資料都是要面對 DataSet 或 DataTable,拿到這些弱型別資料後又必須要去對映到強型別的物件,一開始我還真的是一個個欄位去手動對映到物件的屬性,在做了兩個類別,而且這兩個類別的屬性也不過就一二十個,我就用反射的方式去處理資料對映的這一段處理,因為我看到有很多 SQL Command 查詢結果的欄位都上百個(最多有看到用到好幾個 Table 的查詢結果有將近 500 個欄位),我真的不想在這些事情上面去多浪費一分鐘或一秒鐘,所以一開始用反射的方式去處理,但是執行的 Performance 很差,差到我自己都覺得很丟臉,怎麼會寫出那樣的程式,於是嘗試了很多的方式,最後在重新檢視既有系統的所有程式與資料存取處理之後,我就決定改用 Dapper 來取代既有系統的資料處理方式。

雖然解決了資料對映以及執行效能上的問題,但是隨之而來的問題是,有很多 SQL Command 的查詢結果是一大堆的欄位,要建立二三十個屬性的類別是司空見慣的事情,但總是會碰到那種上百個或是數百個的欄位的時候,遇到這種也必須要跟著去建立對映的類別,而且很多 SQL Command 都是跨資料表的方式去執行,所以要建立對映的類別時,各個屬性的名稱、型別也必須要一致,這就是最靠北最讓人頭痛的時候,你要是因此畏懼而選擇不建立對映類別的話,就只能走回頭路,但如果硬著頭皮以手動的方式去建立對映類別的話,我想將這些類別給建立完的時候也已經是要交付專案的死線日期了。

 

image

建立一段 SQL Command,簡單!
用 Dapper 去執行這一段 SQL Command,簡單!
建立 Dapper 執行 SQL Command 後要將對映轉換資料的類別?
如果從資料庫取出的資料欄位少的話… 簡單!

 

image

如果從資料庫取出的資料欄位很靠北多的話… 想到要建立類別就頭痛!

一段 SQL Command 就要建立一個類別 …
一堆 SQL Command 就要建立一堆類別 …

如果這一堆的 SQL Command 有很多很多的欄位,就表示要在新建立的類別裡慢慢刻那一堆的屬性(我還沒說連資料型別也必須對映),想到這邊…… 還是乖乖回去用 DataSet, DataTable 嗎?

這時候,你要怎麼抉擇呢?

 

工程師的美德

這邊用了 Demo Fan 在上課時常常用的哏,工程師要具備的美德就是要「懶」~~

image

懶,不是遇到麻煩就退縮
懶,不是遇到問題就保守
懶,不是永遠故步自封
懶,是要找出一個更好、更快、更簡單的處理做法
懶,找出一個聰明做事的方法,取代繁瑣無趣的操作

 

一旦遇到問題只會在那邊悶著頭埋頭苦幹,用拼死拼活的方式去用一堆的時間和新鮮的肝去用手工的方式去把這些對映的類別給刻出來,這只能說你很有毅力,但是…然後呢?所以要善用工具與找尋更好的方式來解決我們的問題,而不是在那邊做得要死要活之後還被人嫌。

這邊會用到的工具是「LINQPad」,這部落格裡已經寫過很多篇文章了,所以就不用再一次地介紹,如果你只以為 LINQPad 只是用來讓你練習 LINQ 語法操作的話,這麼想的人就大錯特錯啦,以 LINQPad 5 來說,已經可以是一個小型的 .NET 程式編譯器,詳細介紹就請看之前的文章「LINQPad 5 Beta 新增功能」。

image

P.S.
本篇文章所介紹的方法在免費或付費的各版本 LINQPad 都可以使用。

 

接著要用的方式是由「Necroskillz」在他的部落格裡於 2012-10-09 所發佈的文章「Generate C# POCOs from SQL statement in LINQPad」提供的。
(我在文章裡用了誰的文章裡的做法或是參考了什麼樣的內容,我都一概會如實地交代清楚作者與來源,絕對不會只有「連結」或「這裡」或是其他意味不明的文字含混帶過,這是一種尊重)

image

在「Generate C# POCOs from SQL statement in LINQPad」這篇文章裡就提供了一個 LINQPadExtensions 類別,

image

不過文章裡所提供的程式有些不完整,無法產生完整的類別,所以要另外使用在 GitHub Gist 上面由 jbouy 所提供的程式「Generate C# POCOs from SQL statement in LINQPadFrom http://www.necronet.org/archive/2012/10/09/generate-c-pocos-from-sql-statement-in-linqpad.aspx

image

 

使用的方式如下:

先把程式碼給複製起來,然後開啟 LINQPad,然後將 Language 的選項調整為使用「C# Program」

image

然後將剛才所複製的程式碼給貼到 LINQPad 裡

image

這邊的範例程式是用 SQL Server Express (MS SQL Server 也一樣可以使用),資料庫是用 AdventureWorks,這邊要把 Product 裡面的 Products 這個 Table 的查詢結果去產出對映的類別。

image

使用作者所提供的方法,替換成我們的 SQL Command,

image

按下執行後就可以看到產出的相對映類別(不過類別名稱並沒有對應 Table 名稱)

image

 

將產生的對映類別複製起來,然後在 LINQPad 裡新開一個頁籤,馬上使用 Dapper 來做驗證(LINQPad Premium 版本是可以跟 Visual Studio 一樣透過 Nuget 安裝套件喔)

image

執行結果

image

這邊的操作示範雖然是只有針對一個 Table 的 SQL Command 查詢結果去產出對映的類別,但如果 SQL Command 是有跨多個 Table 或者是跨資料庫的操作,同樣也可以產生類別。

這邊我也提供了我的修改版本給大家,這一個版本是可以讓我們自己輸入要產生類別的名稱,

public static class LINQPadExtensions
{
    private static readonly Dictionary<Type, string> TypeAliases = new Dictionary<Type, string> {
        { typeof(int), "int" },
        { typeof(short), "short" },
        { typeof(byte), "byte" },
        { typeof(byte[]), "byte[]" },
        { typeof(long), "long" },
        { typeof(double), "double" },
        { typeof(decimal), "decimal" },
        { typeof(float), "float" },
        { typeof(bool), "bool" },
        { typeof(string), "string" }
    };
     
    private static readonly HashSet<Type> NullableTypes = new HashSet<Type> {
        typeof(int),
        typeof(short),
        typeof(long),
        typeof(double),
        typeof(decimal),
        typeof(float),
        typeof(bool),
        typeof(DateTime)
    };
 
    public static string DumpClass(this IDbConnection connection, string sql, string className = "Info")
    {
        if(connection.State != ConnectionState.Open)
        {
   connection.Open();
  }
             
        var cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        var reader = cmd.ExecuteReader();
                         
        var builder = new StringBuilder();
        do
        {
            if(reader.FieldCount <= 1) continue;
         
            builder.AppendFormat("public class {0}{1}", className, Environment.NewLine);
            builder.AppendLine("{");
            var schema = reader.GetSchemaTable();
                         
            foreach (DataRow row in schema.Rows)
            {
                var type = (Type)row["DataType"];
                var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
                var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
                var collumnName = (string)row["ColumnName"];
                 
                builder.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));
    builder.AppendLine();
            }
             
            builder.AppendLine("}");
            builder.AppendLine();            
        } while(reader.NextResult());
         
        return builder.ToString();
    }
}

使用方式與執行結果

image

 

我知道網路上有很多工具或是套件是可以幫我們快速地將 Database 的 Tables 給轉換一份對映類別出來,但是這僅止於單一 Table 的查詢應用,對於那些跨資料庫、跨資料表以及充滿各種花式應用的 SQL Command 就只能說是無用武之地,並不是每個人、每個專案、每個公司的開發都是乖乖地用物件導向開發呀。

 

最後來個總結…

 

使用 Dapper

依然可以保持使用 T-SQL 操作資料的做法(彈性)
簡化資料對映轉換的程序(簡單)
配合 LINQPad 的操作,可快速建立對映的類別(快速)

 

不過你如果看到這裡就躍躍欲試地想把你手中的專案都改成這樣的方式開發時,請務必停看聽以及多想兩分鐘,因為事情並不是這麼地簡單。

 

強型別與弱型別

很多公司企業既有資料庫裡的資料表在一開始建立的時候大多不是對映程式的物件去做建立,就算是有也會在每一次的需求變更之後而有了不一樣的面貌,好多個專案去使用相同資料表的情況也是常有的是情,如果對於資料庫的控管不是嚴格、嚴謹,那麼 A 專案想要在 Table 上面去加欄位或是改型別、B 專案要去調整欄位長度、C 專案要去增加關連,想這些情況也是常常發生在我們的周遭,到最後我們所看到的資料庫與資料表都會是怪獸等級,例如一個欄位會因為不同時期而有不同定義與資料格式,欄位名稱跟欄位所存的資料在意義上是完全相反,本來應該是 int 的型別卻是用 nvarchar,日期時間從 date, smalldatetime, datetime 到其他日期時間的型別都用上(例如明明都是 CreateTime,但是每個資料表裡的這個欄位所使用的型別都可以不一致),諸如此類的問題都是會讓系統開發與日後的維護帶很大的困擾。

現在有很多公司企業的專案也逐漸採用 ASP.NET MVC / Web API 來開發,在資料存取的部分就會遇到很多問題,有很多公司的開發人員是直接使用既有且未整理過的資料表拿來作為 Model 的使用與建立,在開發時就會遇到各式各樣的問題,例如資料表根本不是依據物件導向去做建立,所以建立出來的 Model 就會有很多意味不明的屬性名稱與型別,以致於在開發的時候明明用的是 ORM ( Entity Framework ),但心中還是依然在想著怎麼用 LINQ 去兜出以前操作 T-SQL 可以取得資料的 Expression,然後因為寫不出來或是用了很奇怪的方式去達成目的,但最後的執行 Performance 奇差無比而去質疑並否定 ORM。

物件導向的開發並不是你在系統裡建立一堆物件,然後在程式裡用物件傳來傳去就可以叫做物件導向的程式開發,我在一開始接觸 .NET 程式開發時所犯的錯誤就是這樣,直到後來有人帶著我逐步去修正這些的觀念與開發方式,雖然現在離熟悉物件導向的程式開發還有一大段距離,但至少不會再發生那樣的觀念偏差。

image

當你看到這一篇以及前幾篇所介紹的 Dapper,然後很想把你正在開發的專案也改用這樣的方式來開發,這時候會困擾你的將會是強型別與弱型別的適應,因為過去使用弱型別的開發,所面對的是 DataSet DataTable,所以很多開發習慣都是繞著這些弱型別去打轉,但如果今天是改用強型別的話,就是要去調整原本所熟悉的開發方式,甚至於很多原本程式的寫法都必須徹頭徹尾的改變。

先歸根究底的去思考你所面對的資料來源與開發資源,是否能夠支撐你去做這樣的改變,如果有很多的包袱是無法馬上讓你改變或是需要花很多成本去改變的話,那麼我會建議你先不要硬幹下去,請先把要改變的前置作業給完成,完成之後再去做改變,否則你將會發現你忙了一場後換來的只是一場空或是失敗的經驗,然後最後的最後還是要改回使用以前的老方法。

所以有很多時候是環境的根本問題去限制你的改變,人、政策、主管、包袱等等林林種種的問題,這些都是限制作這些改變的種種障礙,所以不是不能去改變,而是你要考慮清楚,這些改變所引發的影響與效應是不是你能夠應用與解決。

 


這一篇使用 LINQPad 去產生 SQL Command 相對映的類別,這個方法只能產生 C# 的類別,如果你所使用的開發語言是 VB.NET,就必須自己去做轉換,例如你可以將 C# 類別複製起來然後拿到 C# Convert to VB.NET 的工具去做轉換,又或者是自己去改寫產生類別的程式,要不然你也可以參考使用以下連結裡所提供的小工具,

Generating .NET POCO Classes for SQL Query Results -- Visual Studio Magazine 
by Joe Kunk

image
(圖片來源:Generating .NET POCO Classes for SQL Query Results -- Visual Studio Magazine)

 

參考連結

Generate C# POCOs from SQL statement in LINQPad | NecroNET.org

GitHub Gist - Generate C# POCOs from SQL statement in LINQPadFrom http://www.necronet.org/archive/2012/10/09/generate-c-pocos-from-sql-statement-in-linqpad.aspx

Generating .NET POCO Classes for SQL Query Results -- Visual Studio Magazine

 

Dapper 系列文章

另一種資料存取對映處理方式的選擇 - Dapper

Dapper 練習題 - 新增多筆或大量資料

Dapper 練習題 - 每個查詢的結果都要定義並對映一個類別嗎?(使用 dynamic)

ASP.NET MVC 的 Model 使用 Dapper

 

以上

16 則留言:

  1. 大推啊...
    現在公司裡面都是使用 MVC + Dapper
    看到這篇文章實在是太有感了...(泣...
    不過我們的 SqlCommand 都會用 SP 來包

    回覆刪除
    回覆
    1. 目前是自己建,因為我還沒有遇到很多欄位的情況 XDDD

      刪除
  2. 讓我想到給外包寫的案子
    用webform的開發方式寫MVC架構...OTZ

    回覆刪除
  3. 你好,
    我依照你的範例在自己的linqpad做一次
    http://lh3.googleusercontent.com/-PdKJaDztvOI/VizrGR_rXEI/AAAAAAAAeqw/1kgI86MUaD8/s1600-h/image%25255B74%25255D.png

    執行時在connection.Query的地方出現錯誤
    'System.Data.Common.DbConnection' 不包含 'Query' 的定義,也找不到擴充方法 'Query' 來接受類型 'System.Data.Common.DbConnection' 的第一個引數 (您是否遺漏 using 指示詞或組件參考?)
    且我的Product 不會變成青綠色
    請問你知道是為什麼嗎?
    thx

    回覆刪除
    回覆
    1. 另外我都有在文末貼上系列文章的連結,所以請你務必按照系列文章的順序看下來。

      你的 LINQPad 有加入 Dapper 的組件參考嗎?按下 F4 就可以加入組件參考,
      如果你是購買 Premium 版,則可以透過 Nuget 安裝 Dapper.

      我沒有看到你的狀況與使用情境,所以我無法隔空想像你的問題,所以建議將你的 LINQPad 操作情境給擷取圖片下來,
      然後請使用左邊的「詢問與建議」功能,將你的狀況與及擷取的圖片給我,我就可以更加瞭解你的問題所在而提供你建議。

      刪除
    2. 作者已經移除這則留言。

      刪除
    3. 我只有做到Add To Query
      並沒有做到Add namespaces 選擇 Dapper

      因為在你這篇文章
      http://kevintsengtw.blogspot.tw/2015/09/dapper.html
      也沒有提到要再Add namespaces -Dapper
      加入namespaces 後問題已經解決

      但我在以下這行程式碼的Product 類別名稱並不會變成青綠色,
      不知你是否有再做另外的設定
      var result=connection.Query(sqlcommand);

      刪除
    4. 「接著改用 Dapper 來處理相同的資料內容以及資料對映,首先在 LINQPad 透過 Nuget 加入 Dapper...」這句話就是啦
      如果沒有加入 Dapper 參考,LINQPad 也會在執行前執行時顯示訊息
      另外你還是沒讓我看到畫面,我無法你是用哪一種 LINQPad 的模式去執行
      你確定你所執行的 LINQPad 畫面裡除了程式與使用的資料庫不同以外,還有什麼是相同的嗎?
      我已經請你將你所操作的 LINQPad 畫面擷圖給我看,以排除問題,
      否則我無法依據你模擬兩可又非常不清楚的描述來想像你的問題。

      請你先瞭解 LINQPad 的基本操作與環境認識,再來如果還是一樣無法解決也不提供擷圖的話,
      那麼這一篇以及這一系列的內容就注定跟你無緣了

      刪除
  4. 作者已經移除這則留言。

    回覆刪除
  5. 作者已經移除這則留言。

    回覆刪除
  6. 感謝分享!!超級實用!!

    回覆刪除
  7. 謝謝 Kevin 的分享, 這真的是非常實用的技巧及工具. ^^

    補充一下, 在產生 C# Class 的部份, 它還可以一次取多個 Result Set 回來, 以產生多個 class, 可以像以下這麼寫, 這樣就可以產生 2 個 Class, 只是名稱都是叫作 Customer, 要再改回來. 當然, 也是可以再改一下擴充方法的程式段, 讓名稱參數的型別為 List 或 Array, 這樣就可以作到多個類別有不同名稱了.

    this.Connection.DumpClass("select * from Customers; select * from Orders;", "Customer").Dump();

    回覆刪除
    回覆
    1. 其實將事情單純化會更好,過多的變化只會徒增困擾

      刪除
  8. XD 還好有再看到此篇才知道LINQPad還可以這樣用! 不然現在正改用Dapper, 但建Model class還是用EF幫我建的....

    回覆刪除

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。

最近的留言