PgSqlDataAdapter can't retrieve more than 1 table
Posted: Tue 28 Dec 2010 07:32
I've created a function in PostgreSQL that should return 2 set of tables but when I retrieved this results using PgSqlDataAdapter and populate it into a PgSqlDataSet it only returns 1 table..Here are my codes..I'll appreciate any help, I'm figuring out this problem for almost 1 week.
But by examining the variable ds which is a PgSqlDataSet Type, it only contains the table customer which is the first executed statement in my refcursorfunc1() function.
Why did the adpt variable which is a PgSqlDataAdapter Type behaved like this? The adpt variable populates the ds variable with only 1 TABLE. Am I missing something here?Im solving this problem for almost 1 week.Please help
Code: Select all
CREATE OR REPLACE FUNCTION refcursorfunc1()
RETURNS SETOF REFCURSOR AS
$$
DECLARE
v_refcursor refcursor;
v_refcursor2 refcursor;
BEGIN
OPEN v_refcursor FOR
SELECT * FROM customer;
RETURN NEXT v_refcursor;
OPEN v_refcursor2 FOR
SELECT * FROM orders;
RETURN NEXT v_refcursor2;
RETURN;
END;
$$ LANGUAGE plpgsql;
And I retrieve those data in C# using these codes:
Code: Select all
PgSqlConnection myConnection = new PgSqlConnection();
PgSqlConnectionStringBuilder pgCSB = new PgSqlConnectionStringBuilder();
pgCSB.Host = "localhost";
pgCSB.Port = 5432;
pgCSB.UserId = "postgres";
pgCSB.Password = "somepassword";
pgCSB.MaxPoolSize = 150;
pgCSB.ConnectionTimeout = 30;
pgCSB.Database = "somedatabase";
PgSqlCommand cmd = new PgSqlCommand("refcursorfunc1");
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ParameterCheck = true;
myConnection.Open();
PgSqlTransaction t = myConnection.BeginTransaction();
PgSqlDataAdapter adpt = new PgSqlDataAdapter(cmd);
PgSqlDataSet ds = new PgSqlDataSet();
adpt.Fill(ds);
t.Commit();
Why did the adpt variable which is a PgSqlDataAdapter Type behaved like this? The adpt variable populates the ds variable with only 1 TABLE. Am I missing something here?Im solving this problem for almost 1 week.Please help