Query parameters for an IN statement with array?

[betawarz]

Hi all,

I'm familiar with adding query paramaters for single comparisons, like:

Code: Select all

cmd.Paramaters.Add("id", PgSqlType.Int).Value = 1;

But, how do you do this for an array of Ints, like with an IN query?

Thanks

[Pinturiccio]

As far as we understand, you are trying to execute the following query, right?

Code: Select all

select * from table where id in(:par)

If you are using the following code, then everything works fine.

Code: Select all

cmd.Paramaters.Add("id", PgSqlType.Int).Value = 1;

And you want to know how to pass an array of integers into a parameter, right?

PostgreSQL does not allow using array in the IN operator. This is a restriction of PostgreSQL, not of dotConnect for PostgreSQL.
However, the ANY operator allows accepting a parameter as an array. Thus the following code is possible:

Code: Select all

PgSqlCommand comm = new PgSqlCommand("select * from table where id ANY(:par)", conn);
object[] elements = new object[] { 1, 2, 3, 4, 7, 8 };
PgSqlArray arr = new PgSqlArray(elements, PgSqlType.Int, 1, elements.Count());
comm.Parameters.Add("par", PgSqlType.Array).Value = arr;