Dapper stored procedures with Postgresql without alphabetical parameters

Question

I am trying to call a Postgresql stored procedure written in plpgsql using C# and Dapper, but I have found that Dapper alphabetizes the parameters before inserting them into the stored procedure. Is there a way I can avoid this behavior and have the items inserted in the correct order?

For example, the following call would not run correctly because it would alphabetize the parameter names. I have to manually alphabetize them for the call to go through.

int map_id = conn.Query<int>(
"insert_color",
new
{
    zebra_name = new DbString { Value = slide_name },
    door_name = new DbString { Value = fov_name },
    foo_number = cycle_number,
    variable_path = new DbString { Value = image_path },
    random_path = new DbString { Value = meta_path },
    x = x,
    y = y,
    z = z,
    exposure = exposure,
    type_name = new DbString { Value = image_type },
    copy = copy
},
commandType: CommandType.StoredProcedure).First();

Here is the stored procedure declaration:

CREATE OR REPLACE FUNCTION insert_color(
zebra_name text, door_name text, foo_number integer,    
variable_path text, random_path text, 
x real, y real, z real, exposure real,
type_nametext, copy integer) RETURNS integer AS $$
    ...
$$ LANGUAGE plpgsql;

It means that instead of sending the parameters to the function in order (zebra name first, then door_name, etc) it sends them alphabetized (copy first, door_name, exposure... etc). I have to add "a_", "b_", "c_" in front of the parameter names for the function to run correctly. — CookieOfFortune
– CookieOfFortune, Commented Jul 31, 2012 at 20:05
Well, in that case, whatever "dapper" is, it's broken. Anything that broken I'd just stop using, but you could try reporting a bug. Of course if it can't call functions then it probably won't cope with arrays, record types, tsearch, postgis, ... — Richard Huxton
– Richard Huxton, Commented Jul 31, 2012 at 20:15
Honestly it hasn't really affected me too much since I can manually alphabetize, it's just annoying. — CookieOfFortune
– CookieOfFortune, Commented Jul 31, 2012 at 20:31

Master Morality · Accepted Answer · 2012-08-01 12:40:10Z

1

Internally, Dapper uses reflection to get the properties of the param object. Specifically, GetProperies(...) trouble is, those aren't guaranteed to be in a particular order...

The GetProperties method does not return properties in a particular order, such as alphabetical or declaration order. Your code must not depend on the order in which properties are returned, because that order varies.

In order be somewhat useful, they chose to order the parameters alphabetically, but unfortunately there is no way to ensure that the parameters appear in the order you have them in the class.

edited Aug 1, 2012 at 12:40

answered Jul 31, 2012 at 20:40

Master Morality

5,9686 gold badges33 silver badges43 bronze badges

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

2 Comments

kgrittn Over a year ago

That's not the syntax for named parameters in PostgreSQL, which is what the question is specifically about. I'm not sure how you might coerce Dapper to use the named parameter convention, but the syntax on the PostgreSQL side is described here: postgresql.org/docs/9.1/interactive/…

Master Morality Over a year ago

@kgrittn, you are correct. I did a quick search on google for the postgresql syntax, and found a page on calling the stored proc, I guess it wasn't postgresql after all. I've removed the code bit.

Collectives™ on Stack Overflow

Dapper stored procedures with Postgresql without alphabetical parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related