Npgsql C# - pass parameters as array of composite type into stored procedure

Question

Maybe this topic is duplicated from this Array of composite type as stored procedure input passed by C# Npgsql. But that is old one from 2017 and some APIs, properties are deprecated.

Currently, I am trying to pass an array of composite types to the stored procedures. I do map a globally composite type. But there an exception was thrown Can't write CLR type Web.API.Models.UdtSpParameter[] with handler type MappedCompositeHandler`1

I try to google that seems to not find any result to resolve that. The following below that what I create, mapping, calling command stored procedure.

Postgresql

/* Create composite type */
CREATE TYPE udt_sp_parameter AS (
    field VARCHAR(50),
    value VARCHAR
);


/* Create stored procedure */
CREATE OR REPLACE PROCEDURE stored_example(
    parameters udt_sp_parameter[])
LANGUAGE 'plpgsql'

AS $BODY$
DECLARE 
    _refCursor CURSOR FOR SELECT field, value FROM UNNEST(parameters::udt_sp_parameter[]);

    _record udt_sp_parameter;
BEGIN 
    OPEN _refCursor;

    LOOP
        FETCH _refCursor INTO _record;
        IF NOT FOUND THEN 
            EXIT;
        END IF;
  
        RAISE NOTICE 'Field: %', _record.field;
        RAISE NOTICE 'Value: %', _record.value IS NULL;
    END LOOP;

    CLOSE _refCursor;
END;
$BODY$;

And I try to call the stored by plpgsql language and work well.

DO
$$
DECLARE
parameters udtt_input_param[] := ARRAY[
     ROW('YED','Yeti')
    ,ROW('INTELLIGENT','NOOB')
    ,ROW('ZXC',NULL)
    ,ROW('CXX','1')];

BEGIN

CALL stored_example(parameters);
END
$$
LANGUAGE plpgsql

C# Npgsql (nuget Npgsql 4.1.4)

// mapping global type on Startup.cs
NpgsqlConnection.GlobalTypeMapper.MapComposite<UdtSpParameter>("udt_sp_parameter");

// class model UdtSpParameter
public class UdtSpParameter
{
    [PgName("field")]
    public string Field { get; set; }
    [PgName("value")]
    public string Value { get; set; }

    public UdtSpParameter() { }
}

// call stored procedure at data access layer for example StudentDAL.cs
public IEnumerable<T> CallStoredResultSet<T>(UdtSpParameter[] inputParameters ) where T : class
{
    var conn = _GetOpenConnection();
    var tran = _BeginTransaction(conn);

    NpgsqlCommand command = new NpgsqlCommand("stored_example", conn);
    command.CommandType = CommandType.StoredProcedure;
    var cmdParam = command.CreateParameter();
    cmdParam.ParameterName = "parameters";
    cmdParam.DbType = DbType.Object;  
    cmdParam.Value = inputParameters;
    cmdParam.DataTypeName = "udt_sp_parameter";

    command.Parameters.Add(cmdParam);

    // throw exception here
    // Can't write CLR type Web.API.Models.UdtSpParameter[] with handler type MappedCompositeHandler`1
    NpgsqlDataReader dr = command.ExecuteReader(); 


    var result = new List<T>();

    while (dr.Read())
    {
        Console.WriteLine(dr[0].ToString(), dr[1].ToString());
    }


    _CommitTransaction(tran);
    _CloseConnection(conn);

    return result;
}

Please find some stuff if I do anything wrong and point me to fix that. Thanks in advance.

Answer 1

The official documentation of Npgsql says:

The only way to call a stored procedure is to write your own CALL my_proc(...) command, without setting CommandBehavior.StoredProcedure.

In your particular case you should modify your code like this:

NpgsqlCommand command = new NpgsqlCommand("call stored_example(:parameters)", conn);
// comment this line command.CommandType = CommandType.StoredProcedure;

Hope it helps bro.