Npgsql: Out parameters not supported with sequential reader (breaking NpgsqlDataAdapter)

[maximcus]

Hi!

I have a Postgre function which has 2 out integer parameters. I try to populate a DataTable with a call to instance method NpgsqlDataAdapter.Fill(DataTable dataTable). Before the call I set NpgsqlDataAdapter.SelectCommand to the appropriate command. This call succeeds when I use npgsql 3.2.7, but when I use npgsql 4.0.2 I get an exception:

System.NotSupportedException: Output parameters aren't supported with SequentialAccess
    in Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext()
--- --- ---
   in System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   in System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   in Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   in System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   in System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   in System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   in ...

The same problem exists with NpgsqlDataAdapter.Fill(DataSet dataSet)

I would like to know, is it a known breaking change, a bug, or maybe I am doing something I'm not supposed to.
If you need more info, I will provide a code sample.

Thanks!

[maximcus]

I found out that the same problem exists in npgsql 4.0.2 if I use NpgsqlDataAdapter.Fill(DataTable dataTable) with postgre function that has no out parameters but returns a table.

Please help!

[hanislovingit]

Ran into the same error msg today "Output parameters aren't supported with SequentialAccess" when trying to call a sproc with 3 output params. As soon as I downgraded Npgsql NuGet from v4.0.3 to v3.2.7, that error went away.

[roji]

Thanks for raising and sorry it took so long to react. The ability to have output parameters with sequential readers was removed in #1494, mainly because I wasn't aware that NpgsqlDataAdapter internally used it. I'll try to fix this for 4.0.4.

[kotovaleksandr]

Hi! I think this change break some behavior, i have code:

using (var command = new GlobalGetInfoCommand(Connection))
{
    using (var reader = command.Execute(CommandBehavior.SingleRow))
    {
        if (reader.Read())
        {
        //some logic
        }
    }
}

GlobalGetInfoCommand is wrapper around NpgsqlCommand for execution stored procedure with one row or 0 rows in result.
In version 4.0.3 and older (also in Microsoft SqlClient) code works fine: after start reader, Read() return true (recordset have one row) and reader done for read values from recordset.
In 4.0.4 behavior changed: reader allow read values from reader after creation, method Read() returns false and logic of code is broken.

Its valid change? Maybe exists workaround?

Thanks!

[roji]

@kotovaleksandr thanks for raising this, it's definitely not an intentional change and is likely some sort of breakage brought about by this hacky fix. Can you please open a new, separate issue? For now you can stay on 4.0.3, we'll try to fix this and release 4.0.5 soon.