Query parameters for an IN statement with array?

Query parameters for an IN statement with array?

Postby betawarz » Thu 23 May 2013 15:50

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
betawarz
 
Posts: 10
Joined: Mon 06 Dec 2010 18:15

Re: Query parameters for an IN statement with array?

Postby Pinturiccio » Fri 24 May 2013 14:17

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;
Pinturiccio
Devart Team
 
Posts: 1986
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL