CODEWITHSUNDEEP
c#.netpostgresql
Nick Farsi
Nick Farsi

Reputation: 427

Pass dapper class to postgres stored procedure

I have a stored procedure:

CREATE OR REPLACE PROCEDURE insert_participant(j json)
LANGUAGE plpgsql    
AS $$
BEGIN
    INSERT INTO participant (procedure_id,lot_id)SELECT procedure_id,lot_id  FROM json_populate_record(null::participant, j);
    COMMIT;
END;
$$;

And a class:

    public class Participant : BaseEntity
    {
        [Key]
        public int Procedure_id{ get; set; }
        public int Lot_id { get; set; }      
    }

I'm trying the following:

        public void Add(Participant item)
        {
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                dbConnection.Execute("insert_participant", item, commandType: CommandType.StoredProcedure);
            }
        }

But it fails and tells that there is no stored procedure with these paremeters.

What kind of parameter should I define when creating stored procedure in order for this to work?

Upvotes: 1

Views: 472

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062502

Dapper is expecting to send parameters which match to the declared properties on the type - so in this case it is going to want to send Lot_id and Procedure_id. It looks like you want to send the entire object as JSON instead, in which case you probably want:

var json = YourChoiceOfJsonSerializeAPI(item);
// possibly: var json = JsonConvert.SerializeObject(item);
dbConnection.Execute("insert_participant", new { json },
    commandType: CommandType.StoredProcedure);

This now has one member called json, so one parameter named json will be added.

Upvotes: 2

Related Questions