I'm trying to figure out how to fill the DataSet via DataAdapter for a refcursor returned by a plpgsql proc:
CREATE OR REPLACE FUNCTION test_reffunc()
RETURNS refcursor AS
$BODY$
DECLARE
v_refcursor refcursor;
BEGIN
OPEN v_refcursor FOR SELECT col1 FROM test;
RETURN v_refcursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test_reffunc() OWNER TO postgres;
I am using the follwing code snippet in C#:
However, It is returning the value the name of the cursor: in a single row. Is there a way to have the fetch all accomplished by the corelab component on this cursor ? I am using the enteprise library components.
how to fill the DataSet via DataAdapter for a refcursor returned by a plpgsql proc
-
- Posts: 2
- Joined: Fri 03 Aug 2007 07:24
-
- Posts: 2
- Joined: Fri 03 Aug 2007 07:24
Sorry I did not placed the code. Here is the code
private static DataTable GetDataTable(PgSqlConnection connection, CommandType commandType, string commandText, params PgSqlParameter[] commandParameters)
{
//create a command and prepare it for execution
PgSqlCommand cmd = new PgSqlCommand();
DataSet ds = new DataSet();
try
{
PgSqlTransaction trn;
trn = connection.BeginTransaction();
PrepareCommand(cmd, connection,(PgSqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
//cmd.Connection.BeginTransaction();
PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);
//PgSqlDataReader dr;
//dr = cmd.ExecuteReader();
//fill the DataSet using default values for DataTable names, etc.
da.AcceptChangesDuringFill = true;
da.Fill(ds);
trn.Commit();
// detach the PgSqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
//return the DataTable
return ds.Tables[0];
}
private static DataTable GetDataTable(PgSqlConnection connection, CommandType commandType, string commandText, params PgSqlParameter[] commandParameters)
{
//create a command and prepare it for execution
PgSqlCommand cmd = new PgSqlCommand();
DataSet ds = new DataSet();
try
{
PgSqlTransaction trn;
trn = connection.BeginTransaction();
PrepareCommand(cmd, connection,(PgSqlTransaction)null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
//cmd.Connection.BeginTransaction();
PgSqlDataAdapter da = new PgSqlDataAdapter(cmd);
//PgSqlDataReader dr;
//dr = cmd.ExecuteReader();
//fill the DataSet using default values for DataTable names, etc.
da.AcceptChangesDuringFill = true;
da.Fill(ds);
trn.Commit();
// detach the PgSqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
//return the DataTable
return ds.Tables[0];
}