The following query has a parameter to be assigned with a list of strings:
select * from a_table where something in :list_of_strings
I have a C# List<string> which I would like to assign to the list_of_strings parameter.
Given an OracleCommand (representing the above query), how can I bind my List<string> to the command's list_of_strings parameter?
Actually, you can't bind a single parameter to a list of values. In this case you could concatenate the values to the query string.
However, that's unadvised since there's a limit of values you can put on an IN clause.
List<string> list = new List<string>();
list.Add("1");
list.Add("2");
list.Add("3");
list.Add("4");
string listStr = string.Join(",", list);
//result: "1,2,3,4"
If your string list is a list of strings, you could do this:
List<string> list = new List<string>();
list.Add("one");
list.Add("two");
list.Add("three");
list.Add("four");
string listStr = string.Concat("'", string.Join("','", list), "'");
//result: "'one','two','three','four'"
Query string:
string query = string.Format("select * from a_table where something in({0})", listStr);
Obs: you may have to handle the possibility where the list is empty.
Another possibility would be inserting all the values on a temporary table and using it on the select statement. Which would have the advantage of unlimited string values and avoiding a new hard parse on the DBMS compared to the concatenating technique:
SELECT *
FROM A_TABLE
WHERE SOMETHING IN(SELECT SOMETHING FROM TEMP_TABLE)
