如何使用DbContext.Database。SqlQuery(sql, params)和存储过程?英孚代码CTP5

I have a stored procedure that has three parameters and I've been trying to use the following to return the results:

我有一个有三个参数的存储过程，我一直在尝试使用以下的方法来返回结果:

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

At first I tried using SqlParameter objects as the params but this didn't work and threw a SqlException with the following message:

起初，我尝试使用SqlParameter对象作为参数，但这没有用，并使用以下消息抛出了一个SqlException:

Procedure or function 'mySpName' expects parameter '@param1', which was not supplied.

过程或函数'mySpName'预期参数'@param1'，它没有提供。

So my question is how you can use this method with a stored procedure that expects parameters?

因此，我的问题是如何使用这个方法使用一个存储过程，它期望参数?

Thanks.

谢谢。

10 个解决方案

#1

You should supply the SqlParameter instances in the following way:

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

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

#2

Also, you can use the "sql" parameter as a format specifier:

另外，您可以使用“sql”参数作为格式说明符:

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

#3

This solution is (only) for SQL Server 2005

此解决方案仅适用于SQL Server 2005。

You guys are lifesavers, but as @Dan Mork said, you need to add EXEC to the mix. What was tripping me up was:

你们是lifesavers，但正如@Dan Mork说的，你需要添加EXEC。绊倒我的是:

• 'EXEC ' before the Proc Name
• “EXEC”在Proc名称之前。
• Commas in between Params
• 逗号之间的参数
• Chopping off '@' on the Param Definitions (not sure that bit is required though).
• 在Param定义上截断“@”(不确定是否需要bit)。

:

:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

#4

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

//Or

/ /或

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

//Or

/ /或

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

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

//Or

/ /或

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

#5

I use this method:

我用这个方法:

var results = this.Database.SqlQuery<yourEntity>("EXEC [ent].[GetNextExportJob] {0}", ProcessorID);

I like it because I just drop in Guids and Datetimes and SqlQuery performs all the formatting for me.

我喜欢它，因为我只在Guids和Datetimes中下降，而SqlQuery为我执行所有格式。

#6

Most answers are brittle because they rely on the order of the SP's parameters. Better to name the Stored Proc's params and give parameterized values to those.

大多数答案都是脆弱的，因为它们依赖于SP的参数的顺序。最好为存储的Proc的参数命名，并将参数化的值赋给它们。

In order to use Named params when calling your SP, without worrying about the order of parameters

为了在调用SP时使用名为params，而不必担心参数的顺序。

Using SQL Server named parameters with ExecuteStoreQuery and ExecuteStoreCommand

使用带有ExecuteStoreQuery和ExecuteStoreCommand的SQL服务器命名参数。

Describes the best approach. Better than Dan Mork's answer here.

描述了最好的方法。比Dan Mork的回答更好。

• Doesn't rely on concatenating strings, and doesn't rely on the order of parameters defined in the SP.
• 不依赖于连接字符串，也不依赖于SP中定义的参数的顺序。

E.g.:

例如:

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

context.Database.SqlQuery<myEntityType>(cmdText, params)

#7

@Tom Halladay's answer is correct with the mention that you shopuld also check for null values and send DbNullable if params are null as you would get an exception like

@Tom Halladay的答案是正确的，因为您的shopuld还检查null值，如果params为空，则发送DbNullable，因为您将得到一个异常。

The parameterized query '...' expects the parameter '@parameterName', which was not supplied.

参数化查询的…“期望参数‘@parameterName’，它没有提供。

Something like this helped me

像这样的事情帮助了我。

public static object GetDBNullOrValue<T>(this T val)
{
    bool isDbNull = true;
    Type t = typeof(T);

    if (Nullable.GetUnderlyingType(t) != null)
        isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);
    else if (t.IsValueType)
        isDbNull = false;
    else
        isDbNull = val == null;

    return isDbNull ? DBNull.Value : (object) val;
}

(credit for the method goes to https://*.com/users/284240/tim-schmelter)

(该方法的信用为https://*.com/users/284240/tim-schmelter)

Then use it like:

然后使用它:

new SqlParameter("@parameterName", parameter.GetValueOrDbNull())

or another solution, more simple, but not generic would be:

或者另一种解决方法，更简单，但不是通用的:

new SqlParameter("@parameterName", parameter??(object)DBNull.Value)

#8

I had the same error message when I was working with calling a stored procedure that takes two input parameters and returns 3 values using SELECT statement and I solved the issue like below in EF Code First Approach

在调用一个存储过程时，我有相同的错误消息，该过程使用两个输入参数并使用SELECT语句返回3个值，我在EF代码第一个方法中解决了如下问题。

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

UPDATE: It looks like with SQL SERVER 2005 missing EXEC keyword is creating problem. So to allow it to work with all SQL SERVER versions I updated my answer and added EXEC in below line

更新:看起来SQL SERVER 2005缺少EXEC关键字造成了问题。因此，为了让它与所有SQL服务器版本一起工作，我更新了我的答案，并在下面添加了EXEC。

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

#9

db.Database.SqlQuery<myEntityType>("exec GetNewSeqOfFoodServing @p0,@p1,@p2 ", foods_WEIGHT.NDB_No, HLP.CuntryID, HLP.ClientID).Single()

@p0,@p1,@p2......
or

@p0 @p1,@p2……或

db.Database.SqlQuery<myEntityType>(
    "exec GetNewSeqOfFoodServing @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

or

或

var cmdText = "exec [DoStuff] @Name = @name_param, @Age = @age_param";
var params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

db.Database.SqlQuery<myEntityType>(cmdText, params)

or

或

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

#10

entityobject_name.Database.ExecuteSqlCommand(string.Format(@"EXEC sp_name @parameter1='{0}',@path='{2}'", pvalue1, pvalue2));

where entityobject_name is your object created for entity example: Entity ent=new entity() // ent is entityobject name here

entityobject_name是为实体示例创建的对象:entity ent=new entity() // ent是entityobject name ?