How to pass an array to an OracleParameter for IN condition?

Question

Here is the way I try to execute an sql command:

List<int> idsToDelete = new List<int>() { 1, 2, 3 };

string sqlDel = @"DELETE FROM products WHERE product_id IN (:idsToDelete)";

using (OracleCommand cmdDel = new OracleCommand(sqlDel, connexion))
{
    cmdDel.Parameters.Add("idsToDelete", idsToDelete.ToArray());
    cmdDel.ExecuteNonQuery();
}

I get the following exception:

Unable to cast an object form type 'System.Int32[]' to type 'System.IConvertible'.

I get the same exception with the following command:

string sqlDel = @"DELETE FROM products WHERE product_id IN :idsToDelete";

I ther a proper way to set a mutli value parameter for an IN condition? I don't want to format the command string, because it has to be reusable.

string sqlDel = $@"DELETE FROM products WHERE product_id IN ({string.Join(", ", idsToDelete)})"; or you have to create a parameter for each item in idsToDelete — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Oct 21, 2019 at 9:39

Dmitrii Bychenko · Accepted Answer · 2019-10-21 10:01:43Z

2

You can either join items into IN

List<int> idsToDelete = new List<int>() { 1, 2, 3 };

string sqlDel = 
  $@"DELETE 
       FROM products 
      WHERE product_id IN ({string.Join(", ", idsToDelete)})";

using (OracleCommand cmdDel = new OracleCommand(sqlDel, connexion))
{
    cmdDel.ExecuteNonQuery();
}

Or create parameter for each item within idsToDelete:

string sqlDel = 
  $@"DELETE 
       FROM products 
      WHERE product_id IN ({string.Join(", ", Enumerable.Range(0, idsToDelete).Select(i => $":prm_Del{i}"))})";

using (OracleCommand cmdDel = new OracleCommand(sqlDel, connexion))
{
    for (int i = 0; i < idsToDelete.Count; ++i) {
      //TODO: A better choice is explict parameter creation:
      //   cmdDel.Parameters.Add($":prm_Del{i}", idsToDelete[i], OracleType);  
      cmdDel.Parameters.AddWithValue($":prm_Del{i}", idsToDelete[i]);
    }

    cmdDel.ExecuteNonQuery();
}

edited Oct 21, 2019 at 10:01

answered Oct 21, 2019 at 9:44

Dmitrii Bychenko

187k20 gold badges178 silver badges231 bronze badges

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

Comments

josibu · Accepted Answer · 2019-10-21 09:39:47Z

0

Don't know whether this is best practice, but it should work:

List<int> idsToDelete = new List<int>() { 1, 2, 3 };

string sqlDel = @"DELETE FROM products WHERE product_id IN (";
sqlDel += string.Join(", ", idsToDelete);
sqlDel += ")";

using (OracleCommand cmdDel = new OracleCommand(sqlDel, connexion))
{
    cmdDel.Parameters.Add("idsToDelete", idsToDelete.ToArray());
    cmdDel.ExecuteNonQuery();
}

Let me know if it helped.

answered Oct 21, 2019 at 9:39

josibu

6681 gold badge8 silver badges27 bronze badges

1 Comment

Kino101 Feb 27 at 17:42

You are not using an OracleParameter as requested, and idsToDelete parameter is added but not used in the request so it is useless...

Jaydip Jadhav · Accepted Answer · 2019-10-21 09:40:55Z

0

Try String.Join()

cmdDel.Parameters.Add("idsToDelete", string.Join(",", idsToDelete));

answered Oct 21, 2019 at 9:40

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to pass an array to an OracleParameter for IN condition?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related