Home > DeveloperSection > Forums > `WHERE … IN @xs`, where @xs parameter is a value list: Supported by ADO.NET?
Garry Winogrand
Garry Winogrand

Total Post:24

Points:168
Posted on    June-19-2013 3:09 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 887  View(s)
Rate this:
Hi Developers,

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)?

Any help on above is really appreciated.


Dev Patel

Total Post:26

Points:182
Posted on    June-19-2013 8:27 AM

Hi Garry,

You will have to build the parameters one at a time:

int[] xs = { 2, 3, 5, 8, 13 };
var c = new SqlCommand();
var parameters = xs.Select((x, i) => string.Concat("@xs", i)).ToList();
for (var i = 0; i < xs.Count; i++)
{
    c.Parameters.AddWithValue(parameters[i], xs[i]);
}

c.CommandText = string.Format(
    "SELECT ... FROM ... WHERE ... IN ({0})", 
    string.Join(", ", parameters)
);

Thanks in advance. 

Don't want to miss updates? Please click the below button!

Follow MindStick