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?

score 40 · Accepted Answer · answered Nov 22 '11 at 16:53

40

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.

answered Nov 22 '11 at 16:53

kevingessner

18,559
5
43
63

1

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. – Marc Gravell Nov 22 '11 at 18:26
2

@MarcGravell it does seem to violate the principle of least surprise. I'd vote to either change the behavior or document the requirement. – sh-beta Nov 26 '11 at 23:16
Be sure to use "public" properties else you get the "must define scalar variable" error when you use "internal" or "private" properties. – neoscribe Jan 31 '20 at 22:36

score 2 · Answer 2 · answered Apr 15 '16 at 14:03

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

score 1 · Answer 3 · answered Mar 01 '12 at 17:36

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.

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

3 Answers3