What is the correct syntax for using Database.ExecuteSqlCommand with parameters?
Solution 1
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.
Solution 2
var sql = @"Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
Solution 3
Try something like this:
context.Database.ExecuteSqlCommand("delete MasterSmsCampaignCertificateInfo where MasterSmsCampaignGuid = @p0 and CertificateId = @p1",
TheCampaignGuid,
certInfo.CertificateId);
Take a look at this similar question: ExecuteSqlCommand with output parameter Best regards
SWeko
A seasoned .net developer, with insatiable curiosity and a habit on getting stuck in most impossible places (both on and off the IDE)
Updated on June 18, 2022Comments
-
SWeko almost 2 years
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'.
-
SWeko almost 12 yearsSo does this mean that the first parameter is set to
@p0
, the second to@p1
, and so on? -
Oscar almost 12 yearsHave you tried passing them as array? context.Database.ExecuteSqlCommand("delete MasterSmsCampaignCertificateInfo where MasterSmsCampaignGuid = @p0 and CertificateId = @p1", new object[]{ TheCampaignGuid, certInfo.CertificateId});
-
SWeko almost 12 yearsIt's a params parameter, so it should not matter, and yes, I've tried that too :)
-
SWeko almost 12 yearsThis syntax works fine, the problem was with my query format.
-
SWeko almost 12 yearsThis syntax works fine, the problem was in the generated query.
-
Ryan Kyle about 9 yearsThis is just a copy of another answer from a similar question, without adjusting for context. stackoverflow.com/a/5475210/642054