How to Pass a list as a param in .NET

Question

Hello, I have a query that is basically like this:

SELECT foo
FROM bar
where bar.Id in (1,2,3);

I would like to pass the list of Id's in as a single param with IDbDataParameter where the query is formatted:

SELECT foo
FROM bar
where bar.Id in (?ListOfID);

and then have a single param that is a list rather than having to do something like this:

SELECT foo
FROM bar
where bar.Id in (?id1, ?id2, ?id3);

I know this is possible in other data providers can I do this with the standard System.Data classes?

P.S. the reason I want it as a single list param rather than a series of params is because as the number of params changes MySQL will view the query as new and we loose some of the caching optimizations. MySQl basically ends up with one query per number of ID's. This is the same reason I dont just want to manipulate the base SQl as a string, because then I end up with one query per VAULE and that would be worse.

Answer 1

Is it possible to use:

string[] myParamaters = new string[2];
myParameters[0] = "id1"
myParameters[1] = "id2"

After creating an array and filling it how you want:

SELECT foo
FROM bar
where bar.Id in (string.Join(", ", myParameters));

I'm not totally sure if that was what you were asking, but it's what I think I understood from your post.

Answer 2

If I understand correctly you want to pass in one parameter into your query and have it split out into something that can be used with an 'IN' operator. What I've done in the past is used a string parameter and filled it with comma delimited list (delimiter can be whatever) then created a sql function to turn the comma delimited list into a table. I used the table outputted from the sql function with the 'IN' operator.

I was using MS Sql Server so not sure if this is possible in MYSQL. If it is would like something like

SELECT foo FROM bar where bar.Id in (SELECT * FROM ConvertToTableFunctionOrProc(?DelimitedList, ?Delimiter));

The function creates a table with one column and one row for each value in the delimited list. Don't know if this can be done in mysql.

Answer 3

You can pass in a delimited list and use a table-variable function to 'split' the list into rows.

I've posted a example here.

Answer 4

It is unclear to me whether you are talking about LINQ, but if you are, you should flip it around and use Contains().

var whatever = from bar in bars
               where ListOfID.Contains(bar.Id)
               select bar;

Yes, the list is converted to dynamic sql, but at least you don't have to mess around with escaping/string manipulation on your own.

As far as I know, sql can't accept arrays as parameters to functions/procs at all, so I doubt whether cached execution plans can even express the idea of an array.

If you have a reasonable number of items, you could "overload" the sproc for each count of parameters up to a certain number, beyond which it would be the regular dynamic way.

StuffWithList1 one
StuffWithList2 one two
StuffWithList3 one two three

OR, just

StuffWithList one two three four five six seven eight nine ten eleven ... twenty

and pass nulls for the unneeded parameters:

StuffWithList 8 9 3 null null null null null null null null ... null