在使用SQL Parameter的情況下,進行可選擇化聯集條件(AND)的設計手法

在資料庫程式設計時期,我們常常會遇到使用SQL Parameter的設計方式,又必須考量到可選擇性的聯集條件設計,常常會讓我們必須產生很多種IF判斷式,來視各種情況撰寫所適用的SQL,以利或者最佳化運行的效能。

在這邊分享一下一個SQL指令的小技巧,可以讓這種情境下的程式碼,可以更簡潔易讀。

情境假設:

假設我們要在資料庫查詢一些資料,想要達到的動作條件是:

  1. 查詢某些特定姓氏的員工(使用LIKE)。
  2. 如果有給予性別條件的話,那就再使用AND條件進行特定性別的聯集查詢,若沒有給予則忽略。
  3. 如果有給予年齡條件的話,那就再使用AND條件進行大於此年齡的聯集查詢,若沒有給予則忽略。

在一般的SQL Parameter設計下,這樣的條件會讓我們可能要用一些IF的語句來切割SQL指令設計,但是事實上,我們可以用下方的設計來巧妙的規避。

//SQL語句
oCmd.CommandText = @"
SELECT * FROM	Employee
WHERE
		cFirstName LIKE @cFirstName + '%'
	AND 
		(cSex = @cSex OR @cSex IS NULL)
	AND
		(iAge > @iAge OR @iAge IS NULL)
";

//加入參數
oCmd.Parameters.Add("@cFirstName",  System.Data.SqlDbType.NVarChar, 10).Value = cFirstName;
oCmd.Parameters.Add("@cSex",        System.Data.SqlDbType.NVarChar,  1).Value = (string.IsNullOrEmpty(cSex)) ? (System.Object)System.DBNull.Value : cSex;
oCmd.Parameters.Add("@iAge",        System.Data.SqlDbType.Int).Value          = (iAge == 0)                  ? (System.Object)System.DBNull.Value : iAge;

從上面的程式碼,我們可以得知cFirstName是一個必要帶值的文字字串,然後我們再利用小技巧接續個'%'字串,來達成LIKE的模糊查詢,這點應該不需要再討論。而真正應該要關注的是,有可能我們在執行期時,並未對cSex、iAg進行賦值的動作,所以cSex有可能一開始為null或為空字串,而iAge有可能一開始就為整數實值型別的預設值,也就是0。

當我們並未對cSex給予任何字串,則SQL Parameter會被帶入System.DBNull.Value,而繞到SQL指令時,DBNull觸發到IS NULL,而基於OR邏輯,導致整段AND變成空判斷(相當於AND一個TRUE邏輯,等於被跳過),藉由這個技巧,前面的cSex = @cSex就直接被忽略掉了,也就是,這樣的寫法讓Parameter變得具備「可選擇性」。

同樣的道理,當我們並未對iAge給予任何數值,則SQL Parameter會被帶入System.DBNull.Value,而繞到SQL指令時,DBNull觸發到IS NULL,而基於OR邏輯,導致整段AND變成空判斷(相當於AND一個TRUE邏輯,等於被跳過),藉由這個技巧,前面的iAge > @iAge就直接被忽略掉了,也就是,這樣的寫法讓Parameter變得具備「可選擇性」。

而當我們可能對cSex或iAg進行賦值時,前面的cSex = @cSex、iAge > @iAge會被觸發,OR後面的條件也會被當然的否定掉(任何有值的東西都不可能等於NULL),因此在SQL的運行上,又可以巧妙的OR一個FALSE,等同於後面這個OR的條件被忽視掉了。

大家覺得這樣的語法,是不是一個很巧妙的設計呢?

C# Database SqlParameter SqlCommand OptionalConditions AND OR