Question
When using a parameterized query in Dapper, I'm having trouble. Other customers have reported having the same problems, but I have not been able to fix it.
The rule
public User GetUser(int employeeId)
{
var args = new
{
EmployeeId = employeeId
};
const string sql = @"
select
first_name 'FirstName',
last_name 'LastName'
from
users
where
employee_id = @EmployeeId
";
using (var con = MakeConnection())
{
var r = con.Query<User>(sql, args);
return r.FirstOrDefault();
}
}
The mistake
A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll
Additional information: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@EmployeeId".
I've also experimented withDynamicParameters
and passing it in its place, although that is also ineffective
var p = new DynamicParameters();
p.Add("@EmployeeId", employeeId); // I have also tried without the @
//...
var r = con.Query<User>(sql,p);
Accepted Answer
There are two concerns at play here (although you note this in your question)where a.acct = '@ZYX'
, according to SQL standards, seeks to match the literal text, which just so happens to contain an, without using any parameters.@
sign. The appropriate use for SQL-Server (see remark below) would bewhere a.acct = @ZYX
.
However! given that you utilizeOdbcConnection
with the designations not applicable. Use the pure ADO.NET clients instead of ODBC if you are genuinely connecting to something like SQL-Server since they offer greater functionality and performance. If ODBC is your only choice, however, it use no named parameters. This would have been a significant issue up until a few days ago, but according to using OleDb to pass query parameters in Dapper, the code (albeit not yet the NuGet package) now supports ODBC. You should be able to use: if you build from source (or wait for the next release):
...
where a.acct = ?
under your control, and:
var result = connection.Query(sqlString.ToString(),
new {
anythingYouLike = accountNumber
});
Observe that the name (anythingYouLike
ODBC does not utilize, therefore it may be... everything you want. For instance, in a more complicated scenario:
.Execute(sql, new { id = 123, name = "abc", when = DateTime.Now });
To ensure that the values are added to the command in the proper order, Dapper leverages some understanding of how anonymous types are implemented.id
, name
, when
).
One other thing to note:
Which means dapper is not replacing the parameter with it's given value.
Dapper never substitutes a parameter's value for the original value. Simply said, it is not the proper approach to parameterize sql. Typically, the arguments are given individually, ensuring:
- No danger of SQL injection exists.
- maximum reuse of query plans
- no formatting problems
Be aware that certain ADO.NET / ODBC suppliers use replacement to internally implement things; nevertheless, this is different from dapper.
Popular Answer
The scalar variable must be declared by Dapper. I arrived here from dublicate
Error: The scalar variable "@Name" must be declared.
I wrote the following piece of code to generate queries on the fly:
public static bool Insert<T>(T entity)
{
var tableName = entity.GetType().CustomAttributes.FirstOrDefault(x => x.AttributeType.Name == nameof(TableAttribute))?.ConstructorArguments?.FirstOrDefault().Value as string;
if (string.IsNullOrEmpty(tableName))
throw new Exception($"Cannot save {entity.GetType().Name}. Database models should have [Table(\"tablename\")] attribute.");
DBSchema.TryGetValue(tableName.ToLower(), out var fields);
using (var con = new SqlConnection(ConnectionString))
{
con.Open();
var sql = $"INSERT INTO [{tableName}] (";
foreach (var field in fields.Where(x => x != "id"))
{
sql += $"[{field}]"+",";
}
sql = sql.TrimEnd(',');
sql += ")";
sql += " VALUES (";
foreach (var field in fields.Where(x => x != "id"))
{
sql += "@"+field + ",";
}
sql = sql.TrimEnd(',');
sql += ")";
var affectedRows = con.Execute(sql, entity);
return affectedRows > 0;
}
}
And when my models looked like this, I received the same error:
[Table("Users")]
public class User
{
public string Name;
public string Age;
}
I altered them to read this:
[Table("Users")]
public class User
{
public string Name { get; set; }
public string Age { get; set; }
}
And it gave me a solution to the issue.