retrieving array columns

retrieving array columns

Postby 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);
dconn
 
Posts: 3
Joined: Thu 15 Jan 2015 15:04

Re: retrieving array columns

Postby 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/postgresql/docs/?Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlDataReader_members.html
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: retrieving array columns

Postby dconn » Thu 16 Apr 2015 18:42

I see. Thank you very much.
dconn
 
Posts: 3
Joined: Thu 15 Jan 2015 15:04


Return to dotConnect for PostgreSQL