Geek Answers Handbook

How to pass a list as a parameter in .NET.

I have a query that looks something like this:

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

I would like to pass the Id list as a single parameter with IDbDataParameter where the request is formatted:

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

and then have one parameter, which is a list, and not do something like this:

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

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

PS the reason why I want it to be as one parameter of the list, and not a series of parameters, is that as the number of parameters changes, MySQL will consider the query as new, and we will lose some of the caching optimizations. MySQl basically ends with a single request for the number of identifiers. This is the same reason I just don't want to manipulate the underlying SQl as a string, because then I get one request per VAULE, and that would be worse.

+4
source share
4 answers

Is it possible to use:

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

After creating the array and filling it as you want:

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

I'm not quite sure that this was what you asked, but this is what I think I understood from your post.

+2
source

If I understand correctly, you want to pass one parameter to your request and divide it into what you can use with the "IN" operator. What I did in the past uses a string parameter and populates it with a comma-delimited list (the delimiter can be any), and then created a sql function to turn the comma-delimited list into a table. I used a table derived from sql function using the IN statement.

I used the MS Sql server, so I'm not sure if this is possible in MYSQL. If it looks 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 a delimited list. I don't know if this can be done in mysql.

0
source

You can pass a delimited list and use the table-variable function to “split” the list into lines.

I posted an example here.

0
source

I don’t understand if you are talking about LINQ, but if so, you should flip it 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 bother with escaping / string manipulations yourself.

As far as I know, sql cannot accept arrays as parameters for / procs functions in general, so I doubt if cached execution plans can even express the idea of ​​an array.

If you have a reasonable number of elements, you can "overload" the sproc for each parameter count to a certain number, after which it will be in the usual dynamic way.

 StuffWithList1 one StuffWithList2 one two StuffWithList3 one two three

Or simply

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

and pass zeros for unnecessary parameters:

 StuffWithList 8 9 3 null null null null null null null null ... null
0
source

More articles:


All Articles