Devart Forums

Posted: **Wed 27 Sep 2006 09:18**

Hi,

I have big problem to use function with PostgreSQLDirect .NET driver, before I use Npgsql. I don't know how to use this driver? I use one parameter (integer) and I want to take one record from database.

Code: Select all

CREATE TYPE address_item AS
   (name text,
    line1 text,
    line2 text,
    city text,
    county text,
    postalcode text,
    country bpchar(2));

Code: Select all

CREATE OR REPLACE FUNCTION address_print(id int4) RETURNS address_item AS $BODY$
DECLARE
  rec record;
  item address_item%ROWTYPE;
BEGIN
  IF id < 0
  THEN
    RETURN NULL;
  ELSE
    SELECT INTO rec
      address_name AS name
    , address_line1 AS line1
    , address_line2 AS line2
    , address_city AS city
    , address_county AS county
    , address_postalcode AS postalcode
    , address_country AS country
    FROM
      address
    WHERE address_id=id
    ;

    IF NOT FOUND
    THEN
      RETURN NULL;
    ELSE
      item.name := rec.name;
      item.line1 := rec.line1;
      item.line2 := rec.line2;
      item.city := rec.city;
      item.county := rec.county;
      item.postalcode := rec.postalcode;
      item.country := rec.country;
      RETURN item;
    END IF;
  END IF;
END;
$BODY$ LANGUAGE 'plpgsql';

How to use PostgreSQLDirect .NET with this function?

mr-owl

Posted: **Wed 27 Sep 2006 09:58**

Using our StoredProc sample:
1. Change PgSqlConnection component.
2. Choose appropriate function from the list in PgSqlCommand component.
3. Type value for the parameter and press Execute button to check what data this function would return.

Posted: **Wed 27 Sep 2006 11:46**

Alexey wrote:Using our StoredProc sample:
1. Change PgSqlConnection component.
2. Choose appropriate function from the list in PgSqlCommand component.
3. Type value for the parameter and press Execute button to check what data this function would return.

Thx by I need example code... In Npgsql I use:

Code: Select all

NpgsqlCommand sql = new NpgsqlCommand(String.Empty, PostgreSQL);
sql.CommandType=CommandType.StoredProcedure;
sql.CommandText = "address_print(:id)";
sql.Parameters.Add("id", "NpgsqlDataType.Integer").Value=0;
NpgsqlDataReader DataReader = sql.ExecuteReader();
while(DataReader.Read())
{
  do something;
}
DataReader.Close();

How to do this same with PostgreSQLDirect .NET driver?

mr-owl

Posted: **Thu 28 Sep 2006 05:48**

Assuming PostgreSQL is PgSqlConnection instance which is already opened, the code would be like this:

Code: Select all

      PgSqlCommand sql = new PgSqlCommand(String.Empty, PostgreSQL);
      sql.CommandType = CommandType.StoredProcedure;
      sql.CommandText = "address_print";
      sql.Parameters.Add("id", PgSqlType.Int).Value = 0;
      PgSqlDataReader DataReader = sql.ExecuteReader();
      while (DataReader.Read())
      {
        //do something; 
      }
      DataReader.Close();

Devart Forums

How to use db function with parameters?

How to use db function with parameters?