動態 SQL 查詢常見需求:根據使用者輸入的查詢條件(可能為空),只在 WHERE 子句中加入有值的條件,並使用參數化防止 SQL Injection。本文示範使用 List<SqlParameter> 動態累加參數,最後 ToArray() 傳遞給執行方法。
// 錯誤:即使 a.Name 為 null/空字串,仍加入 @name 參數
// SQL 變成 ... AND [name] = @name,參數值為 null/'',可能查無資料或全表掃描
string sql = "SELECT * FROM [User] WHERE 1=1";
sql += " AND [Id]=@id AND [Name]=@name AND [Code]=@code";
var parameters = new[]
{
new SqlParameter("@id", a.Id),
new SqlParameter("@name", a.Name), // 可能為 null
new SqlParameter("@code", a.Code),
};
public UserInfo GetUser(UserInfo filter)
{
// 1. 基礎 SQL
string sql = "SELECT Id, Name, Code, Password FROM [User] WHERE 1=1";
// 2. 動態組裝 WHERE 並同步建立參數
var paramList = new List();
if (filter.Id > 0)
{
sql += " AND [Id] = @id";
paramList.Add(new SqlParameter("@id", filter.Id));
}
if (!string.IsNullOrEmpty(filter.Name))
{
sql += " AND [Name] = @name";
paramList.Add(new SqlParameter("@name", filter.Name));
}
if (!string.IsNullOrEmpty(filter.Code))
{
sql += " AND [Code] = @code";
paramList.Add(new SqlParameter("@code", filter.Code));
}
if (!string.IsNullOrEmpty(filter.Password))
{
sql += " AND [Password] = @pwd";
paramList.Add(new SqlParameter("@pwd", filter.Password));
}
// 3. 轉陣列執行
SqlParameter[] parameters = paramList.ToArray();
using (var reader = SqlHelper.ExecuteReader(sql, parameters))
{
if (reader.Read())
{
return new UserInfo
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Code = reader.GetString(2),
Password = reader.GetString(3)
};
}
}
return null;
}
public static class SqlHelper
{
private static string ConnStr => ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)
{
var conn = new SqlConnection(ConnStr);
var cmd = new SqlCommand(sql, conn);
if (parameters != null && parameters.Length > 0)
cmd.Parameters.AddRange(parameters); // 安全:支援空陣列
conn.Open();
// CommandBehavior.CloseConnection: Reader 關閉時自動關閉連線
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
// 需求:WHERE Id IN (1,2,3,...) 參數個數不固定
var ids = new[] { 1, 2, 3, 4, 5 };
var paramNames = ids.Select((id, i) => $"@id{i}").ToArray();
sql += $" AND [Id] IN ({string.Join(",", paramNames)})";
for (int i = 0; i < ids.Length; i++)
paramList.Add(new SqlParameter(paramNames[i], ids[i]));
if (!string.IsNullOrEmpty(filter.Keyword))
{
sql += " AND ([Name] LIKE @kw OR [Code] LIKE @kw)";
paramList.Add(new SqlParameter("@kw", "%" + filter.Keyword + "%"));
}
| 面向 | 字串拼接 | 參數化 + 動態建構 |
|---|---|---|
| SQL Injection | 高風險 | 完全防禦 |
| 執行計畫重用 | 難(每種組合不同 SQL 文本) | 較好(相同條件組合重用) |
| 空值處理 | 易出錯 | 條件直接不加入 WHERE |
| 可讀性 | 低 | 高 |
// Dapper 動態參數
var sql = "SELECT * FROM [User] WHERE 1=1";
var param = new DynamicParameters();
if (filter.Id > 0) { sql += " AND Id=@id"; param.Add("@id", filter.Id); }
if (!string.IsNullOrEmpty(filter.Name)) { sql += " AND Name=@name"; param.Add("@name", filter.Name); }
var user = conn.QueryFirstOrDefault(sql, param);
List<SqlParameter> 動態累加 + ToArray() 是 ADO.NET 時代處理動態條件的標準模式。核心原則:**SQL 字串與參數陣列同步建構,絕不將使用者輸入直接拼接進 SQL**。此模式可平滑遷移至 Dapper 的 DynamicParameters 或 EF Core 的 WhereIf 擴充,邏輯完全一致。