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

c# dapper sqlexception

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?

Accepted Answer

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.


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why