How to use db function with parameters?

How to use db function with parameters?

Postby mr-owl » 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
mr-owl
 
Posts: 7
Joined: Thu 14 Sep 2006 17:09

Postby Alexey » 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby mr-owl » 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
mr-owl
 
Posts: 7
Joined: Thu 14 Sep 2006 17:09

Postby Alexey » 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();
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL