篩選條件一多，程式中的if就增加，程式複雜度也就增加!
有什麼好的方法呢?
最近看到程式的Code，裡面因為有一些篩選條件當使用者有輸入時，才要進行篩選。
所以程式就會用if去判斷使用者有輸入，就加入SQL的篩選條件字串，如下的Code，
//Way 1:用 if 判斷，需要再加入
using (SqlConnection connection = new SqlConnection(connectionString))
{
string commandText = @"SELECT USR_ID, CNAME, ENAME FROM EMP_EMPLOYEE WHERE 1 = 1";
if (!string.IsNullOrWhiteSpace(txtCName.Text))
{
commandText += " AND CNAME = @CNAME";
}
SqlCommand command = new SqlCommand(commandText, connection);
if (!string.IsNullOrWhiteSpace(txtCName.Text))
{
command.Parameters.AddWithValue("@CNAME", txtCName.Text);
}
try
{
connection.Open();
var reader = command.ExecuteReader();
// .....
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}這樣筆者認為有以下的問題點，
1.SQL太過分散，如果條件一多，無法知到這個SQL到底會組出什麼樣子
2.太多的判斷，增加程式的複雜度，程式碼也會變多
所以可以在篩選條件中多加 OR 的方式(原篩選條件 OR 輸入值 = '')，來達到如果使用者沒有輸入，就代表不使用條件去篩選，如下，
//Way 2:用 OR 來決定用那個篩選條件
using (SqlConnection connection = new SqlConnection(connectionString))
{
string commandText = @"SELECT USR_ID, CNAME, ENAME FROM EMP_EMPLOYEE WHERE 1 = 1
AND (CNAME = @CNAME OR @CNAME = '')";
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.AddWithValue("@CNAME", txtCName.Text);
try
{
connection.Open();
var reader = command.ExecuteReader();
// .....
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
以上的方式是筆者慣用的方式，雖然SQL會變多，但程式碼少了if的判斷，也能看到完整的SQL。
大家可以試看看哦!
2014/11/24 小喵提醒SQL效能問題
可以比較一下SQL的執行計畫，看看加入OR是否是否會影響到效能(無法使用到index，而造成Scan)哦!
2014/11/25 使用 James 的 BigTable 來測試，
CREATE TABLE [BigTable]
(
A1 NVARCHAR(4),
A2 NVARCHAR(10),
A3 NCHAR(100),
A4 NCHAR(200),
A5 INT,
A6 VARCHAR(30)
)
GO
DECLARE @I INT;
DECLARE @J INT;
DECLARE @K INT;
SET @J = 0;
SET NOCOUNT ON;
WHILE @J < 10
BEGIN
SET @I = 0;
BEGIN TRAN
WHILE @I < 10000
BEGIN
SET @K = @J*100000+@I ;
INSERT INTO [BigTable] ( A1,A2,A3,A4,A5 , A6) VALUES ( 'AAA', RIGHT('0000000000'+STR(@K),10), NEWID(),NEWID(),@K, LTRIM(STR(@I)) )
SET @I += 1;
END
COMMIT
SET @J += 1;
END
針對 A6 欄位 Search ，如果沒有 index 的話，都使用 Scan 所以就沒有差別
如果依SQL的建議，加入index的話，那有沒有使用 OR 就差蠻多的哦!
小喵說的對，的確是用不到Index，如下，
在程式維護性與SQL的執行效能之間，就請大家依實際的需求進行選擇。
感謝小喵、Alice、James、Terry等先進們給筆者的分享與測試，真的很謝謝你們!
另外在使用EF來查詢資料時，如果欄位是允許NULL的話，EF在查詢條件中加入OR，如下
--EF 6.1.2
string courseName = "Course_7";
var courses2 = from c in db.Courses
where c.Name == courseName
select c;
var coursesL2 = courses2.ToList();
產出的SQL類似如下，
DECLARE @Name NVARCHAR(30);
感謝小章哥的一起討論與解惑!!!
SQL的效能問題可以參考「動態查詢條件(Catch-all Queries)」
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
