如何通过实体框架将空值传递到存储过程? [英] How to pass null values into stored procedure via Entity Framework?

问题描述

我正在将ASP.NET MVC 4应用程序与Entity Framework一起使用.

我想有条件地传递空值，假设如果将值传递给DeptID，那么它应该只列出已传递的DeptID的值的列表，如果传递DeptID = NULL，则应该将所有部门的值传递给

我通过检查DeptID编写了一个过程，它返回了所需的输出(如果DeptID是null，则返回所有行).

但是问题是当我想通过实体框架的以下代码传递空值时，它不起作用.

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();
List<GeResult_Result> objTicketList =ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", " @DeptID",
new SqlParameter("DeptID", DeptID)).ToList();

知道为什么会这样吗?

解决方案

如果DeptID为NULL，则需要将DBNull.Value传递到SqlParameter

尝试一下:

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();

// I'm *assuming* that DeptID is defined as an "int" - if not, adapt as needed
SqlParameter paramDeptId = new SqlParameter("DeptID", SqlDbType.Int);

if (DeptID == null)
    paramDeptId.Value = DBType.Null;
else
    paramDeptId.Value = DeptID;

List<GeResult_Result> objTicketList = ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", "@DeptID", paramDeptId).ToList();

I am using an ASP.NET MVC 4 application with Entity Framework.

I want to pass null values conditionally, let's say if pass value into DeptID then it should list of values for only passed DeptID, and if pass DeptID = NULL then it should it all the departments.

I wrote a procedure by checking DeptID and it returns the desired output (if DeptID is null, then all rows are returned).

But problem is to when I want to pass null values through following code of Entity Framework it is not working.

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();
List<GeResult_Result> objTicketList =ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", " @DeptID",
new SqlParameter("DeptID", DeptID)).ToList();

Any idea why it is so?

解决方案

If DeptID is NULL, then you need to pass DBNull.Value into your SqlParameter

Try this:

IdleTicketsChecker.Data.Test11Entities objModel = new Data.Test11Entities();

// I'm *assuming* that DeptID is defined as an "int" - if not, adapt as needed
SqlParameter paramDeptId = new SqlParameter("DeptID", SqlDbType.Int);

if (DeptID == null)
    paramDeptId.Value = DBType.Null;
else
    paramDeptId.Value = DeptID;

List<GeResult_Result> objTicketList = ExecuteCustomStoredProc<GetIdleTicketsByMinutes_Result>("GeResult", "@DeptID", paramDeptId).ToList();

这篇关于如何通过实体框架将空值传递到存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！