0

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.

Eb Heravi
  • 398
  • 5
  • 15
Triet Nguyen
  • 763
  • 9
  • 20

2 Answers2

3

You have this error because you have specified the type name for the parameter which is the composite type and not an array of composites. Therefore, you should specify udt_sp_parameter[] as the value of DataTypeName instead of udt_sp_parameter:

var cmdParam = command.CreateParameter();
cmdParam.ParameterName = "parameters";
cmdParam.Value = inputParameters;
cmdParam.DataTypeName = "udt_sp_parameter[]";

Since your type is registered and the driver already knows its PostgreSQL name, there is no need to specify it during the parameter setup. Feel free to remove the last line from the code above:

var cmdParam = command.CreateParameter();
cmdParam.ParameterName = "parameters";
cmdParam.Value = inputParameters;

The driver is smart enough to detect types automatically in most cases, but when there is an ambiguity then it should be specified. For example, you have a string which should be passed as JSON, or a CLR type should be serialized to JSON. In other cases just rely on the driver internals and allow him do his work.

Yoh Deadfall
  • 2,711
  • 7
  • 28
  • 32
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.

elopezp
  • 617
  • 7
  • 7