Query parameters for an IN statement with array?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
betawarz
Posts: 10
Joined: Mon 06 Dec 2010 18:15

Query parameters for an IN statement with array?

Post by 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

Pinturiccio
Devart Team
Posts: 2125
Joined: Wed 02 Nov 2011 09:44

Re: Query parameters for an IN statement with array?

Post by 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;

Post Reply