我有一个存储过程,有三个参数,我一直试图使用以下命令返回结果:

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

起初,我尝试使用SqlParameter对象作为参数,但这并不奏效,并抛出了一个SqlException与以下消息:

过程或函数“mySpName”需要未提供的参数“@param1”。

我的问题是如何将此方法用于期望参数的存储过程?

谢谢。


当前回答

我用的是EF 6。X是这样的:

using(var db = new ProFormDbContext())
            {
                var Action = 1; 
                var xNTID = "A239333";

                var userPlan = db.Database.SqlQuery<UserPlan>(
                "AD.usp_UserPlanInfo @Action, @NTID", //, @HPID",
                new SqlParameter("Action", Action),
                new SqlParameter("NTID", xNTID)).ToList();


            }

不要在sqlparameter上重复使用,有些人会在变量上这样做

var Action = new SqlParameter("@Action", 1);  // Don't do this, as it is set below already.

其他回答

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 });

/ /或

using(var context = new MyDataContext())
{
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();
}

/ /或

using(var context = new MyDataContext())
{
object[] parameters =  { param1, param2, param3 };

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
parameters).ToList();
}

/ /或

using(var context = new MyDataContext())
{  
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
param1, param2, param3).ToList();
}

您应该以以下方式提供SqlParameter实例:

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

当我使用SELECT语句调用一个接受两个输入参数并返回3个值的存储过程时,我有相同的错误消息,我在EF Code First方法中解决了如下问题

 SqlParameter @TableName = new SqlParameter()
        {
            ParameterName = "@TableName",
            DbType = DbType.String,
            Value = "Trans"
        };

SqlParameter @FieldName = new SqlParameter()
        {
            ParameterName = "@FieldName",
            DbType = DbType.String,
            Value = "HLTransNbr"
        };


object[] parameters = new object[] { @TableName, @FieldName };

List<Sample> x = this.Database.SqlQuery<Sample>("EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", parameters).ToList();


public class Sample
{
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public int NextNum { get; set; }
}

更新:它看起来像SQL SERVER 2005缺少的EXEC关键字正在创建问题。因此,为了允许它与所有SQL SERVER版本一起工作,我更新了我的答案,并在下面添加了EXEC

 List<Sample> x = this.Database.SqlQuery<Sample>(" EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", param).ToList();

同样,你可以使用"sql"参数作为格式说明符:

context.Database.SqlQuery<MyEntityType>("mySpName @param1 = {0}", param1)

我用的是EF 6。X是这样的:

using(var db = new ProFormDbContext())
            {
                var Action = 1; 
                var xNTID = "A239333";

                var userPlan = db.Database.SqlQuery<UserPlan>(
                "AD.usp_UserPlanInfo @Action, @NTID", //, @HPID",
                new SqlParameter("Action", Action),
                new SqlParameter("NTID", xNTID)).ToList();


            }

不要在sqlparameter上重复使用,有些人会在变量上这样做

var Action = new SqlParameter("@Action", 1);  // Don't do this, as it is set below already.