Dapper - must declare scalar variable

Question

Hi guys,

I am having trouble using Dapper. Getting must declare scalar variable @orderNo error.

How can I fix this?

private async Task GetDataForPdf(string orderNo)
 {
            var connString = _configuration["ConnectionStrings:Production"];
            await using var sqlConnection = new SqlConnection(connString);
            sqlConnection.Open();

            await using var command = new SqlCommand { Connection = sqlConnection };
            const string sql =
                @"select od.quantity, od.productCode, od.productName, od.price, od.quantity * od.price As Total, o.orderTotalPrice, o.orderDate, o.orderBy,o.email
                    from Orders o
                    inner join OrderDetail  od ON od.orderId = o.id
                    where od.status = 2 and o.orderNo = @orderNo";
            

            command.Parameters.Add(new SqlParameter("orderNo", orderNo));
            try
            {
                //Dapper
                var results = await sqlConnection
                    .QueryAsync<PdfDto>(sql);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
            

            //generate Pdf
            //SendEmailWithColumns(results);


        }

Thank you.

Answer 1

One of the benefits of Dapper is less code, here there is zero need for a SqlCommand.

Simple example where parameters is how to set the parameter for @id.

var sql = "SELECT id, Photo FROM dbo.Pictures1 WHERE dbo.Pictures1.id = @id";

using var cn = new SqlConnection(ConnectionString());
var parameters = new { id = identifier };
var container = cn.QueryFirstOrDefault<ImageContainer>(sql, parameters);

Answer 2

You assign the parameter "orderno" to the SqlCommand "command"; but you don't use the command anywhere, you work dirctly with SqlConnection and the SQL statement with the parameter name.