SQL Server Output Parameter showing null value in EF C#

Question

I have a below stored procedure with output parameter.

ALTER proc [dbo].[CRS_GetNewMessageCount]
@CaseId int,
@Type varchar(50),
@Location varchar(50),
@Count int out
as

begin
if @location='admin'
    begin
    if @type='consumer'
    select @Count=(Select count(fdId) from tb_CRS_Messages where fdCaseId=@CaseId and fdIsConsumerType=1 and fdIsReadatAdmin=0)
    else
    select @Count=(Select count(fdId) from tb_CRS_Messages where fdCaseId=@CaseId and fdIsMemberType=1 and fdIsReadatAdmin=0)
    end
else
begin
    if @type='consumer'
    select @Count=(Select count(fdId) from tb_CRS_Messages where fdCaseId=@CaseId and fdIsConsumerType=1 and fdIsReadatFront=0)
else
select @Count=(Select count(fdId) from tb_CRS_Messages where fdCaseId=@CaseId and fdIsMemberType=1 and fdIsReadatFront=0)
END
SELECT @Count
END

It is perfect working at SQL server see below output :

I am calling this stored procedure through Entity Framework :

using (DbContext db = new DbContext())
            {
                try
                {
                    var NewMessage = new ObjectParameter("Count", typeof(int));
                    int returnValue = 0;
                    db.CRS_GetNewMessageCount(CaseId, type, location, NewMessage);
                    int.TryParse(NewMessage.Value.ToString(), out returnValue);
                    return returnValue;
                }
                catch { return 0; }
            }

It gives null value in output parameter.

Please help.

Using SELECT @Count is redundant. You don't capture the value of the out parameter because you enter null. — Gert Arnold
– Gert Arnold, Commented Apr 27, 2016 at 13:43
It solved using FirstOrDefault() next to the Stored Procedure. Thank you guys for your help. — Sunil Kumar
– Sunil Kumar, Commented Apr 27, 2016 at 14:14

Reboon · Accepted Answer · 2016-04-27 11:59:37Z

1

Not sure whether it makes a difference, but did you try to use "typeof(Int32)" instead of "typeof(int)"? Also try NewMessage as ObjectParameter and not as var, maybe it makes a difference.

ObjectParameter NewMessage = new ObjectParameter("NewMessage ", typeof(Int32));

Did you try to run your stored procedure without parameters, means return @Count-variable with a value no matter what parameters you enter? Like this you can identify whether your input parameters are wrong (handed over by C#) or not.

answered Apr 27, 2016 at 11:59

Reboon

5782 gold badges5 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alltej · Accepted Answer · 2016-04-27 14:28:38Z

-1

Hope this helps How to: Execute a Query Using a Stored Procedure with In and Out Parameters

https://msdn.microsoft.com/en-us/library/bb896334(v=vs.100).aspx

answered Apr 27, 2016 at 14:28

alltej

7,39311 gold badges57 silver badges100 bronze badges

Collectives™ on Stack Overflow

SQL Server Output Parameter showing null value in EF C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related