Devart Forums

Code: Select all

CREATE TABLE t1
(
  c1 integer,
  c2 character varying(20)
)
WITH (
  OIDS=FALSE
);

Code: Select all

CREATE OR REPLACE FUNCTION getmultiple()
  RETURNS SETOF refcursor AS
$BODY$
  DECLARE 
  ref1 refcursor;
  ref2 refcursor;
BEGIN
	OPEN ref1 FOR
	SELECT * FROM T1;
	OPEN ref2 FOR
	SELECT * FROM T1;

	return next ref1;
	return next ref2;
	return;
END;
$BODY$
  LANGUAGE plpgsql

Code: Select all

string connectionString = @"Server=----; Port=----; Database=-----; User Id=------; Password=------; Unicode = true; Max Pool Size=200; Min Pool Size=20; Connection Lifetime=120; Connection Timeout=120; Default Command Timeout=120; Pooling=true";
            using (PgSqlConnection conn = new PgSqlConnection(connectionString))
            {
                conn.Open();
                using (PgSqlTransaction transaction = conn.BeginTransaction())
                {
                    using (PgSqlCommand command = new PgSqlCommand("getmultiple", conn, transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        using (IDataReader reader = command.ExecuteReader())
                        {
                            Dictionary result1 = new Dictionary();
                            Dictionary result2 = new Dictionary();

                            int idOrdinal = reader.GetOrdinal("c1");
                            int nameOrdinal = reader.GetOrdinal("c2");

                            while (reader.Read())
                            {
                                result1.Add(reader.GetInt32(idOrdinal), reader.GetString(nameOrdinal));
                            }

                            reader.NextResult();

                            while (reader.Read())
                            {
                                result2.Add(reader.GetInt32(idOrdinal), reader.GetString(nameOrdinal));
                            }

                            reader.Close();
                        }
                    }

                    transaction.Commit();
                }
                conn.Close();
            }