How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?

206,572

Solution 1

Turns out that this works.

var firstName = "John";
var id = 12;
var sql = "Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);

Solution 2

Try this:

var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";

ctx.Database.ExecuteSqlCommand(
    sql,
    new SqlParameter("@FirstName", firstname),
    new SqlParameter("@Id", id));

Solution 3

You can either:

1) Pass raw arguments and use the {0} syntax. E.g:

DbContext.Database.SqlQuery("StoredProcedureName {0}", paramName);

2) Pass DbParameter subclass arguments and use @ParamName syntax.

DbContext.Database.SqlQuery("StoredProcedureName @ParamName", 
                                   new SqlParameter("@ParamName", paramValue);

If you use the first syntax, EF will actually wrap your arguments with DbParamater classes, assign them names, and replace {0} with the generated parameter name.

The first syntax if preferred because you don't need to use a factory or know what type of DbParamaters to create (SqlParameter, OracleParamter, etc.).

Solution 4

The other answers don't work when using Oracle. You need to use : instead of @.

var sql = "Update [User] SET FirstName = :FirstName WHERE Id = :Id";

context.Database.ExecuteSqlCommand(
   sql,
   new OracleParameter(":FirstName", firstName), 
   new OracleParameter(":Id", id));

Solution 5

Try this (edited):

ctx.Database.ExecuteSqlCommand(sql, new SqlParameter("FirstName", firstName), 
                                    new SqlParameter("Id", id));

Previous idea was wrong.

Share:
206,572

Related videos on Youtube

jessegavin
Author by

jessegavin

I am a Senior Software Developer at Dow Jones. Formerly vmware, Thomson Reuters.

Updated on January 22, 2022

Comments

  • jessegavin
    jessegavin almost 2 years

    Let's just suppose I have a valid need for directly executing a sql command in Entity Framework. I am having trouble figuring out how to use parameters in my sql statement. The following example (not my real example) doesn't work.

    var firstName = "John";
    var id = 12;
    var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
    ctx.Database.ExecuteSqlCommand(sql, firstName, id);
    

    The ExecuteSqlCommand method doesn't allow you to pass in named parameters like in ADO.Net and the documentation for this method doesn't give any examples on how to execute a parameterized query.

    How do I specify the parameters correctly?

  • jessegavin
    jessegavin over 12 years
    When I do that I get the following error: "No mapping exists from object type System.Data.Objects.ObjectParameter to a known managed provider native type."
  • Ladislav Mrnka
    Ladislav Mrnka over 12 years
    Sorry my mistake. Use DbParameter.
  • jrummell
    jrummell over 12 years
    DbParameter is abstract. You'll have to use SqlParameter or use a DbFactory to create a DbParameter.
  • Greg Biles
    Greg Biles about 12 years
    This will work, but this mechanism allows for SQL injection and also prevents the database from reusing an execution plan when the statement shows up again but with different values.
  • Josh Gallagher
    Josh Gallagher almost 12 years
    @GregB I do not think you are correct here. I have tested that it won't allow me to, for instance, terminate a string literal early. Moreover, I looked at the source code and found that it's using DbCommand.CreateParameter to wrap up any raw values into parameters. So no SQL injection, and a nice succinct method invocation.
  • Greg Biles
    Greg Biles almost 12 years
    @JoshGallagher Yes, you're right. I was thinking of a string.Format scenario putting this together.
  • Andrew Barber
    Andrew Barber about 11 years
    Any chance you could explain this code and why it is an answer to the question posed, so that those who come and find this later can understand it?
  • Neco
    Neco about 10 years
    Problem with {0} syntax that you loose readability - i personally do not really like it. Problem with passing SqlParameters that you need to specify concrete implementation of DbParameter (SqlParameter, OracleParameter etc.). The provided example allows you to avoid these issues.
  • Andrew Barber
    Andrew Barber about 10 years
    I guess that's a valid opinion, but you have not answered the question actually being asked here; How to pass parameters to ExecuteSqlCommand() You should be sure to answer the specific question being asked when you post answers.
  • Arnthor
    Arnthor over 9 years
    It does NOT work as of SQL Server 2008 R2. You'll have @p0, @p2, ..., @pN instead of parameters you passed. Use SqlParameter("@paramName", value) instead.
  • Makotosan
    Makotosan over 8 years
    Upvoted for mentioning that the {0} syntax is database agnostic. "... you dont[sic] need to use a factory or know what type of DbParamaters[sic] to create ..."
  • an phu
    an phu over 8 years
    Downvoted because it is unnecessarily complicated (e.g. uses reflection, re-inventing the wheel, fails to account for different database providers)
  • Zar Shardan
    Zar Shardan over 7 years
    Up-voted because it shows one of the possible solutions. I'm sure ExecuteSqlCommand accepts parameters the same way SqlQuery does.I also like Dapper.net style of passing the parameters.
  • Marek Malczewski
    Marek Malczewski almost 6 years
    In SQL Server I use @p0 instead of :p0.
  • sansy
    sansy over 5 years
    I am not able to pass more than 3 paramters. If I do, i get following error "Additional information: The parameterized query '(@param1 type1,@param2 type2,@param3' expects the parameter '@param4', which was not supplied." Any suggestion?
  • ANeves
    ANeves over 5 years
    This is not db-agnostic, it will only work for MS-SQL Server. It will fail for Oracle or PG.
  • georgiaboy82
    georgiaboy82 about 5 years
    So ExecuteSqlCommand is just a glorified String.Format call that also executes the sql command it formats.
  • Joshua Kemmerer
    Joshua Kemmerer almost 5 years
    I love .NET Core 2.0 so much it gives me tears of joy :')
  • Paul Carlton
    Paul Carlton over 4 years
    I can see some people's enthusiasm since .NET Core 2.0 has been a smoother ride for me so far.
  • CodeNaked
    CodeNaked about 4 years
    How is the first one not vulnerable to SQL injection? Both use C# string interpolation. The first one wouldn't be able to preempt the string expansion, as far as I know. I suspect you meant that to be ctx.Database.ExecuteSqlCommand("Update [User] SET FirstName = {firstName} WHERE Id = {id}", firstName, id);
  • Greg Gum
    Greg Gum about 4 years
    @CodeNaked, No, I did not mean that. EF is aware of the issue, and creates two actual parameters to protect you. So it is not just a string that is passed through. See link above for details. If you try it in VS, my version will not issue a warning about Sql Injection, and the other one will.
  • CodeNaked
    CodeNaked about 4 years
    @GregGum - TIL about FormattableString. You're right and that's pretty cool!
  • FlyingV
    FlyingV about 4 years
    Don't forget to do using System.Data.SqlClient
  • FlyingV
    FlyingV about 4 years
    Don't forget to do using System.Data.SqlClient
  • Daniel
    Daniel about 4 years
    @GregBiles and others: This overload is definitely NOT using string formatting, even though it looks like it. If you look at the sql in profiler, it is parameterized. .NET is doing the right thing in this case and changing {0} to `@p0, then parameterizing. It's a trade-off. It does look like evil string.format, but it is more concise (and db agnostic, see other answer below) than having to do new SqlParameter("@foo",paramvalue)
  • ScottB
    ScottB almost 4 years
    Scenario 1 is deprecated in favour of an interpolated version. Equivalent is now: DbContext.Database.ExecuteSqlInterpolated($"StoredProcedureN‌​ame {paramName}");
  • Chris Bordeman
    Chris Bordeman over 3 years
    Thank god no one uses Oracle. Well not voluntarily! EDIT: Apologies for the late joke! EDIT: Apologies for the bad joke!
  • Flatlineato
    Flatlineato almost 2 years
    In the first example I think a parenthesis is missing
  • Greg Gum
    Greg Gum almost 2 years
    @Flatlineato, Parenthesis added.

Related