ASP.NET 三層架構實作多條件動態查詢：條件模型傳遞與 SQL 參數化組裝

三層架構 (UI/BLL/DAL) 中，UI 層收集使用者查詢條件，不應直接拼接 SQL 或傳遞控制項參考。正確做法：定義條件模型類別，UI 組裝模型清單傳給 BLL，DAL 根據模型動態建構參數化 SQL。

1. 共用條件模型

namespace Common.Models
{
    public enum SqlOperator
    {
        Equal, NotEqual, GreaterThan, LessThan, GreaterEqual, LessEqual,
        Like, NotLike, In, NotIn, IsNull, IsNotNull
    }

    public class SearchCondition
    {
        public string ColumnName { get; set; }      // 資料庫欄位名
        public SqlOperator Operator { get; set; }   // 運算子
        public object Value { get; set; }           // 比較值 (In 時為 IEnumerable)
        public string Logic { get; set; } = "AND";  // 與上一條件邏輯 AND/OR
    }
}

2. UI 層組裝條件 (WebForm 範例)

var conditions = new List();

// 文字模糊查詢
if (!string.IsNullOrEmpty(txtName.Text))
    conditions.Add(new SearchCondition { ColumnName = "BookName", Operator = SqlOperator.Like, Value = txtName.Text });

// 精確比對
if (!string.IsNullOrEmpty(ddlCategory.SelectedValue))
    conditions.Add(new SearchCondition { ColumnName = "CategoryId", Operator = SqlOperator.Equal, Value = ddlCategory.SelectedValue });

// 範圍查詢
if (decimal.TryParse(txtMinPrice.Text, out var min))
    conditions.Add(new SearchCondition { ColumnName = "Price", Operator = SqlOperator.GreaterEqual, Value = min });
if (decimal.TryParse(txtMaxPrice.Text, out var max))
    conditions.Add(new SearchCondition { ColumnName = "Price", Operator = SqlOperator.LessEqual, Value = max });

// 多值 IN 查詢
var pubIds = GetSelectedPublisherIds(); // List
if (pubIds.Any())
    conditions.Add(new SearchCondition { ColumnName = "PublisherId", Operator = SqlOperator.In, Value = pubIds });

// 呼叫 BLL
var books = bookBll.Search(conditions);

3. DAL 動態建構參數化 SQL

public List Search(List conditions)
{
    var sb = new StringBuilder("SELECT * FROM Books WHERE 1=1 ");
    var parameters = new List();

    for (int i = 0; i < conditions.Count; i++)
    {
        var c = conditions[i];
        string paramName = $"@p{i}";
        string logic = i == 0 ? "" : (c.Logic + " ");

        switch (c.Operator)
        {
            case SqlOperator.Like:
                sb.Append($"{logic}{c.ColumnName} LIKE {paramName}");
                parameters.Add(new SqlParameter(paramName, "%" + c.Value + "%"));
                break;
            case SqlOperator.Equal:
                sb.Append($"{logic}{c.ColumnName} = {paramName}");
                parameters.Add(new SqlParameter(paramName, c.Value));
                break;
            case SqlOperator.GreaterThan:
                sb.Append($"{logic}{c.ColumnName} > {paramName}");
                parameters.Add(new SqlParameter(paramName, c.Value));
                break;
            case SqlOperator.LessThan:
                sb.Append($"{logic}{c.ColumnName} < {paramName}");
                parameters.Add(new SqlParameter(paramName, c.Value));
                break;
            case SqlOperator.In:
                // IN 需展開多參數
                var values = (IEnumerable)c.Value;
                var inParams = new List();
                int idx = 0;
                foreach (var v in values)
                {
                    string inParam = $"@in{i}_{idx++}";
                    inParams.Add(inParam);
                    parameters.Add(new SqlParameter(inParam, v));
                }
                sb.Append($"{logic}{c.ColumnName} IN ({string.Join(",", inParams)})");
                break;
            case SqlOperator.IsNull:
                sb.Append($"{logic}{c.ColumnName} IS NULL");
                break;
                // 其他運算子類推...
        }
    }

    return SqlHelper.Query(sb.ToString(), parameters.ToArray());
}

4. SqlHelper 通用查詢方法

public static List Query(string sql, SqlParameter[] parameters) where T : new()
{
    var list = new List();
    using (var conn = new SqlConnection(ConnStr))
    using (var cmd = new SqlCommand(sql, conn))
    {
        if (parameters != null) cmd.Parameters.AddRange(parameters);
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var obj = new T();
                // 簡易映射：屬性名 = 欄位名
                foreach (var prop in typeof(T).GetProperties())
                {
                    if (reader[prop.Name] != DBNull.Value)
                        prop.SetValue(obj, reader[prop.Name]);
                }
                list.Add(obj);
            }
        }
    }
    return list;
}

生成 SQL 範例

SELECT * FROM Books WHERE 1=1 
  AND BookName LIKE @p0 
  AND CategoryId = @p1 
  AND Price >= @p2 
  AND Price <= @p3 
  AND PublisherId IN (@in4_0, @in4_1, @in4_2)

關鍵優勢總結

進階：排序、分頁、選取欄位

public class SearchRequest
{
    public List Conditions { get; set; }
    public string OrderBy { get; set; } = "Id DESC";
    public int PageIndex { get; set; } = 1;
    public int PageSize { get; set; } = 20;
    public string[] SelectColumns { get; set; } // null 表 SELECT *
}

DAL 根據 `OrderBy`、`PageIndex/Size` 追加 `ORDER BY ... OFFSET ... ROWS FETCH NEXT ... ROWS ONLY` (SQL Server 2012+)。