Dapper - 使用 LINQPad 快速產生相對映 SQL Command 查詢結果的類別 | mrkt的程式學習筆記 - 點部落

2016-06-15

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

6765
0
Dapper
2016-06-10

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

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

新與舊

很多開發者從一開始接觸程式開發就已經是從物件導向與 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 都是跨資料表的方式去執行，所以要建立對映的類別時，各個屬性的名稱、型別也必須要一致，這就是~~最靠北~~最讓人頭痛的時候，你要是因此畏懼而選擇不建立對映類別的話，就只能走回頭路，但如果硬著頭皮以手動的方式去建立對映類別的話，我想將這些類別給建立完的時候也已經是要交付專案的死線日期了。

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

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

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

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

這時候，你要怎麼抉擇呢？

工程師的美德

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

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

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

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

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

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

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

不過文章裡所提供的程式有些不完整，無法產生完整的類別，所以要另外使用在 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」

使用的方式如下：

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

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

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

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

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

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

執行結果

這邊的操作示範雖然是只有針對一個 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();
    }
}

使用方式與執行結果

我知道網路上有很多工具或是套件是可以幫我們快速地將 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 程式開發時所犯的錯誤就是這樣，直到後來有人帶著我逐步去修正這些的觀念與開發方式，雖然現在離熟悉物件導向的程式開發還有一大段距離，但至少不會再發生那樣的觀念偏差。

當你看到這一篇以及前幾篇所介紹的 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

（圖片來源：Generating .NET POCO Classes for SQL Query Results -- Visual Studio Magazine）