Dapper dynamic parameters throw a SQLException "must define scalar variable" when not using anonymous objects

Question

(This code is using Dapper Dot Net in C#)

This code works:

var command = "UPDATE account SET priority_id = @Priority WHERE name = @Name";
connection_.Execute(command, new { Name = "myname", Priority = 10 } );

This code throws a SqlException:

class MyAccount 
{
    public string Name;
    public int Priority;
}

var command = "UPDATE account SET priority_id = @Priority WHERE name = @Name";
var acct = new MyAccount { Name = "helloworld", Priority = 10 };
connection_.Execute(command, acct);

System.Data.SqlClient.SqlException: Must declare the scalar variable "@Priority".

Why?

Obligee

Answer 1

Implement your model with properties, not fields:

class MyAccount 
{
    public string Name { get; set; }
    public int Priority { get; set; }
}

Dapper looks at the properties of the object to get parameters, ignoring fields. Anonymous types work because they are implemented with properties.

Diazole

Interesting; we consider fields for the materialization, so maybe it is a little inconsistent that we don't allow then (public ones, at least), for parameterisation. Meh... You're right though - trivial to solve by correct use of properties. – Thecla

@MarcGravell it does seem to violate the principle of least surprise. I'd vote to either change the behavior or document the requirement. – Obligee

Be sure to use "public" properties else you get the "must define scalar variable" error when you use "internal" or "private" properties. – Carrousel

Answer 2

Although this answer doesn't relate to the poster's issue, I had a similar problem with a different fix that I'll share here.

I was incorrectly defining the parameter list as a new []{ ... }:

var name = "myName";
var priority = 1;
var command = "UPDATE account SET priority_id = @Priority WHERE name = @Name";
connection_.Execute(command, new []{ priority, name });

Dropping the [] solved my issue:

connection_.Execute(command, new { priority, name });

Congreve

Answer 3

I also got a same problem with data type. When make a query with dynamic object that has a DateTime property, exception: The member CreatedDate of type System.Object cannot be used as a parameter value.

It worked when I used a POCO instead of dynamic later.

Broads