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

c# sqlexception dapper
sh-beta picture sh-beta · Nov 22, 2011 · Viewed 11.9k times · Source

(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

kevingessner picture kevingessner · Nov 22, 2011

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.

Related questions

How to catch a specific SqlException error?

Q: Is there a better way to handle SqlExceptions? The below examples rely on interpreting the text in the message. Eg1: I have an existing try catch to handle if a table does not exist. Ignore the fact that I …

Read More
how do I fix SqlException Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10

I was running an application for a few hours and then suddenly: SqlException was unhandled by user code: Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10. Viewing details of the exception shows it is a "Number" "845". …

Read More
C# try catch confusion

I'm very new to C#. When I try catch something like this: try { connection.Open(); command.ExecuteNonQuery(); } catch(SqlException ex) { MessageBox.Show("there was an issue!"); } How do I know if the problem happened with the Open or ExecuteNonQuery? What …

Read More