浮生一夢

目前在公司所屬的專案團隊，採用 DataContext(以下簡稱 DC) 連結資料庫進行操作，其中 ExecuteQuery & ExecuteCommand 是最頻繁使用的方法(其實大概也就只用這兩個啦)，此二法與 SqlCommand(以下簡稱 CMD) 類別的 ExecuteReader & ExecuteNonQuery 用途類似，都是用來進行「查詢」和「新增刪除修改」等資料庫操作的。而 DC 除了方法名稱看起來較白話以外，個人覺得比起 CMD 較顯著的優勢還有二點：第一，就是不用再自己寫落落長的那一段資料庫連線架構，例如 Open() & Close() 和 SqlConnection 物件的設定或是 Adapter 什麼的，相信有用過 ADO.NET 進行資料存取的人應該能感受這一段的惱人吧；此外，不同於 CMD 大都要使用 DataTable 來承接資料庫撈回的資料，DC 的查詢功能可以很簡便地把撈出的資料直接灌到事先設計好的 Model 裡，透過 ToList() 方法便能得到以該 Model 實例為元素的 List，資料操作或解析起來都非常方便，程式碼可讀性也高。

而基於避免 SQL Injection 攻擊，以串字串的方式把前端帶回的使用者輸入直接組進 SQL 語法應該已是開發人員的基本禁忌。在使用 CMD 時往往會透過 SqlCommand.Parameters.Add 之類的方法來把某些參數安全地填入 SQL 語句；在使用 DC 的 ExecuteQuery 與 ExecuteCommand 方法時，一般則是先透過 StringBuilder 準備含有「格式化字串參數空位(即：{0}, {1}… 這類參數對照)」的 SQL 語句，再將呼叫方法時第二個以後的參數依序代入空位，以達動態生成 SQL 的目的，用文字描述有點彆扭，看一下下面的範例程式碼大概就能馬上理解了。應該不少人很快就發現，後者其實很類似我們平常使用 String.Format() 做字串格式化的邏輯，透過 DC 則實現了字串格式化 + 檢查參數以防範 SQL Injection 的功能，相較 CMD 說真的較省力且直覺一些。

StringBuilder sql = new StringBuilder();
sql.Append("SELECT FirstName, LastName FROM Employees WHERE Country = {0} and City = {1} ");
var result = dc.ExecuteQuery<EmployeeModel>(sql.ToString(), "USA", "Seattle");

儘管 DC 是如此好用，但卻偏偏有個硬傷，也就是這篇文章誕生的原因─它沒辦法在格式化字串的參數當中直接代入 C# 的 Null 型態值。某些情境下，在 INSERT / UPDATE 時就是希望能將資料表中的 Nullable 欄位「確實填入 Null」而不是空白或是一些意義類似 Null 的值(例：0 / -1 / ’null’ 字串/ 1900-01-01 之類的)，我就是要 Null，其它什麼都不要！這種時候應該怎麼辦？使用 CMD 塞參數時，透過以 DBNull.Value 作為參數就能達成此需求，但在 DC 除了使用 Null 作為參數會出錯(錯誤訊息：「A query parameter cannot be of type 'System.Object' (查詢參數不能屬於型別 'System.Object' )」)，同時 DBNull.Value 也是完全不管用。

為了解決這個問題，先前同事 Chris 有想出一個不錯的方法，當時我們面對的是要寫入一個可為 Null 的 DateTime 型別欄位值，當使用者在未填寫前端的對應欄位時進行存檔，INSERT 就要將該欄位塞入 Null。上一段所敘述的報錯問題經追蹤後發現，要是在 SQL 的 StringBuilder 中直接寫死 ‘Null’ 字串，運作起來是完全 OK 的，這代表此錯誤並非在資料庫層級發生，而是 DC 本身一吃到 Null 參數就吐。基於此，Chris 想到的解決方法是直接在 SQL 語句對應的欄位值處使用 TRY_PARSE(欄位值 as DateTime)，欄位值使用格式化參數方式挖空以便代入(例如寫成 {0} as DateTime)，如此一來就可將「判斷是否要填入 Null」的動作延遲至指令送到資料庫後再進行，藉以繞過 DC 的 Null 排斥機制。(參考：TRY_PARSE 的用途)

Chris 的方法很合理，至少在想要把 Null 填入 DateTime 型別的欄位時是很好理解的，但…如果今天想要填入的目標欄位是 varchar 或其他的型別呢？從上段末附上的連結中 Remarks 小節可以看到 TRY_PARSE() 其實並非萬能，它只能用來「將字串轉成日期/時間/數字」，因此假如填入的目標是 varchar 或是 money 之類的，那這個方法就宣告失靈，若還硬是要搭配一些條件判斷來使用則八成會寫出令人費解的程式碼。

儘管一直以來都隱隱約約查覺到有此缺陷，但始終沒有靜下心地思考一個較通用的解決方法，總是想說真遇到了就先用 Chris 的解法擋吧，行不通再說啦。而這樣苟且的想法在最近就受到了挑戰，終於還是面對了要把可能為 Null 的參數塞入 varchar 欄位的需求。稍微嘗試一下以後發現，確實不能簡單地直接套用 Chris 的方法了，而我也完全不想在應該是設定 varchar 欄位的地方為了填入 Null 硬寫出 TRY_PARSE('' as DateTime) 這種邪魔歪道的程式碼，看來該是時候痛定思痛搜尋與嘗試一些其它解決辦法了。

在進行了幾個 Google 的動作以後，發現網路上確實有人也提出過這個困擾，但我卻沒有找到比較滿意的解法，不過倒是從查到的資料中得到了不少靈感，最後也很幸運地嘗試出了一個還算合理的解決方案。廢話不多說，直接看以下範例程式碼與說明：

[會出錯的寫法]

string compID = "WASDX";
string compName = "Foo Bar Co.";
string title = "";
StringBuilder sql = new StringBuilder();
sql.Append(@"INSERT INTO Customers (
				CustomerID,
				CompanyName, 
				ContactTitle
			)
			VALUES (
				{0}, {1}, {2}
			) ");
dc.ExecuteCommand(sql.ToString(), compID, compName, String.IsNullOrWhiteSpace(title) ? null : title);

[可以將 VALUES 處改用這種寫法]

// ...
sql.Append(@"INSERT INTO Customers (
				CustomerID,
				CompanyName, 
				ContactTitle
			)
			VALUES (
				{0}, {1}, 
				(CASE {2} when '' then null else {2} END)
			) ");
dc.ExecuteCommand(sql.ToString(), compID, compName, String.IsNullOrWhiteSpace(title) ? "" : title);

這個作法的靈感主要來自於兩件事實：

1. 寫死 Null 的 SQL 語法能運作
2. Chris 用 TRY_PARSE() 將填入 Null 的動作成功延遲至資料庫執行語法階段的經驗

總合以上兩點，這次我們就使用 SQL 的 CASE-WHEN 來幫助我們繞過 DC 吧！CASE-WHEN 可以在資料庫端擔綱如同 IF-ELSE 的判斷任務，在 DC 填入參數的地方則可以使用三元運算子來控制代入 SQL 語句的值，但注意此時還不能使用 Null，而是在滿足填入 Null 的條件時要給一個在資料正常的情況下該欄位絕對不可能出現的識別值，例如空白或是 ”null” 還是 “how do you turn this on” 之類的，透過在 SQL 中的 CASE-WHEN 來判斷代入參數是否為該識別值，若是的話該欄位就是 Null，若否就照常使用代入參數。如此一來，就可以在兼顧程式碼語意的情況下完成這個需求了，總算是解決了一個心頭大患啊！然而我相信這並不是最好的解法，畢竟要花心思選用一個不會被用到的識別值這件事還是讓人心裡有點不愉快，不過就當作給有同樣遇到這種問題的人一種可行的參考方案囉，也歡迎有不同 idea 的人可以不吝分享您的看法！