CODEWITHSUNDEEP
c#sqloracle-database
sinsedrix
sinsedrix

Reputation: 4795

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

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.

Upvotes: 1

Views: 2340

Answers (3)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186843

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();
}

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try String.Join() 

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

Upvotes: 0

josibu
josibu

Reputation: 678

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.

Upvotes: 0

Related Questions