管理系統常見「不定條件查詢」：使用者可填寫 10+ 欄位任意組合查詢。傳統 `StringBuilder` 拼接 SQL 程式碼冗長、難維護、易注入、可讀性差。
public static class SqlBuilderExtensions {
// 基礎：等值條件 (參數有值才加入 WHERE)
public static string WhereIf(this string sql, string column, string value, string op = "=") {
if (string.IsNullOrWhiteSpace(value)) return sql;
return $"{sql} AND {column} {op} @{column.Replace(".", "_")}";
// 實務建議：回傳 (sql, parameters) Tuple 或使用 Dapper 動態參數
}
// 進階：字串參數化版本 (搭配 Dapper/ADO.NET 參數集合)
public static (string Sql, DynamicParameters Params) WhereIf(
this (string Sql, DynamicParameters Params) tuple,
string column, string value, string op = "=")
{
var (sql, p) = tuple;
if (string.IsNullOrWhiteSpace(value)) return tuple;
string paramName = $"p_{column.Replace(".", "_").Replace("[", "").Replace("]", "")}_{Guid.NewGuid():N}";
sql += $" AND {column} {op} @{paramName}";
p.Add(paramName, value);
return (sql, p);
}
// 範圍查詢：起迄皆可選
public static (string Sql, DynamicParameters Params) BetweenIf(
this (string Sql, DynamicParameters Params) tuple,
string column, string start, string end)
{
var (sql, p) = tuple;
bool hasStart = !string.IsNullOrWhiteSpace(start);
bool hasEnd = !string.IsNullOrWhiteSpace(end);
if (!hasStart && !hasEnd) return tuple;
if (hasStart && hasEnd) {
string pStart = $"p_{column}_s_{Guid.NewGuid():N}";
string pEnd = $"p_{column}_e_{Guid.NewGuid():N}";
sql += $" AND {column} BETWEEN @{pStart} AND @{pEnd}";
p.Add(pStart, start);
p.Add(pEnd, end);
} else if (hasStart) {
string pStart = $"p_{column}_s_{Guid.NewGuid():N}";
sql += $" AND {column} >= @{pStart}";
p.Add(pStart, start);
} else {
string pEnd = $"p_{column}_e_{Guid.NewGuid():N}";
sql += $" AND {column} <= @{pEnd}";
p.Add(pEnd, end);
}
return (sql, p);
}
// Like 模糊查詢
public static (string Sql, DynamicParameters Params) LikeIf(
this (string Sql, DynamicParameters Params) tuple,
string column, string value)
{
var (sql, p) = tuple;
if (string.IsNullOrWhiteSpace(value)) return tuple;
string paramName = $"p_{column}_like_{Guid.NewGuid():N}";
sql += $" AND {column} LIKE @{paramName}";
p.Add(paramName, $"%{value}%");
return (sql, p);
}
// In 子句 (陣列/列表)
public static (string Sql, DynamicParameters Params) InIf(
this (string Sql, DynamicParameters Params) tuple,
string column, IEnumerable values)
{
var (sql, p) = tuple;
var list = values?.Where(v => v != null).ToList();
if (list == null || list.Count == 0) return tuple;
// Dapper 支援直接傳 IEnumerable 產生 @p0, @p1...
sql += $" AND {column} IN @{column}_in";
p.Add($"{column}_in", list);
return (sql, p);
}
}
// 80+ 行 if-else 拼接，參數處理分散，SQL 注入風險
StringBuilder sb = new StringBuilder("SELECT * FROM V_QueryGR WHERE 1=1 ");
if (!string.IsNullOrEmpty(varShipto)) sb.Append(" AND Plant=@Plant ");
if (!string.IsNullOrEmpty(varGRNO)) { /* 範圍邏輯 */ }
// ... 重複 13 次
using Dapper; // 需安裝 Dapper 套件
public DataTable GetGRCollections(QueryParams q) {
var baseSql = "SELECT * FROM V_QueryGR WHERE 1=1";
var p = new DynamicParameters();
var (sql, params) = (baseSql, p)
.BetweenIf("GRTime", q.DateFrom, q.DateTo)
.WhereIf("Plant", q.ShiftTo)
.WhereIf("GRNO", q.GRNO) // 單值
// 範圍邏輯需自訂擴展
.WhereIf("MaterialNO", q.MaterialNO)
.BetweenIf("PackingNO", q.PL, q.PLTo)
.WhereIf("CustomID", q.CustomerID)
.WhereIf("CustomID2", q.CustomerID1)
.WhereIf("CustomerPN", q.CustomerPN)
.WhereIf("SuplierPN", q.SupplierPN)
// Checked 狀態
.WhereIf("CheckPrice", q.Checked == "Checked" ? "1" :
q.Checked == "UnChecked" ? "0" : null);
// 執行查詢
using (var conn = new SqlConnection(connStr)) {
return conn.Query(sql, params).AsDataTable(); // 擴展方法轉 DataTable
}
}
// 參數物件 (Model Binding 自動填入)
public class QueryParams {
public string ShiftTo { get; set; }
public string GRNO { get; set; }
public string GRNOTo { get; set; }
public string MaterialNO { get; set; }
public string PL { get; set; }
public string PLTo { get; set; }
public string CustomerID { get; set; }
public string CustomerID1 { get; set; }
public string CustomerPN { get; set; }
public string DateFrom { get; set; }
public string DateTo { get; set; }
public string Checked { get; set; }
public string SupplierPN { get; set; }
}
public static (string Sql, DynamicParameters Params) OrderByIf(
this (string Sql, DynamicParameters Params) tuple,
string column, string direction = "ASC")
{
var (sql, p) = tuple;
if (string.IsNullOrWhiteSpace(column)) return tuple;
string dir = direction.ToUpper() == "DESC" ? "DESC" : "ASC";
// 防注入：白名單驗證欄位名
var allowed = new[] { "GRTime", "GRNO", "Plant", "MaterialNO" };
if (!allowed.Contains(column)) throw new ArgumentException("非法排序欄位");
return ($"{sql} ORDER BY {column} {dir}", p);
}
public static (string Sql, DynamicParameters Params) Page(
this (string Sql, DynamicParameters Params) tuple,
int pageIndex, int pageSize)
{
var (sql, p) = tuple;
// SQL Server 2012+ OFFSET FETCH
sql += $" OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY";
p.Add("offset", (pageIndex - 1) * pageSize);
p.Add("pageSize", pageSize);
return (sql, p);
}
public PagedResult Search(GridQueryRequest req) {
var (sql, p) = ("SELECT * FROM V_QueryGR WHERE 1=1", new DynamicParameters())
.WhereIf("Plant", req.Filters.GetValueOrDefault("Plant"))
.LikeIf("MaterialNO", req.Filters.GetValueOrDefault("MaterialNO"))
.BetweenIf("GRTime",
req.Filters.GetValueOrDefault("DateFrom"),
req.Filters.GetValueOrDefault("DateTo"))
.OrderByIf(req.SortColumn, req.SortDirection)
.Page(req.PageIndex, req.PageSize);
using var conn = new SqlConnection(_connStr);
var totalSql = $"SELECT COUNT(1) FROM ({sql.Split(new[]{"ORDER BY"}, StringSplitOptions.None)[0]}) t";
int total = conn.ExecuteScalar(totalSql, p);
var items = conn.Query(sql, p).ToList();
return new PagedResult { Items = items, TotalCount = total, PageIndex = req.PageIndex };
}
| 指標 | 字串拼接 | 擴展方法 + 參數化 |
|---|---|---|
| 程式碼行數 | 80+ | 15-20 |
| SQL 注入風險 | 高 (字串插值) | 無 (參數化) |
| 計畫快取重用 | 差 (每組合產生新 SQL) | 佳 (固定參數化模板) |
| 可測試性 | 難 (靜態方法、副作用) | 易 (純函式、可 Mock) |
| 維護性 | 低 (散落邏輯) | 高 (集中、可組合) |
