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
.
REFCURSOR
sCREATE 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$;
REFCURSOR
s 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 REFCURSOR
s 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 REFCURSOR
s? Is there a way to read the results without manually dereferencing the cursors?
I've got a vanilla example that returns multiple REFCURSOR
s 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.
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?
Coming from an sql server background where its just a question of a list of select statements in your stored proc and your "good to go"; using postgresql and the requirement to use refcursors and "fetch " those refcusors on the other side can be quite painful.
What i can suggest is:
1.) Use a postgresql Procedure with refcursors as INOUT parameters.
CREATE OR REPLACE PROCEDURE public.proc_testmultiplerefcursor(INOUT ref1 refcursor, INOUT ref2 refcursor)
2.) Call the procedure and then fetch the refcursors for the returned data using "FETCH ALL".
Fill the INOUT parameters with names for refcursors so they are recoverable in this case i have used 'ref1' & 'ref2'.
var sql = "BEGIN;CALL public.proc_testmultiplerefcursor(@pentity_id,'ref1','ref2');" +
"FETCH ALL FROM ref1; " +
"FETCH ALL FROM ref2;" +
"COMMIT;";
3.) Then your usual Dapper QueryMutliple and Reads.
var multi = await conn.QueryMultipleAsync(sql);
var result1 = (await multi.ReadAsync<Character>()).AsList();
var result2 =(await multi.ReadAsync<Planet>()).AsList();
This is untested but i hope it can be of help. Postgresql is painfull but brilliant.