三層架構(UI → BLL → DAL)要求 UI 層不直接拼 SQL、DAL 層不直接存取 UI 控制項。多條件查詢若在 UI 拼 SQL 破壞分層,若在 DAL 拼 SQL 則缺乏條件來源。解決方案:引入 條件模型 (SearchCriteria) 作為層間傳遞契約。
public enum SqlOperator { Equal, NotEqual, GreaterThan, LessThan, Like, In, Between }
public class SearchCondition
{
public string ColumnName { get; set; } // 資料庫欄位名
public object Value { get; set; } // 比對值
public SqlOperator Operator { get; set; } // 運算子
public string Logic { get; set; } = "AND"; // 與下一條件邏輯關係 AND/OR
}
var conditions = new List<SearchCondition>();
void AddCondition(string column, object value, SqlOperator op)
{
if (value == null || (value is string s && string.IsNullOrWhiteSpace(s))) return;
conditions.Add(new SearchCondition { ColumnName = column, Value = value, Operator = op });
}
// 範例:圖書查詢
AddCondition("BookName", txtName.Text, SqlOperator.Like);
AddCondition("Author", txtAuthor.Text, SqlOperator.Like);
AddCondition("CategoryId", ddlCategory.SelectedValue, SqlOperator.Equal);
AddCondition("Price", txtMinPrice.Text, SqlOperator.GreaterThan); // 需驗證數值
// ...
public List<Book> SearchBooks(List<SearchCondition> conditions)
{
return BookDAL.Instance.Search(conditions);
}
public List<Book> Search(List<SearchCondition> conditions)
{
var sb = new StringBuilder("SELECT * FROM Books WHERE IsDeleted=0 ");
var parameters = new List<SqlParameter>();
int paramIndex = 0;
foreach (var c in conditions)
{
string paramName = $"@p{paramIndex++}";
string clause = c.Operator switch
{
SqlOperator.Equal => $"{c.ColumnName} = {paramName}",
SqlOperator.NotEqual => $"{c.ColumnName} <> {paramName}",
SqlOperator.GreaterThan => $"{c.ColumnName} > {paramName}",
SqlOperator.LessThan => $"{c.ColumnName} < {paramName}",
SqlOperator.Like => $"{c.ColumnName} LIKE {paramName}",
SqlOperator.In => $"{c.ColumnName} IN ({string.Join(",", ((IEnumerable)c.Value).Cast<object>().Select((_, i) => $"{paramName}{i}"))})",
SqlOperator.Between => $"{c.ColumnName} BETWEEN {paramName}0 AND {paramName}1",
_ => throw new ArgumentException($"不支援的運算子: {c.Operator}")
};
sb.Append($" {c.Logic} {clause} ");
// 參數值處理
if (c.Operator == SqlOperator.Like)
parameters.Add(new SqlParameter(paramName, $"%{c.Value}%"));
else if (c.Operator == SqlOperator.In)
{
var vals = ((IEnumerable)c.Value).Cast<object>().ToArray();
for (int i = 0; i < vals.Length; i++)
parameters.Add(new SqlParameter($"{paramName}{i}", vals[i]));
}
else if (c.Operator == SqlOperator.Between)
{
var range = (Array)c.Value;
parameters.Add(new SqlParameter($"{paramName}0", range.GetValue(0)));
parameters.Add(new SqlParameter($"{paramName}1", range.GetValue(1)));
}
else
parameters.Add(new SqlParameter(paramName, c.Value));
}
return SqlHelper.Query<Book>(sb.ToString(), parameters.ToArray());
}
若使用 Dapper,可直接將 DynamicParameters 組裝傳入;EF Core 則可透過 IQueryable 動態套用 Where 條件,原理相同但語法更簡潔。