I am trying to understand how to use Dapper to make a call to a PostgreSQL function that returns multiple result sets. My understanding is that in PostgreSQL, the best (only?) way to currently achieve this is to declare that the function RETURNS SETOF REFCURSOR.
Example PostgreSQL Function that Returns Multiple REFCURSORs
CREATE OR REPLACE FUNCTION public.testmultiplerefcursorfunc()
RETURNS SETOF REFCURSOR
LANGUAGE 'plpgsql'
STABLE
AS $BODY$
DECLARE
ref1 REFCURSOR;
ref2 REFCURSOR;
BEGIN
OPEN ref1 FOR
SELECT *
FROM characters;
RETURN NEXT ref1;
OPEN ref2 FOR
SELECT *
FROM planets;
RETURN NEXT ref2;
END;
$BODY$;
Broken Dapper+PostgreSQL with Multiple REFCURSORs Example
[Test]
public void UsingDapper_QueryMultiple_CallFunctionThatReturnsMultipleRefCursors_ReadsMultipleResultSetsViaMultipleRefCursors()
{
// Arrange
using (var conn = new NpgsqlConnection(_getConnectionStringToDatabase()))
{
var funcName = "testmultiplerefcursorfunc";
var expect1 = CharacterTestData;
var expect2 = PlanetTestData;
conn.Open();
using (var transaction = conn.BeginTransaction())
{
// Act
using (var results = conn.QueryMultiple(
funcName,
commandType: CommandType.StoredProcedure,
transaction: transaction))
{
var result1 = results.Read<Character>().AsList();
var result2 = results.Read<Planet>().AsList();
// Assert
CollectionAssert.AreEquivalent(expect1, result1);
CollectionAssert.AreEquivalent(expect2, result2);
}
}
}
}
The problem that I'm having with the code above is that when I make the first results.Read<T>() call, it attempts to return both REFCURSORs cast as T. This cast then results in a T with null values for all of the properties. Then the next call to results.Read<T>() throws the following exception:
System.ObjectDisposedException: 'The reader has been disposed; this can happen after all data has been consumed
Object name: 'Dapper.SqlMapper+GridReader'.'
So, how does Dapper work with multiple PostgreSQL REFCURSORs? Is there a way to read the results without manually dereferencing the cursors?
I've got a vanilla example that returns multiple REFCURSORs without using Dapper that works where I manually dereference the cursors and read the results and I've also got examples that work against a SQL Server stored procedure that return multiple results.
I haven't (yet) found any particular documentation that points to a specific difference of how QueryMultiple should be called for PostgreSQL vs SQL Server, but such documentation would be greatly appreciated.
Even when calling a PostgreSQL function that returns single REFCURSOR using Dapper, I've found it necessary to manually handle the cursor dereferencing like the example below.
But from what I've read so far, this doesn't seem like it's supposed to be necessary, although I've had trouble finding specific documentation/examples for Dapper+PostgreSQL that show how this should otherwise work.
Working Dapper+PostgreSQL with Single REFCURSOR Example
[Test]
public void UsingDapper_Query_CallFunctionThatReturnsRefCursor_ReadsRowsViaRefCursor()
{
// Arrange
using (var conn = new NpgsqlConnection(_getConnectionStringToDatabase()))
{
var procName = "testrefcursorfunc";
var expect = CharacterTestData;
conn.Open();
using (var transaction = conn.BeginTransaction())
{
// Act
var cursorResult = (IDictionary<string, object>)conn
.Query<dynamic>(procName, commandType: CommandType.StoredProcedure, transaction: transaction)
.Single();
var cursorSql = $@"FETCH ALL FROM ""{(string)cursorResult[procName]}""";
var result = conn.Query<Character>(
cursorSql,
commandType: CommandType.Text,
transaction: transaction);
// Assert
CollectionAssert.AreEquivalent(expect, result);
}
}
}
So, with Dapper + PostgreSQL + REFCURSOR, is it always necessary to manually deference the cursor to read the results? Or can Dapper handle that for you?
