【ADO.NET】Using SqlCommand with parameters for T-SQL IN(...) Statement

在 ADO.NET 用 Parameters 串含有 IN(...) 的 SQL 命令時沒有發生效果，類似下面

cmd.CommandText = "DELETE FROM Member WHERE Name IN (@names)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("names", "'Allen','Bill','Carl'");
int ret = cmd.ExecuteNonQuery();

可能為了防 Injection 之類的，系統行為這裡不會像我想的直接當字串解析過去，查了的結論是必須自己去建總共需要的參數跟條件敘述，像

cmd.CommandText = "DELETE FROM Member WHERE Name IN (@name1, @name2, @name3)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("names1", "Allen");
cmd.Parameters.AddWithValue("names2", "Bill");
cmd.Parameters.AddWithValue("names3", "Carl");
int ret = cmd.ExecuteNonQuery();

如果是要輸入集合的話可以參考

string condi = "";
cmd.CommandText = "DELETE FROM Member WHERE Name IN ({0})";
cmd.Parameters.Clear();
for (int i = 0; i < memberList.Count; i++) {
 condi += $"@name{i},";
 cmd.Parameters.AddWithValue($"name{i}", memberList[i]);
}
condi = condi.Remove(condi.Length - 1);
cmd.CommandText = string.Format(cmd.CommandText, condi);
int ret = cmd.ExecuteNonQuery();

ref: SOF