CODEWITHSUNDEEP
sql-serverentity-frameworkasp.net-mvc-4entity-framework-4entity-framework-6
skiskd
skiskd

Reputation: 423

How to pass null values into stored procedure via Entity Framework?

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?

Upvotes: 2

Views: 3765

Answers (1)

marc_s
marc_s

Reputation: 754488

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();

Upvotes: 4

Related Questions