C# 小技巧：讓複雜型別屬性與資料庫欄位無縫接軌

今天來介紹一則私房小技巧，能讓 C# 的複雜型別屬性與資料庫欄位無縫接軌，抱著「花盆種出葱請朋友來家裡吃飯品嚐」的心情分享給大家。

舉個實例說明應用情境。假設我有個 C# 資料型別長這樣，其中包含一些非基礎型別(Primitive Type)屬性，像是自訂列舉、int[] 及自訂物件陣列(Medal[])：

    public class Runner
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public Levels Level { get; set; }
        public int[] Topics { get; set; }
        public Medal[] Medals { get; set; }
    }

    public enum Levels
    {
        VIP, Regular, Trial
    }

    public class Medal
    {
        public string Name { get; set; }
        public DateTime Date { get; set; }
    }

我想將用這個型別產生的物件(如以下範例)寫入 Runner 資料表存成一筆資料(為求單純，Topics 與 Medals 就不另拆資料表了)，大家會怎麼會設計？

var p = new Runner()
{
    Id = "A01",
    Name = "Jeffrey",
    Level = Levels.VIP,
    Topics = new int[] { 1, 3, 5, 7 },
    Medals = new Medal[]
    {
        new Medal() { Name="42K", Date=new DateTime(2012, 2, 19) },
        new Medal() { Name="SUB4", Date=new DateTime(2017, 2, 12) }
    }
};

依據直覺，資料表會差不多像這樣：

CREATE TABLE [dbo].Runner
(
	[Id] VARCHAR(8) NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(32) NOT NULL, 
    [Level] CHAR NOT NULL, -- V=VIP, R=Regular, T=Trial
    [Topics] VARCHAR(128) NOT NULL, 
    [Medals] VARCHAR(1024) NOT NULL
)

string、int、DateTime 等基礎型別可以直接對映成 NVARCHAR、INTEGER、DATE... 等資料庫型別，但遇到較複雜的型別便得自己想辦法轉換。 就本案例，Level 用單一字元 V/R/T 代表 VIP/Regular/Trial 等列舉值，Topics 用逗號分隔數字字串表示整數陣列，Medals 為求省事我直接用 JSON 轉換。

依此設計，在寫入資料及讀取資料時，便需要寫一段轉換邏輯將特定型別屬性轉成字串以寫入 VARCHAR，讀取則反過來要將字串轉回列舉、int[] 及 Medal[] 型別。 程式差不多會像這樣：(使用 Dapper 搭配 SQL LocalDB 示範)

class Program
{
    static void Main(string[] args)
    {
        var p = new Runner()
        {
            Id = "A01",
            Name = "Jeffrey",
            Level = Levels.VIP,
            Topics = new int[] { 1, 3, 5, 7 },
            Medals = new Medal[]
            {
                new Medal() { Name="42K", Date=new DateTime(2012, 2, 19) },
                new Medal() { Name="SUB4", Date=new DateTime(2017, 2, 12) }
            }
        };
        TestWrite(p);
        var q = TestRead(p.Id);
        Console.WriteLine("AreEqual Check=" +
            (JsonConvert.SerializeObject(p) == JsonConvert.SerializeObject(q)));
        Console.Read();
    }
    static string cs =
        "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDBFilename=X:\\LocalDB\\RunnerDB.mdf";
    static void TestWrite(Runner r)
    {
        using (var cn = new SqlConnection(cs))
        {
            cn.Execute("DELETE FROM Runner WHERE Id = @id", new { id = r.Id });
            cn.Execute(
@"INSERT INTO Runner (Id,Name,Level,Topics,Medals)
VALUES (@id,@name,@level,@topics,@medals)", new
{
id = r.Id,
name = r.Name,
level = r.Level.ToString().Substring(0, 1),
topics = string.Join(",", r.Topics.ToArray()),
medals = JsonConvert.SerializeObject(r.Medals)
});
        }
    }
    static Runner TestRead(string id)
    {
        using (var cn = new SqlConnection(cs))
        {
            var d =
                cn.Query("SELECT * FROM Runner WHERE Id=@id", new { id })
                .Single(); //忽略查無資料的狀況
            return new Runner()
            {
                Id = d.Id,
                Name = d.Name,
                Level =
                    (Levels)
                    Enum.Parse(typeof(Levels),
                    Enum.GetNames(typeof(Levels))
                    .First(o => o.StartsWith(d.Level))),
                Topics = ((string)d.Topics).Split(',').Select(o => int.Parse(o)).ToArray(),
                Medals = JsonConvert.DeserializeObject<Medal[]>(d.Medals)
            };
        }
    }
}

實測將物件寫入後再讀取，比對前後一致，而資料在資料表中會像這樣：

寫入讀取時要外加一坨轉換邏輯感覺有點 Low，如果能把轉換邏輯收進 Runner 物件，在寫入及讀取資料時自動執行，程式才會優雅，所以我們來改造一下。

自動轉換的核心原理是另外新增實際寫入資料庫的對映屬性，以 Medal[] Medals 為例，我選擇新增一個 string MedalsJson 用來儲存 Medals 序列化後的 JSON 字串。而 Medals 屬性 get 改成動態由 MedalsJson 解析，set 時則將 Medal[] 轉成 JSON 存入 MedalJson。同理，Level、Topics 也如泡製。 我習慣一併調整資料表欄位名稱為 LevelFlag、TopicsStr、MedalJson，對映這些新增的儲存屬性，力求命名清晰不易混淆，所以資料表欄位名稱將調整如下：

CREATE TABLE [dbo].Runner
(
	[Id] VARCHAR(8) NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(32) NOT NULL, 
    [LevelFlag] CHAR NOT NULL, -- V=VIP, R=Regular, T=Trial
    [TopicsStr] VARCHAR(128) NOT NULL, 
    [MedalsJson] VARCHAR(1024) NOT NULL
)

依據前述構想，將 Runner 型別改寫如下：

    public class Runner
    {
        [Key]
        public string Id { get; set; }
        public string Name { get; set; }

        [JsonConverter(typeof(StringEnumConverter))]
        [NotMapped]
        public Levels Level {
            get
            {
                return (Levels)Enum.Parse(typeof(Levels),
                            Enum.GetNames(typeof(Levels))
                            .First(o => o.StartsWith(LevelFlag)));
            }
            set
            {
                LevelFlag = value.ToString().Substring(0, 1);
            }
        }
        [JsonIgnore]
        public string LevelFlag { get; set; } = "T";

        [NotMapped]
        public int[] Topics {
            get {
                if (string.IsNullOrEmpty(TopicsStr))
                    return new int[] { };
                return TopicsStr.Split(',').Select(o => int.Parse(o)).ToArray();
            }
            set {
                if (value == null || !value.Any())
                    TopicsStr = null;
                else 
                    TopicsStr = string.Join(",", value);
            }
        }

        [JsonIgnore]
        public string TopicsStr { get; set; }

        private Medal[] _medals = null;
        [NotMapped]
        public Medal[] Medals {
            get
            {
                if (string.IsNullOrEmpty(MedalsJson)) return null;
                if (_medals == null)
                {
                    _medals = JsonConvert.DeserializeObject<Medal[]>(MedalsJson); 
                }
                return _medals;
            }
            set
            {
                MedalsJson = JsonConvert.SerializeObject(value);
                _medals = value;
            }
        }
        [JsonIgnore]
        public string MedalsJson { get; set; }
        
    }

註1：在 LevelFlag、TopicsStr、MedalsJson 放上 [JsonIgnored] 的理由是 JSON 化前端可直接使用 Level、Topics、Medals 等屬性， 沒必要再由 LevleFlag、TopicsStr、MedalsJson 轉換，故不需要傳遞。延伸閱讀：Json.NET技巧兩則: 忽略屬性及列舉轉字串
註2: Level、Topics、Medals 放上 [NotMapped] (NotMappedAttribute) 是預留 Entity Framework 或其他自動機制識別之用，映對資料庫欄位時忽略這些屬性。

[2019-07-22 補充] 忘了說，如果你的轉換只需搭配 Dapper 使用，有個很厲害的武器 - Dapper TypeHandler，非常省事方便，屬性與資料庫欄位名稱還能維持一致。 (欄位與屬性型別不同但名稱相同這點倒是有好有壞，有時可能造成混淆) 但我後來多選擇在資料物件內自己處理轉換，理由是有些專案不一定是用 Dapper，會走 EF 或土砲 ORM 系統，此時可設計成 Entity 型別只包含要寫入資料庫欄位的屬性，利用 partial class 技巧 動態轉換出 int[]、Enum、自訂物件陣列等實際要應用型別的屬性，一樣可以順暢運作。(感謝軟體主廚提醒)

一旦資料型別具備自我轉換能力，INSERT、SELECT 執行程序直接使用原始物件即可，不需加工轉換，程式碼當場簡潔很多：

    static void TestWrite(Runner r)
    {
        using (var cn = new SqlConnection(cs))
        {
            cn.Execute("DELETE FROM Runner WHERE Id = @id", new { id = r.Id });
            cn.Execute(
@"INSERT INTO Runner (Id,Name,LevelFlag,TopicsStr,MedalsJson)
VALUES (@Id,@Name,@LevelFlag,@TopicsStr,@MedalsJson)", r);
        }
    }
    static Runner TestRead(string id)
    {
        using (var cn = new SqlConnection(cs))
        {
            return cn.Query<Runner>(
                "SELECT * FROM Runner WHERE Id=@id", new { id })
                .Single(); //忽略查無資料的狀況
        }
    }

【同場加映】

若嫌要自己產生 INSERT、SELECT 語法麻煩，我們還可寫個共用函式靠 System.Reflection 自動產生 INSERT 與 SELECT 的，這個前幾天才示範過。

        //...省略...
        Insert<Runner>(p);
        var q = Read<Runner>(p.Id);
        Console.WriteLine("AreEqual Check=" +
            (JsonConvert.SerializeObject(p) == JsonConvert.SerializeObject(q)));
        Console.Read();
    }

    static void Insert<T>(T data)
    {
        var tableName = typeof(T).Name;

        var propNames = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance)
            .Where(o => o.GetCustomAttribute(typeof(NotMappedAttribute)) == null) //略過標註[NotMapped]的屬性           
            .Select(o => o.Name).ToArray();
        string insertSql =
$@"INSERT INTO {tableName} ({string.Join(",", propNames)}) 
VALUES ({string.Join(",", propNames.Select(o => $"@{o}").ToArray())})";

        using (var cn = new SqlConnection(cs))
        {
            //方便重複測試，先刪光資料
            cn.Execute("DELETE FROM Runner");
            cn.Execute(insertSql, data);
        }
    }

    static T Read<T>(object pkValue)
    {
        using (var cn = new SqlConnection(cs))
        {
            var tableName = typeof(T).Name;
            //前題：資料物件有單一欄位Primary Key，並標註[Key] Attribute
            var pkProp =
                typeof(T).GetProperties().SingleOrDefault(o =>
                o.GetCustomAttribute(typeof(KeyAttribute)) != null);
            return cn.Query<T>($"SELECT * FROM {tableName} WHERE {pkProp.Name} = @id",
                new { id = pkValue }).Single();
        }
    }

以上就是我讓資料物件無縫穿梭於 C# 與資料庫間的小技巧，提供大家參考。

Tips of how to use C# property feature to save object to DB data table and restore thme from DB smoothly.