The parameter must not be enclosed inside ', otherwise it is not detected as a parameter. Use this instead:
command.CommandText = @"select * from customers where customerName = @customerName";
I'm using EFCore in my project, and I'm attempting to execute a raw SQL query (because I'm targeting a database that I don't own and don't want to scaffold out the massive schema for it), passing in a parameter. Here is what I'm doing (although I've changed the query for protection):
var customerId = "testname";
using (var command = _dbContext.Database.GetDbConnection().CreateCommand())
{
command.CommandText = @"select * from customers where customerName = '@customerName'";
var customerIdParam = new SqlParameter("@customerName", customerId);
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@customerName";
parameter.Value = customerId;
parameter.DbType = System.Data.DbType.String;
parameter.Direction = System.Data.ParameterDirection.Input;
command.Parameters.Add(parameter);
await _dbContext.Database.OpenConnectionAsync();
using (var result = await command.ExecuteReaderAsync())
{
return result;
}
}
The problem is that the parameter doesn't seem to be passing in. If I hardcode the value for customerName that I'm sending in, it works just fine and returns rows, but if I pass it in as a parameter, result doesn't return any rows
The parameter must not be enclosed inside ', otherwise it is not detected as a parameter. Use this instead:
command.CommandText = @"select * from customers where customerName = @customerName";
Aha, that was it! Thanks so much. It's been probably 5 years since the last time I had to execute raw SQL in C# versus using the ORM to handle it