Entity framework - SP Returned value

Question

Working on my first mvc(5) asp.net web application. I am using data entity framework. I have a stored procedure, 'usp_EventInsert' which takes HevEventID, HemEmployeID and HevEventDescription:

@HevEventID int,
@HemEmployeID int,
@HevEventDescription varchar(150)

SET NOCOUNT ON
SET XACT_ABORT ON

INSERT INTO [dbo].[RhEvent] ([HevEventID],[HemEmployeID],[HevEventDescription])
SELECT @HevEventID, @HemEmployeID, @HevEventDescriptionID

In my code, I want to insert an event in the table and check if it was successful. I am calling it this way, for example:

myDBcontext.usp_EventInsert(123,112345,"some description")

I was expecting to be able to fetch the HevEventID(or HemEmployeID or HevEventDescriptionID) once the stored procedure is called. For example, I tried:

var event = myDBcontext.usp_EventInsert(123,112345,"some description")

I was expecting to find the elements in the select inside 'event'. But event = -1 when I try that. Not only event doesn't have any of the select data in it, it's value is -1(From what I read SET NOCOUNT ON causes that?), which seems odd to me since the stored procedure was successful (new row being inserted in the table).

any suggestions on how I can be sure the stored procedure was successful? Without doing a select on the table..

Make your SP return a negative status code for error and the inserted ID if successfull. — Juan Carlos Oropeza, Oct 13 '16 at 19:37
Did you managed to solve your problem? if not i may give you an answer adapted directly to your question. — Antoine Pelletier, Oct 19 '16 at 17:39

score 1 · Accepted Answer · edited May 23 '17 at 12:13

Like i answered in this previous questions:

ORA-01722: invalid number on Entity Framework

Being sure that your stored procedure worked properly and obtain the RETURN you wanted from it can be quite a trick with Entity. I won't suggest you any precise methode of doing so. But i will strongly suggest you read all these links question/answer :

using stored procedure in entity framework

Getting data from stored procedure with Entity Framework

Sql Stored proc and Entity framework 6

If you haven't found your way out of it with one of the methode i provided you, explain what is the problem concerning the methode you've used and i'm sure we can fix this.

Entity framework - SP Returned value

1 Answers1