How to Pass a list as a param in .NET

Question

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.

I don't know if this answers your question, but why not use params[] int ids? — alexn
– alexn, Commented Sep 21, 2009 at 17:49
@alexn: this is about ADO.NET command parameters, not about methods at all. — Pavel Minaev
– Pavel Minaev, Commented Sep 21, 2009 at 18:09
Do any of these questions help? stackoverflow.com/search?q=parameterize+in+clause — Greg
– Greg, Commented Sep 21, 2009 at 19:50
@Greg they all point to the direction Im going which is to loop over and make individual params. I was hoping there was a param type that was a list itself and that does not seem to be the case. — ryber
– ryber, Commented Sep 21, 2009 at 21:00

Sivvy · Accepted Answer · 2009-09-21 17:58:38Z

2

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.

answered Sep 21, 2009 at 17:58

Sivvy

85210 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ryber Over a year ago

not quite, that would be direct string manipulation of the query. Which is easy, but Im looking to making the list a prepared statement with proper IDbDataParameter objects

Christian Blackburn Jun 17 at 21:05

This example creates invalid syntax because the strings aren't quoted. You need the result to be in ('id1', 'id2'). Notice the apostrophes. I typically solve this by looping through the list/array and building the string.

Binz · Accepted Answer · 2009-09-21 19:45:03Z

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.

Community · Accepted Answer · 2017-05-23 11:58:26Z

0

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.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Sep 22, 2009 at 1:17

JNappi

1,6131 gold badge16 silver badges28 bronze badges

Comments

Jason Kleban · Accepted Answer · 2009-09-22 01:32:34Z

0

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

answered Sep 22, 2009 at 1:32

Jason Kleban

21k19 gold badges83 silver badges128 bronze badges

2 Comments

ryber Over a year ago

Nope, not using LINQ, just the ye-olde ADO.Net classes from .NET 1 specifically IDbDataParameter

Jason Kleban Over a year ago

Ah ok sorry. Well at least the StuffWithList with many parameters should still work for you.

Collectives™ on Stack Overflow

How to Pass a list as a param in .NET

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related