Adding a list of strings to OracleCommand.Parameters in C# [duplicate]

Question

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?

Answer 1

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)