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
– Lukasz Szozda, Commented Mar 11, 2016 at 11:36

Patrick Hofman · Accepted Answer · 2016-03-11 11:29:33Z

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, 2016 at 11:29

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Prageeth Liyanage Over a year ago

parameter counter will be vary according to the login user.

Patrick Hofman Over a year ago

So you have to dynamically create SQL. That isn't that hard, is it?

Prageeth Liyanage Over a year ago

What about the programming part? mySqlCommand.Parameters.AddWithValue("@id1", id1);

Patrick Hofman Over a year ago

Yes, you have to split that too.

Patrick Hofman Over a year ago

What isn't working? Do you get an exception? What is the code used?

Community · Accepted Answer · 2017-05-23 12:15:51Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related