Send parameters for IN operator in sql using parameterized queries in c#

Question

I am using asp.net , C# to make my app.

In there for database oprations I am suing parametrised queries.

Here is a code

mySqlCommand = new MySqlCommand();
mySqlCommand.Parameters.AddWithValue("@ids", ids);

switch (privilegeType.ToString())
{
    case "CorporateLead":
        myQuery = @"SELECT 
  leavea.applied_leave_id_pk,
  leavea.emp_id_fk,
  leavea.emp_name AS NAME,
  leavea.start_date,
  leavea.end_date,
  leavea.is_half_day,
 ..............
FROM
  lea_applied_leave AS leavea 
  INNER JOIN emp_employee_master AS emp 
    ON emp.employee_master_id_pk = leavea.emp_id_fk 
WHERE emp.`corporate_id_fk` IN (@ids) ;

In there ids will include (10,11,12)

Ids is a string. Parameter counter will be vary according to the login user. I pass that string as a parameterized query.

But when app execute this it only getting the result which belongs to id of 10.

When I execute this code directly on MySQL it shows correct result.

So what is the wrong here? Is there any way to send parameters for IN operator?

`WHERE FIND_IN_SET(emp.corporate_id_fk, @ids);` But keep in mind it could be slow (no index usage) — Lukasz Szozda, Mar 11 '16 at 11:36

score 1 · Accepted Answer · answered Mar 11 '16 at 11:29

1

Parameters are not a substitution for string operations. It takes the value 'as-is'.

That said, your SQL should read like this:

WHERE emp.`corporate_id_fk` IN (@id1, @id2, @id3) ;

Add a separate parameter for each value in your SQL statement.

answered Mar 11 '16 at 11:29

Patrick Hofman

153,850
22
249
325

parameter counter will be vary according to the login user. – Prageeth Liyanage Mar 11 '16 at 11:40
So you have to dynamically create SQL. That isn't that hard, is it? – Patrick Hofman Mar 11 '16 at 11:40
What about the programming part? mySqlCommand.Parameters.AddWithValue("@id1", id1); – Prageeth Liyanage Mar 11 '16 at 11:42
Yes, you have to split that too. – Patrick Hofman Mar 11 '16 at 11:46
What isn't working? Do you get an exception? What is the code used? – Patrick Hofman Mar 11 '16 at 11:59

score 0 · Answer 2 · edited May 23 '17 at 12:15

From my past experience (which is not extensive), Passing an array of data in SqlCommand.Parameters doesn't really work as expected.

Though there are a few ways in which we can do this.

1) Table Value Parameters

Have a look at the following answer and links in it.

How to pass table value parameters to stored procedure from .net code

2) Create query using string builder (or string format)

Get @IDs in a format that we can directly put it in using string format.

string Ids = @"'10', '11', '12'";

string myQuery = string.format(@"SELECT leavea.applied_leave_id_pk, leavea.emp_id_fk,   leavea.emp_name AS NAME, leavea.start_date, leavea.end_date, leavea.is_half_day FROM lea_applied_leave AS leavea INNER JOIN emp_employee_master AS emp ON emp.employee_master_id_pk = leavea.emp_id_fk WHERE emp.`corporate_id_fk` IN {0}", Ids);

This approach allows to keep the query and inputs separated.

3) Passing inputs as an XML

Create an XElement from the Ids. Using same approach pass XElement instead. And then have a look at the below link for examples of how to use the XML in SQL.

https://msdn.microsoft.com/en-IN/library/ms187897.aspx

Send parameters for IN operator in sql using parameterized queries in c#

2 Answers2