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?

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.

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 });

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.