// 錯誤示範:每種參數組合寫一條 SQL
if (id > 0 && string.IsNullOrEmpty(name)) {
cmd.CommandText = "SELECT * FROM T WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", id);
}
else if (id <= 0 && !string.IsNullOrEmpty(name)) {
cmd.CommandText = "SELECT * FROM T WHERE Name = @name";
cmd.Parameters.AddWithValue("@name", name);
}
else if (id > 0 && !string.IsNullOrEmpty(name)) {
cmd.CommandText = "SELECT * FROM T WHERE Id = @id AND Name = @name";
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
}
// 參數越多,組合爆炸指數級增長!
string sql = "SELECT * FROM T WHERE 1=1"; // 關鍵技巧:永真條件
var parameters = new List();
if (id != null) {
sql += " AND Id = @id";
parameters.Add(new SqlParameter("@id", id));
}
if (!string.IsNullOrEmpty(name)) {
sql += " AND Name = @name";
parameters.Add(new SqlParameter("@name", name));
}
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters.ToArray());
| 技巧 | 作用 | 注意事項 |
|---|---|---|
WHERE 1=1 | 消除首個條件前是否需 AND 的判斷邏輯 | 優化器會自動忽略常數條件,零效能影響 |
參數化 @param | 防止 SQL 注入、利用計畫快取 | 嚴禁字串拼接數值:sql += " AND Id = " + id ❌ |
| 集合收集參數 | 動態對應欄位,避免重複 AddWithValue | 建議明確指定 SqlDbType 避免型別推斷偏差 |
| NULL 代表「未指定」 | 符合資料庫三值邏輯,避免魔術數字 (如 0/-1) 污染業務邏輯 | 前端傳遞 null 而非 0 或空字串 |
public class DynamicQueryBuilder
{
private readonly StringBuilder _sql = new StringBuilder();
private readonly List _params = new List();
public DynamicQueryBuilder(string baseSql)
{
_sql.Append(baseSql);
if (!baseSql.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
_sql.Append(" WHERE 1=1");
}
public DynamicQueryBuilder AddWhere(string column, object value, string op = "=")
{
if (value != null && !(value is string s && string.IsNullOrEmpty(s)))
{
string paramName = "@" + column + _params.Count;
_sql.Append($" AND {column} {op} {paramName}");
_params.Add(new SqlParameter(paramName, value));
}
return this;
}
public DynamicQueryBuilder AddIn(string column, IEnumerable
var builder = new DynamicQueryBuilder("SELECT * FROM Orders")
.AddWhere("CustomerId", customerId)
.AddWhere("Status", status)
.AddIn("ProductId", productIds)
.AddWhere("CreatedAt", startDate, ">=")
.AddWhere("CreatedAt", endDate, "<=");
var (sql, params) = builder.Build();
using (var cmd = new SqlCommand(sql, conn)) {
cmd.Parameters.AddRange(params);
// ExecuteReader...
}
WHERE 1=1 + 參數化集合 是動態 SQL 的黃金標準:
① 邏輯清晰、零排列組合;
② 參數化防注入、利用計畫快取;
③ 語意明確 (NULL = 未篩選);
④ 易於擴展 (IN、BETWEEN、LIKE 等運算子)。