Click here to Skip to main content
14,866,398 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Team,
I have an Oracle select query with IN clause which needs to be pass ids with parameterized

C#
string ids ="1,2,3,4,5";
string query="select * from employee where empid in (" + ids + " and ROWNUM <=10)";


What I have tried:

C#
string ids ="1,2,3,4,5";
string query="select * from employee where empid in (" + ids + " and ROWNUM <=10)";
OracleCommand cmd = new OracleCommand(query, con);
string[] lstIds = ids.Split(",").Select(s => (s).ToArray();
stringBuilder sb = new StringBuilder();
string strParameter = string.empty;
string Param = "Param";
for(int i = 0; i <= lstIds.Length - 1; i++)
{
    strParameter += i == 0 ? Param + i : ",:" + Param + i;
    if (i == 0)
        sb.Append(lstIds[1]);
    else
        sb.Append("," + lstIds[1]);
}

cmd.Parameters.Add(Param, sb.ToString());

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds= new DataSet();
da.Fill(ds);

Could you please assist on this how to use parameterised query with In clause
Posted
Updated 8-Oct-20 5:30am
v2

1 solution

Basically speaking, you can't: Using comma separated value parameter strings in SQL IN clauses[^] explains why.

It also explains how it is possible in SqlServer by passing the list of IDs as a string via a query parameter, and then converting that to a temporary table which the IN clause can then reference. But I have no idea how that function could work in Oracle - I don't use it. It might work. It might not!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900