How to use db function with parameters?

[mr-owl]

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

[Alexey]

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.

[mr-owl]

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

[Alexey]

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();