How do I read multiple results sets from a PostgreSQL function using Dapper?

Question

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?

Answer 1

I've ran into this problem lately, and to imitate the multiple result set behavior of SQL Server, I used a function that returns SETOF REFCURSOR. I made my own simple library to resolve this problem, so I can reuse it to my other projects. See my GitHub Repository

Assuming you have 5 tables in the database

• User
  • Id
  • Username
• Role
  • Id
  • Name
• Permission
  • Id
  • Name
• UserRole
  • UserId | FK: User.Id
  • RoleId | FK: Role.Id
• RolePermission
  • RoleId | FK: Role.Id
  • PermissionId | FK: Permission.Id

PostgreSQL (PL/PGSQL)

CREATE OR REPLACE FUNCTION "get_user_with_roles_and_permissions"("user__id" INTEGER)
RETURNS SETOF REFCURSOR AS
$BODY$
DECLARE
    -- Refcursor declarations
    "ref__user" REFCURSOR;
    "ref__roles" REFCURSOR;
    "ref__permissions" REFCURSOR;
BEGIN
    -- Select User
    -- NOTE: this only query for exactly 1 row
    OPEN "ref__user" FOR
    SELECT "User"."Id", "User"."Username"
    FROM "User"
    WHERE "User"."Id" = "user__id"
    LIMIT 1;
    RETURN NEXT "ref__user";

    -- Select Roles
    OPEN "ref__roles" FOR
    SELECT "Role"."Id", "Role"."Name"
    FROM "Role"
    INNER JOIN "UserRole" ON "Role"."Id" = "UserRole"."RoleId"
    WHERE "UserRole"."UserId" = "user__id";
    RETURN NEXT "ref__roles";

    -- Select Permissions
    -- NOTE: There's a chance that user has many roles which have same permission, we use DISTINCT to eliminate duplicates
    OPEN "ref__permissions" FOR
    SELECT DISTINCT "Permission"."Id", "Permission"."Name"
    FROM "Permission"
    INNER JOIN "RolePermission" ON "Permission"."Id" = "RolePermission"."PermissionId"
    INNER JOIN "UserRole" ON "RolePermission"."RoleId" = "UserRole"."RoleId"
    WHERE "UserRole"."UserId" = "user__id";
    RETURN NEXT "ref__permissions";
END;
$BODY$

C#

/* Entity models */
record User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public IEnumerable<Role> Roles { get; set; }
    public IEnumerable<Permission> Permissions { get; set; }
}

record Role
{
    public int Id { get; set; }
    public string Name { get; set; }
}

record Permission
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// Calling the database function in C# code

// Create an instance of NpgsqlConnection
using var connection = new NpgsqlConnection(connectionString);

// Try to open a database connection
connection.Open();

// Begin a database transaction
using var transaction = connection.BeginTransaction();

// Query refcursors
// Call a function with name 'get_user_with_roles_and_permissions' with parameter 'user_id' = 1
var refcursors = connection.QueryRefcursor("get_user_with_roles_and_permissions", transaction, new { user__id = 1 });

// we use ReadSingleOrDefault because we're sure that there is only one user that has an id of 1 (or none if the user with id = 1 doesn't exists)
var user = refcursors.ReadSingleOrDefault<User>();

// Check if user with id = 1 exists
if (user is not null)
{
    // Query for roles
    user.Roles = refcursors.Read<Role>();

    // Query for permissions
    user.Permissions = refcursors.Read<Permission>(); 
}

Answer 2

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.

Answer 3

You Can Use Like this. Sure It Will Work..!

public DataSet Manage_user_profiledata(string _prof_code)
        {
            string query = string.Format(@"select * from  Function_Name(@prof_code, @first_tbl, @second_tbl)");
            NpgsqlParameter[] sqlParameters = new NpgsqlParameter[3];
            sqlParameters[0] = new NpgsqlParameter("@prof_code", NpgsqlDbType.Varchar);
            sqlParameters[0].Value = Convert.ToString(_prof_code);

            //
            sqlParameters[1] = new NpgsqlParameter("@first_tbl", NpgsqlTypes.NpgsqlDbType.Refcursor);
            sqlParameters[1].Value = Convert.ToString("Avilable");
            sqlParameters[1].Direction = ParameterDirection.InputOutput;
            sqlParameters[1].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
            //
            sqlParameters[2] = new NpgsqlParameter("@second_tbl", NpgsqlTypes.NpgsqlDbType.Refcursor);
            sqlParameters[2].Value = Convert.ToString("Assigned");
            sqlParameters[2].Direction = ParameterDirection.InputOutput;
            sqlParameters[2].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
            return conn.executeMultipleSelectQuery(query, sqlParameters);

        }





public DataSet executeMultipleSelectQuery(string _query, NpgsqlParameter[] sqlParameter)
        {
            // NgpSql Init //
            npg_connection = new NpgsqlConnection(connstr);
            npg_command = new NpgsqlCommand(_query, npg_connection);
            // NgpSql Init //
            i = 0;
            try
            {
                ds = new DataSet();
                npg_connection.Open();
                NpgsqlTransaction tran = npg_connection.BeginTransaction();
                npg_command.CommandType = CommandType.Text;
                npg_command.Parameters.AddRange(sqlParameter);
                npg_command.ExecuteNonQuery();
                foreach (NpgsqlParameter parm in sqlParameter)
                {
                    if (parm.NpgsqlDbType == NpgsqlTypes.NpgsqlDbType.Refcursor)
                    {
                        if (parm.Value.ToString() != "null" || parm.Value.ToString() != "NULL" || parm.Value.ToString() != "")
                        {
                            string parm_val = string.Format("FETCH ALL IN \"{0}\"", parm.Value.ToString());
                            npg_adapter = new NpgsqlDataAdapter(parm_val.Trim().ToString(), npg_connection);
                            ds.Tables.Add(parm.Value.ToString());
                            npg_adapter.Fill(ds.Tables[i]);
                            i++;
                        }
                    }
                }
                tran.Commit();
                return ds;
            }
            catch (Exception ex)
            {
                ds_ERROR.Tables[0].Rows.Add(ex.ToString(), ex.Message.ToString());
                return ds_ERROR;
            }
            finally
            {
                npg_connection.Close();
            }
        }

Answer 4

Try with conn.QueryMultipleAsync In Current source you are using conn.QueryMultiple. Here is the complete guide for it . Dapper Multiple