Pass dapper class to postgres stored procedure

Question

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?

Marc Gravell · Accepted Answer · 2020-03-26 12:32:57Z

2

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.

answered Mar 26, 2020 at 12:32

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nick Farsi Over a year ago

so there is no way to avoid serialization?

juharr Over a year ago

@NickFarsi You can if you rewire the stored procedure to not use json.

Nick Farsi Over a year ago

but that's the gist of the original question! What should I use instead of json as a parameter when declaring the procedure?

Marc Gravell Over a year ago

@NickFarsi if you don't want to serialize, then have parameters for each property that you want to send; I'm not going to obsess about the syntax (I'm mostly SQL Server), but I'd expect (again, using SQL Server syntax) @id int, @name nvarchar(200), @dob datetime, ... etc; I can't tell you what you need in your case without knowing what BaseEntity has, plus... again, syntactical differences, but presumably something like @Procedure_id int, @Lot_id int ?

Collectives™ on Stack Overflow

Pass dapper class to postgres stored procedure

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related