Devart Forums

Code: Select all

select function:

 CREATE OR REPLACE FUNCTION getdept()
  RETURNS SETOF dept AS
  'DECLARE rc dept%ROWTYPE; 
  BEGIN FOR rc IN SELECT * FROM public.dept  ORDER BY deptno LOOP   
      RETURN NEXT rc;     
  END LOOP;   RETURN;  END; '
  LANGUAGE 'plpgsql' VOLATILE;

Code: Select all

Insert function:

 CREATE OR REPLACE FUNCTION insert_dept(int4, "varchar", "varchar")
  RETURNS int4 AS
  'DECLARE
  num ALIAS FOR $1;
  name ALIAS FOR $2;
  location ALIAS FOR $3;
  BEGIN
   INSERT INTO  public.dept  VALUES (num ,name, location);
   return 1;
  END;'
  LANGUAGE 'plpgsql' VOLATILE;

Code: Select all

C# code:
  PgSqlDataAdapter dataAdapter = new PgSqlDataAdapter();
  dataAdapter.SelectCommand = pgSqlConnection.CreateCommand();
  dataAdapter.SelectCommand.CommandText = "getdept";
  dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
  dataAdapter.InsertCommand = pgSqlConnection.CreateCommand();
  dataAdapter.InsertCommand.CommandText = "insert_dept";
  dataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;
  PgSqlCommandBuilder.DeriveParameters(dataAdapter.InsertCommand);