sql - C# syntax for tsql "IN" parameter -

in tsql can write:

select * mytablename  m m.field in (1, 5, 8, 56) 

if want same thing in parameterized c#, syntax?

the sql server 2008 has feature called table-valued parameters. create "special type" in sql server , can pass datatable parameter, containing values want.

you can use way:

on db this: create type dbo.intarray table (value int not null)

your in query must changed like: customerid in (select value @1)

// array of ids int[] ids = new[] { 1, 2, 3, 4, 5, 6, 7, 10 };  using (var connection = new sqlconnection("initial catalog=adventureworkslt2012;integrated security=true")) {     connection.open();      using (var command = new sqlcommand("select customerid saleslt.customer customerid in (select value @1)", connection))     {         // untyped datatable         var dt = new datatable();          // single column         dt.columns.add();          // copy ids in datatable         foreach (var v in ids)         {             dt.rows.add(v);         }          // create table-valued parameter         var param = command.parameters.addwithvalue("@1", dt);         param.sqldbtype = sqldbtype.structured;         param.typename = "dbo.intarray";          using (var reader = command.executereader())         {             while (reader.read())             {                 int id = (int)reader[0];                  console.writeline(id);             }         }     } } 

technically change query in like

inner join @1 par on customerid = par.value 

this has advantage create multi-column datatable , table-valued parameter , search on multiple conditions @ same time.

(note code overlong because it's working example based on microsoft's adventureworks db)