How EF can capture return message or data from store procedure

Question

User1183902823 posted

here is a example of store procedures which return data so many different way

just return value
--------------------
CREATE PROCEDURE GetMyInt
( @Param int)
AS
DECLARE @ReturnValue int

SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
GO


value return by output parameter
------------------------------------
CREATE PROCEDURE GetMyInt
( @Param     int
 ,@OutValue  int OUTPUT)
AS
SELECT @OutValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

return value as resultset
-------------------------
CREATE PROCEDURE GetMyInt
( @Param     int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

there is 3 different approach. now if we call these 3 store procedure by EF then how EF can capture return data.

Answer 1

User1183902823 posted

my below store procedure return error message to client side. so now  tell me with a sample example how EF can hold the error message.

one example more https://www.codeproject.com/Questions/260684/how-we-can-Raise-Error-in-Stored-procedure-IN-MS-S

first approach to return error message from store procedure
-------------------------------------------------------------

BEGIN TRY
   delete from Test
    where  ID = @ID
END TRY
BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    BREAK
END CATCH

Second approach to return error message from store procedure
-------------------------------------------------------------
DECLARE @DetailedErrorDesc VARCHAR(MAX)
BEGIN TRY

--tsql code goes here

END TRY
BEGIN CATCH

SELECT @DetailedErrorDesc =         
  CAST(ERROR_NUMBER() AS VARCHAR) + ' : '+
  CAST(ERROR_SEVERITY() AS VARCHAR) + ' : ' +
  CAST(ERROR_STATE() AS VARCHAR) + ' : ' +
  ERROR_PROCEDURE() + ' : ' +
  ERROR_MESSAGE() + ' : ' +
  CAST(ERROR_LINE() AS VARCHAR);

--Now you can decide what to do with the detailed error message....return it or log it etc

END CATCH

OR 

BEGIN TRY
    -- ...
    -- Do Stuff
    -- ...
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Answer 2

User1120430333 posted

If you are using Sprocs, then what's the purpose of using EF? Why don' you just use ADO.NET and SQL Command objects?

Answer 3

User-707554951 posted

Hi tridip1974,

I test 3 different approach, it all return -1 value. which indicates you sp have something wrong.

In sp value could be return by output parameter.

Correct should as below:

CREATE PROCEDURE GetMyInt1
( @SellId     int
 ,@OutValue  int OUTPUT)
AS BEGIN
SELECT @OutValue=TotalAmount FROM Sells WHERE SellId = @SellId
select @OutValue
END

Another way is by using return codes;

For details about this please refer to the following links below:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure

http://www.c-sharpcorner.com/UploadFile/rohatash/stored-procedure-with-a-return-value-in-sql-server-2012/

https://www.aspsnippets.com/Articles/Return-Value-from-Stored-Procedure-in-SQL-Server-example.aspx

Best regards 

Cathy

Answer 4

User1183902823 posted

are you trying to say if we return result set from SP the below way then EF can not hold the return data ?

CREATE PROCEDURE GetMyInt
( @Param int)
AS
DECLARE @ReturnValue int

SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
GO

OR

CREATE PROCEDURE GetMyInt
( @Param     int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

Answer 5

User1120430333 posted

are you trying to say if we return result set from SP the below way then EF can not hold the return data ?

If all you are doing is running sprocs through EF, then what is the point of even using EF?

You can execute the sproc using the EF backdoor, use a datareader to read the results, populate a DTO and return the DTO. 

https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

Answer 6

User475983607 posted

Have you tried RTFM; Read The Free Manual?   

What version of Entity Framework?  If this is EDMX, IIRC, EF can handle OUTPUT parameter and result sets but not RETURN.  The function import wizard make the process pretty easy.

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

If you using Code First then I would execute RAW Queries similar to how ADO.NET looks and, like the above, well documented.

https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-proceduresv

https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/advanced