让我们假设我有一个有效的需要直接执行实体框架中的sql命令。我很难弄清楚如何在我的sql语句中使用参数。下面的例子(不是我的真实例子)不起作用。
var firstName = "John";
var id = 12;
var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
ExecuteSqlCommand方法不允许像ADO那样传入命名参数。Net和此方法的文档没有给出如何执行参数化查询的任何示例。
如何正确地指定参数?
当前回答
试试这个(编辑过的):
ctx.Database.ExecuteSqlCommand(sql, new SqlParameter("FirstName", firstName),
new SqlParameter("Id", id));
之前的想法是错误的。
其他回答
Oracle的简化版本。如果您不想创建OracleParameter
var sql = "Update [User] SET FirstName = :p0 WHERE Id = :p1";
context.Database.ExecuteSqlCommand(sql, firstName, id);
存储过程可以像下面这样执行
string cmd = Constants.StoredProcs.usp_AddRoles.ToString() + " @userId, @roleIdList";
int result = db.Database
.ExecuteSqlCommand
(
cmd,
new SqlParameter("@userId", userId),
new SqlParameter("@roleIdList", roleId)
);
对于。net Core 2.2，你可以使用FormattableString进行动态SQL。
//Assuming this is your dynamic value and this not coming from user input
var tableName = "LogTable";
// let's say target date is coming from user input
var targetDate = DateTime.Now.Date.AddDays(-30);
var param = new SqlParameter("@targetDate", targetDate);
var sql = string.Format("Delete From {0} Where CreatedDate < @targetDate", tableName);
var froamttedSql = FormattableStringFactory.Create(sql, param);
_db.Database.ExecuteSqlCommand(froamttedSql);
事实证明这是可行的。
var firstName = "John";
var id = 12;
var sql = "Update [User] SET FirstName = {0} WHERE Id = {1}";
ctx.Database.ExecuteSqlCommand(sql, firstName, id);
试试这个:
var sql = @"Update [User] SET FirstName = @FirstName WHERE Id = @Id";
ctx.Database.ExecuteSqlCommand(
sql,
new SqlParameter("@FirstName", firstname),
new SqlParameter("@Id", id));
