System.NotSupportedException: Commands with multiple queries cannot have out parameters

Question

I ran into another issue with using a data reader around a sproc with multiple ref cursors coming out. I am getting a not supported exception. Unfortunately, i can see from where it is coming from the source code of npgsql however.. i am not sure if i agree with throwing that exception. The code we have written works with oracle (both fully managed and managed flavors), sql server. Any help appreciated to keep it consistent for an api across some of those key flavors of dbms out there.

sproc body

CREATE OR REPLACE FUNCTION public.getmultipleresultsets (
    v_organizationid integer)
    RETURNS Setof refcursor
    LANGUAGE 'plpgsql'

AS $BODY$

declare       public override void AddCursorOutParameter(DbCommand command,
        string RefCursorName)
    { 
        NpgsqlParameter parameter = (NpgsqlParameter)CreateParameter(RefCursorName, false);
        parameter.NpgsqlDbType = NpgsqlDbType.Refcursor;
        parameter.NpgsqlValue = DBNull.Value;
        parameter.Direction = ParameterDirection.Output;

        command.Parameters.Add(parameter); 
    }
cv_1 refcursor;
cv_2 refcursor;

BEGIN 

    open cv_1 for
    SELECT a.errorCategoryId, a.name, a.bitFlag
    FROM ErrorCategories a
    ORDER BY name;
    RETURN next cv_1;

    open cv_2 for
    SELECT * 
    FROM StgNetworkStats  ;
    RETURN next cv_2;

END;

$BODY$;

Key Reader code that wraps postgres sql (Entlib implementation of npgsql)

private IDataReader DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
{
    try
    {

        var sql = new StringBuilder();

        using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            while (reader.Read())
            {
                sql.AppendLine($"FETCH ALL IN \"{ reader.GetString(0) }\";");
            }
        }

        command.CommandText = sql.ToString();
        command.CommandType = CommandType.Text; 

        IDataReader reader2 = command.ExecuteReader(cmdBehavior);
        return reader2;

    }
    catch (Exception)
    {
        throw;
    }
}

The command building code is shown below

 Helper.InitializeCommand(cmd, 300, "getmultipleresultsets");
    db.AddReturnValueParameter(cmd);

    db.AddInParameter(cmd, "organizationId", DbType.Int32, ORGANIZATIONID);

    db.AddCursorOutParameter(cmd, "CV_1");
    db.AddCursorOutParameter(cmd, "CV_2

The code that adds the refcursor parameter goes something like this

Answer 1

You code above seems to garble the PostgreSQL function with the .NET client code attempting to read its result.

Regardless, your function is declared to return a set of refcursors - this is not the same as two output parameters; you seem to be confusing the name of the cursor (cursors have names, but not ints, for example) with the name of the parameter (int parameters do have names).

Please note that PostgreSQL does not actually have output parameters - a function always returns a single table, and that's it. PostgreSQL does have a function syntax with output parameters, but that is only a way to construct the schema of the output table. This is unlike SQL Server, which apparently can return both a table and a set of named output parameters. To facilitate portability, when reading results, if Npgsql sees any NpgsqlParameter with direction out, it will attempt to find a resultset with the name of the parameter and will simply populate the NpgsqlParameter's Value with the first row's value for that column. This practice has zero added value over simply reading the resultset yourself - it's just there for compatibility.

To sum it up, I'd suggest you read the refcursors with your reader and then fetch their results as appropriate.