`WHERE … IN @xs`, where @xs parameter is a value list: Supported by ADO.NET?

Question

I am trying to create a parameterised ADO.NET query with a WHERE … IN @xs clause, where @xs is a short list of possibly non-contiguous values, for instance (2, 3, 5, 8, 13). Can this be done? If so, how?

This won't work:

int[] xs = { 2, 3, 5, 8, 13 };  // I've also tried using a List<int> instead
var c = new System.Data.SqlClient.SqlCommand();
c.CommandText = "SELECT … FROM … WHERE … IN @xs";
c.Parameters.AddWithValue("@xs", xs);  // throws ArgumentException due to `values`

Neither the System.Data.SqlDbType enum nor the types in the System.Data.SqlTypes namespace suggest that this scenario is supported. Must I revert to dynamic SQL (i.e. manually composing the final SQL CommandText using string operations)?

Answer 1

If you are using SQL Server 2008 or later, an alternative might be to use "table valued parameters". More info here : Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters