野人獻曝,分享最近在寫的正式、測試台間的小規模資料搬移法。

情境是正式台跟測試台各有自己的資料庫,想將正式台某幾筆資料匯出,備份保存或是匯入測試台資料庫模擬測試;或是反過來,資料先在測試台輸入驗證無誤後要上線,希望將將輸入好的資料直接匯入正式台,省去在正式台重新登打的工夫。

這類情境用 Entity Framework 不難實現的,這篇介紹則介紹不用 EF 的做法。匯出匯入的前題是要有強型別的 Entity 型別,你可以手工宣告,也可借用 Visual Studio 強大的「貼上 JSON 做為類別」、「貼上 XML 做為類別」功能快速產生(參考:Visual Studio 的選擇性貼上,貼上Json作為類別 - 50懶 - 點部落)。

匯出時先用 .Query<EntityType>("SELECT * FROM Table") 產生 EntityType[],JSON 序列化後供使用者下載保存或匯入到另一套系統。實務上可透過壓縮提高傳輸與儲存效率(參考:程式範例:byte[] 不落地壓縮 ZIP 檔 - 黑暗執行緒)。

在匯入端則是先將 JSON 反序列化為 EntityType[],再來我選擇用 Reflection 自動產生 INSERT INTO 指令,再用 Dapper .Execute(InsertScript, EntityType[]) 一次塞入多筆資料,一行搞定。

程式範例如下:

       public class Player
        {
            public int SeqNo { get; set; }
            public string Id { get; set; }
            public string Name { get; set; }
            public DateTime RegDate { get; set; }
            public int Score { get; set; }
        }


        static void Main(string[] args)
        {
            string json = null;
            //模擬由伺服器A匯出Player[],再轉成JSON
            using (var cn = new SqlConnection(cs))
            {
                var data = cn.Query<Player>(
                    @"SELECT Id,Name,RegDate,Score FROM Players").ToArray();
                json = JsonConvert.SerializeObject(data);
            }
            //補充:實務應用時可使用ZIP壓縮技術
            //http://blog.darkthread.net/post-2018-08-14-zip-byte-array.aspx


            //模擬上傳JSON到伺服器B,還原後INSERT進資料庫
            var restored = JsonConvert.DeserializeObject<Player[]>(json);
            var insertScript = GenerateInserScript(restored[0], "Players", "SeqNo".Split(','));
            Console.WriteLine(insertScript);
            using (var cn = new SqlConnection(cs))
            {
                //傳入Insert Script跟物件陣列,完成資料匯入
                cn.Execute(insertScript, restored);
            }
        }


        /// <summary>
        /// 使用Reflection產生SQL INSERT腳本
        /// </summary>
        /// <param name="sample">樣本型別</param>
        /// <param name="tableName">資料表名稱</param>
        /// <param name="ignoredColNames">忽略欄位名稱</param>
        /// <returns></returns>
        static string GenerateInserScript(object sample, string tableName, 
            string[] ignoredColNames = null)
        {
            var sb = new StringBuilder();
            //此處假設tableName由開發人員決定,不開放使用者輸入,否則要防範SQL Injection
            sb.AppendLine("INSERT INTO " + tableName);

            string[] props = sample.GetType()
                .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Select(o => o.Name).ToArray();
            props = props.Where(o =>
                !o.StartsWith("_") && //排除_開頭及ignoredColNames列舉欄位名
                (ignoredColNames == null || !ignoredColNames.Contains(o)))
                .ToArray();
            sb.AppendLine($"({string.Join(", ", props)})");
            sb.AppendLine("VALUES");
            sb.AppendLine($"({string.Join(", ", props.Select(o => "@" + o).ToArray())});");
            return sb.ToString();
        }

透過 Reflection 產生的 INSERT 語法如下,欄位名稱前面加上@就變成變數,剛好讓 Dapper 依欄位名稱自動對映,比想像中簡單很多吧! 這裡只設計可排除特定欄位(例如:自動跳號欄位)的設計,實務若有需要,可加入客製化邏輯。

以上是小量資料匯出匯入功能的簡單示範,我們下次見。

Sample code of how to use Dapper and JSON to move data between databases quickly and easily.


Comments

# by 北極大西瓜

推推 簡單 方便 輕量 這樣超好用的!!!

Post a comment