Using Parameters with an SQL IN Clause

在我前面的文章有提到 WHERE IN () 帶多值的問題，今天看到一篇國外的文章寫到，和大家一起分享

Using parameters can be a bit tricky with an IN clause though, e.g.

SELECT * FROM MyTable WHERE ID IN (3, 10, 17)


As an example, assume you have a ListBox containing possible field values. If the user can select zero, one or more of the items in the list to filter the results you could do this:


SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand();
StringBuilder query = new StringBuilder("SELECT * FROM MyTable");

switch (this.listBox1.SelectedItems.Count)
{
case 0:
break;

case 1:
query.Append(" WHERE MyColumn = @MyColumn");
command.Parameters.AddWithValue("@MyColumn", this.listBox1.SelectedItem);
break;

default:
query.Append(" WHERE MyColumn IN (");
string paramName;

for (int index = 0; index < this.listBox1.SelectedItems.Count; index++) { paramName = "@MyColumn" + index.ToString(); if (index > 0)
{
query.Append(", ");
}

query.Append(paramName);
command.Parameters.AddWithValue(paramName, this.listBox1.SelectedItems[index]);
}
query.Append(")");
break;
}

command.CommandText = query.ToString();
command.Connection = connection;


上面是VB的語法，我下一篇會分享實作過的C#語法。