使用EntityFramework用空参数值调用存储过程

本文介绍了使用EntityFramework用空参数值调用存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在sqlserver 2008上有一个存储过程，其中一个参数接受空值.我不知道如何使用参数的空值调用该SP.对于更多的上下文，我正在使用EntityFramework 6xx

在下一个示例中，参数"@status，@Compatible"的值可以为null，但是我只是得到一个例外，说明未提供这些praram.

public override IList<MyOutputType> SearchStuff(string Term, int? status, bool? Compatible, long TMPLDMID, int RangeFrom, int RangeTo) { List<SqlParameter> par = new List<SqlParameter>(); par.Add(new SqlParameter("@Term", System.Data.SqlDbType.VarChar, 50) { Value = "%" + Term + "%" }); par.Add(new SqlParameter("@status", System.Data.SqlDbType.Int) { Value = status, IsNullable = true }); par.Add(new SqlParameter("@Compatible", Compatible) { IsNullable = true }); par.Add(new SqlParameter("@TMPLDMID", TMPLDMID)); par.Add(new SqlParameter("@RangeFrom", RangeFrom)); par.Add(new SqlParameter("@RangeTo", RangeTo)); return db.Database.SqlQuery<MyOutputType>( "EXEC [spSearchForStuff] @Term, @status, @Compatible, @TMPLDMID, @RangeFrom, @RangeTo", par.ToArray() ).ToList();

解决方案

我遇到了同样的问题.如果值为null，则将加载默认值.我实际上需要一个null，因此对于状态，您应该可以在下面进行.

par.Add(new SqlParameter("@status", (object)status??DBNull.Value);

检查此答案以获取更多深入的示例 stackoverflow/a/4556007/1248536

i've a stored procedure on sqlserver 2008 and one of the parameters accept null values. i don't know how to call that SP with a null value on the parameter. for a little more context i'm using EntityFramework 6xx

On the next example the parameters "@status, @Compatible" can have null as value, but i'm just getting an exception saying that those prarams was not provided.

public override IList<MyOutputType> SearchStuff(string Term, int? status, bool? Compatible, long TMPLDMID, int RangeFrom, int RangeTo) { List<SqlParameter> par = new List<SqlParameter>(); par.Add(new SqlParameter("@Term", System.Data.SqlDbType.VarChar, 50) { Value = "%" + Term + "%" }); par.Add(new SqlParameter("@status", System.Data.SqlDbType.Int) { Value = status, IsNullable = true }); par.Add(new SqlParameter("@Compatible", Compatible) { IsNullable = true }); par.Add(new SqlParameter("@TMPLDMID", TMPLDMID)); par.Add(new SqlParameter("@RangeFrom", RangeFrom)); par.Add(new SqlParameter("@RangeTo", RangeTo)); return db.Database.SqlQuery<MyOutputType>( "EXEC [spSearchForStuff] @Term, @status, @Compatible, @TMPLDMID, @RangeFrom, @RangeTo", par.ToArray() ).ToList();

解决方案

I ran into the same issue. If the values were null it would load default. Which I actually needed a null, so for status you should be able to do below.

par.Add(new SqlParameter("@status", (object)status??DBNull.Value);

check this answer for more in depth examples stackoverflow/a/4556007/1248536