Why with Dapper I get a declare the scalar variable error?

c# dapper parameterized-query sql

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);
1
2
7/31/2015 9:34:21 PM

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.

16
5/23/2017 12:10:36 PM

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow