What Is the Correct Syntax for Using Database.Executesqlcommand with Parameters

What is the correct syntax for using Database.ExecuteSqlCommand with parameters on mysql?

MySQL Syntax of calling Stored Procedure from command is "CALL ". Also the parameters names should be having "@" in front.

So you need to change the code as as following.

var affectedRows = 
  context.Database.ExecuteSqlCommand("CALL UpdateStatus (@p_customerId, @p_id)",
        new MySqlParameter("@p_customerId", "006"),
        new MySqlParameter("@p_id", 9));

What is the correct syntax for using Database.ExecuteSqlCommand with parameters?

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.

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

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);

What is wrong with my attempt to use EntityFramework to execute a sql statement

The table name can't be parameterised.

To avoid injection the best you can do is emulate the TSQL QUOTENAME function. Ensure the table name is no longer than 128 characters. Wrap it in [] and replace any embedded ] with ]].

Executing a DROP TABLE based on arbitrary user input sounds highly unusual/problematic in itself though.

ExecuteSqlCommand with output parameter

I ended up using this to get it working, but I'm sure there's a more optimal way:

var p = new SqlParameter
{
    ParameterName = "token",
    DbType = System.Data.DbType.String,
    Size = 100,
    Direction = System.Data.ParameterDirection.Output
};
var resp = this.Database.SqlQuery<String>("exec dbo.usp_GetRequestToken @token", p);

return resp.First();

How to use DbContext.Database.SqlQueryTElement(sql, params) with stored procedure? EF Code First CTP5

You should supply the SqlParameter instances in the following way:

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

You are missing the parameters in the SQL string you are executing. Try creating your parameters with an "@" preceding the name and then changing the ExecuteSqlCommand call to:

context.Database.ExecuteSqlCommand("exec MySchema.MyProc @customerId, @indicatorTypeId, @indicators, @startDate, @endDate", customerIdParam, typeIdParam, tableParameter, startDateParam, endDateParam);

Are EF Core 3.1 ExecuteSqlRaw / ExecuteSqlRawAsync drop-in replacements for ExecuteSqlCommand / ExecuteSqlCommandAsync?

I didn't see where the docs ever define the contents of the userSuppliedSearchTerm variable or its type, and I'm struggling to see how it could be sensibly a nugget of SQL (string userSuppliedSearchTerm = "WHERE id = 123"; with baked in values? SQL Injection?) or a single primitive value (int userSuppliedSearchTerm = 123) so does it mean it's some kind of custom type that specifies the db column name and the value ? How does EFCore know which column from the table is to be queried? Does EFCore sort out the syntax error presented by the parentheses? Are the parentheses mandatory for EFCore to understand the query?

All nothing to do with EF Core

The docs have chosen to use a Table Valued Function dbo.SearchBlogs to demonstrate the use of raw SQL querying, so SELECT * FROM [dbo].[SearchBlogs]({0}) is legal SQL because SearchBlogs is a function, not a table/view - it just wasn't mentioned on the docs I linked

ExecuteSqlCommand with DbContext

You don't need much of a change, it is only a syntax issue:

ExecuteProcedure("exec prc_SitePartVrsn_CanLock @spvId, @editMode OUTPUT", parameter, output);

And you can either use [dbo].[prc_SitePartVrsn_CanLock] or prc_SitePartVrsn_CanLock

DbContext ExecuteSQLCommand with no parameters

In order to get a little SQL Injection Protection, you SHOULD use parameters here. You have one parameter. You should probably also be using a stored procedure, but that's another question.

I would code it like this...

var parms = new ParameterCollection();

parms.Add("signOffId", signOffID);

db.Database.ExecuteSqlCommand("DELETE FROM SignoffCommentAttachment 
WHERE SignoffCommentAttachment.SignoffCommentID 
IN (SELECT [SignoffCommentID] FROM [SignoffComments] 
WHERE SignoffID = @signOffID)", parms);

I didn't check my syntax there but that's the method I would use. Only I would use a stored procedure.

---

Related Topics