Passing a SQL parameter to an IN() clause using typed datasets in .NET
See the question and my original answer on StackOverflowSQL Server 2008 has a feature called Table-Valued Parameters
So you need to
- define your query as
SELECT * from Table WHERE ID IN (SELECT * FROM (@IDs))
- go back in the TableAdapter visual designer in Visual Studio, and update the @IDS parameter to modify the @IDS parameter as DbType=Object and ProviderType=Structured
- run this SQL batch in the database your are using:
CREATE TYPE MyIntArray AS TABLE ( Value INT );GO
. This will create a MyIntArray "table type" with just one column of INT type. - Now the tricky thing is to pass the "MyIntArray" type to the TableAdapter, on the ADO.NET side.
Unfortunately the Table Adapter designer does not support the SqlParameter.TypeName
argument, so we need to fix it by ourselves. The goal is to modify the CommandCollection
property of the generated TableAdapter class. Unfortunately, this property is protected, so you have to derive the TableAdapter or for example use Reflection to tweak it. Here is an example with a derived class:
public class MyTableAdapter2 : MyTableAdapter
{
public MyTableAdapter2()
{
SqlCommand[] cmds = base.CommandCollection;
// here, the IDS parameter is index 0 of command 1
// you'll have to be more clever, but you get the idea
cmds[1].Parameters[0].TypeName = "MyIntArray";
}
}
And this is how you can call this method:
MyTableAdapter t = new MyTableAdapter2();
// create the TVP parameter, with one column. the name is irrelevant.
DataTable tvp = new DataTable();
tvp.Columns.Add();
// add one row for each value
DataRow row = tvp.NewRow();
row[0] = 1;
tvp.Rows.Add(row);
row = tvp.NewRow();
row[0] = 2;
tvp.Rows.Add(row);
row = tvp.NewRow();
row[0] = 3;
tvp.Rows.Add(row);
t.Fill(new MyDataTable(), tvp);