retrieving array columns

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
dconn
Posts: 3
Joined: Thu 15 Jan 2015 15:04

retrieving array columns

Post by dconn » Fri 10 Apr 2015 14:40

Hello -

When I select from a Pg table that contains a string array column, that column appears in my populated dataset as a single string, not a string array. Am I doing something wrong, or is this how dotConnect handles array retrieves ?

Here's a snippet:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand(sql, _con );
cmd.CommandType = CommandType.Text;
PgSqlDataSet ds = new PgSqlDataSet();

try
{
  PgSqlDataReader rdr = cmd.ExecuteReader();
  ds.Load(rdr, LoadOption.OverwriteChanges, "Data");
}
catch (Exception ex)
{
  throw new Exception("Error loading PostgreSQL data", ex);
}

return (ds);

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

Re: retrieving array columns

Post by Pinturiccio » Tue 14 Apr 2015 15:45

Yes, with your code, values of a column, storing arrays of strings are returned as String. DataReader returns scalar types. However dotConnect for PostgreSQL supports special types for working with complex data types. For this you can use PgSqlDataAdapter and its ReturnProviderSpecificTypes property. For example, the following code works with the same table; however, it returns PgSqlArray instead of String:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand(sql, _con );
cmd.CommandType = CommandType.Text;
PgSqlDataSet ds = new PgSqlDataSet();

try
{
  PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);
  da.ReturnProviderSpecificTypes = true;
  da.Fill(ds, "Data");
}

catch (Exception ex)
{
  throw new Exception("Error loading PostgreSQL data", ex);
}
PgSqlDataReader also has its specific methods for returning PostgreSQL-specific types, for example, GetPgSqlArray. For more information, please refer to http://www.devart.com/dotconnect/postgr ... mbers.html

dconn
Posts: 3
Joined: Thu 15 Jan 2015 15:04

Re: retrieving array columns

Post by dconn » Thu 16 Apr 2015 18:42

I see. Thank you very much.

Post Reply