What is the correct syntax for using Database.ExecuteSqlCommand with parameters?
entity-framework sql sql-server
Question
I use Entity Framework 4.2 and want to call a stored procedure that has input parameters. I'm using Database.ExecuteSqlCommand to call the stored procedure.
However, the documentation is lacking in the correct syntax for the call in order to map the parameters correctly. My google-foo is failing me, and any help will be appreciated.
I.e. I have a procedure
procedure SetElementFrequency
@ElementTypeID integer,
@Frequency float
as ...
I've tried calling it with
Database.ExecuteSqlCommand("exec SetElementFrequency @p0 @p1",
elementType, frequency);
and
Database.ExecuteSqlCommand("exec SetElementFrequency {0} {1}",
elementType, frequency);
but they both fail with the error Incorrect syntax near '@p1'.
Accepted Answer
Depending on your underlying database provider, you can use either of the following.
Database.ExecuteSqlCommand(
"exec SetElementFrequency {0}, {1}",
elementType, frequency);
or
Database.ExecuteSqlCommand("exec SetElementFrequency ?, ?", elementType, frequency);
You may also specify elementType and frequency as DbParameter-based objects to provide your own names via the ParameterName property.
Popular Answer
var sql = @"Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
