EF6是否使用SqlQuery / ExecuteSqlCommand调用存储的proc，而无需考虑参数顺序？ [英] EF6 Call stored procs using SqlQuery/ExecuteSqlCommand without regard for parameter order?

问题描述

我正在使用EF 6 SqlQuery / ExecuteSqlCommand调用存储过程，如下所示：

I am using EF 6 SqlQuery/ExecuteSqlCommand to call stored procedures like so:

var parameters = new List<SqlParameter>()
{
    new SqlParameter("@ID", model.ID),
    new SqlParameter("@Email", model.Email),
    new SqlParameter("@Phone", model.Phone)
};

using (var context = new MyContext())
{
    context.Database.ExecuteSqlCommand("sp_MySproc_U @ID, @Email, @Phone", parameters.ToArray());
}

这很好，直到我意识到我的电子邮件值已保存到我的电话领域，反之亦然。为什么？由于存储过程中参数的顺序为@ ID，@ Phone，@ Email，因此显然与我代码中的参数顺序不匹配。 SqlQuery / ExecuteSqlCommand语法中是否有一种简单的方法来专门命名参数并将其与值匹配。我的目标是调用存储的proc，而不必关心代码中与存储过程中的顺序匹配的参数的顺序。

This works great, until I realized that my Email values were being saved to my Phone field and vice versa. Why? Because the order of the parameters in my stored procedure is @ID, @Phone, @Email, which obviously doesn't match the order of the parameters in my code. Is there a simple way in the SqlQuery/ExecuteSqlCommand syntax to specifically name the parameters and match them with values. My goal is to call my stored procs without being concerned with the order of the parameters in code matching the order in the stored procedure.

这篇文章建议以下内容，

db.Database.SqlQuery<resultClass>("mySpName PageNumber=@PageNumber,Code=@Code,PageSize=@PageSize", parameters.ToArray()).ToList();

，但这会返回并显示无效字符‘=’错误。感谢您的帮助。

but this returns and "Invalid character '='" error. Thanks for your help.

推荐答案

根据 SQL Server存储过程调用语法，命名参数必须以符号@ 开头，即

According to the SQL Server stored procedure call syntax, the named parameters must be preceeded by at sign @, i.e

@parameter=value

或

@parameter=@variable

因此您可以使用

context.Database.ExecuteSqlCommand(
    "sp_MySproc_U @ID=@ID, @Email=@Email, @Phone\@Phone",
     parameters.ToArray());

或更笼统

context.Database.ExecuteSqlCommand(
    "sp_MySproc_U " + string.Join(", ", parameters.Select(p => p.Name + "=" + p.Name)), 
    parameters.ToArray());

这篇关于EF6是否使用SqlQuery / ExecuteSqlCommand调用存储的proc，而无需考虑参数顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！