How to access the "Results", "Messages" and "Return Value" of a stored procedure using Entity Framework 4?

Question

Question

How to access the "results", "messages" and "return value" of a stored procedure using Entity Framework 4.4 and C # 4.0?

Below is a procedure that takes three parameters. Anyway, when I run the Store procedure, I should, I hope, have access to all three values ​​for Results, Messages, and Return Value.

Can someone help me figure out how to do this with EF? Using code created from EF, all that seems accessible to me is the "Results" of the query (returned lines)

Stored procedure

USE [THIS_DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[THIS_PROCEDURE] @FIRST_PARAM CHAR(17) = NULL, @SECOND_PARAM CHAR(2) = NULL, @THIRD_PARAM CHAR(5) = NULL AS BEGIN SET NOCOUNT ON; DECLARE @ReturnValue INT = 0; IF COALESCE(@SECOND_PARAM, 'XX') NOT IN ('XX', 'YY') BEGIN RAISERROR('Invalid @SECOND_PARAM value: %s; @SECOND_PARAM mXXt be XX or YY.', 2, 1, @SECOND_PARAM ) WITH SETERROR; SET @ReturnValue = -50100; END IF COALESCE(@SECOND_PARAM, 'XX') = 'YY' BEGIN RAISERROR('@SECOND_PARAM value: %s; YY is valid, but currently is not supported, returning XX results.', 2, 1, @SECOND_PARAM) WITH SETERROR; SET @ReturnValue = -50105; END IF COALESCE(@THIRD_PARAM, 'XX-EN') NOT IN ('XX-EN') BEGIN RAISERROR('Invalid @THIRD_PARAM value: %s; @THIRD_PARAM mXXt be XX-EN.', 2, 1, @THIRD_PARAM) WITH SETERROR; SET @ReturnValue = -50101; END SELECT DISTINCT THESE.VALUES FROM dbo.THIS_TABLE WHERE THESE.CONDITIONS; IF @@ROWCOUNT = 0 BEGIN DECLARE @SP_MATCHCOUNT INT EXEC @SP_MATCHCOUNT = [dbo].[MATCHTABLE] @PATTERNH = @PATTERN IF @SP_MATCHCOUNT > 0 BEGIN RAISERROR('Mapping from HERE to HERE not found for PATTERN: %s.', 2, 1, @PATTERN) WITH SETERROR SET @ReturnValue = -50103; END ELSE BEGIN RAISERROR('PATTERN Pattern not found for PATTERN: %s.', 2, 1, @PATTERN) WITH SETERROR SET @ReturnValue = -50104; END END RETURN @ReturnValue END 

CODE

 public virtual ObjectResult<THIS_PROCEDURE_RESULT> THIS_PROCEDURE_METHOD(string FIRST, string SECOND, string THIRD) { var FIRST_PARAM = FIRST != null ? new ObjectParameter("FIRST", FIRST) : new ObjectParameter("FIRST", typeof(string)); var SECOND_PARAM = SECOND != null ? new ObjectParameter("SECOND", SECOND) : new ObjectParameter("SECOND", typeof(string)); var THIRD_PARAM = THIRD != null ? new ObjectParameter("THIRD", THIRD) : new ObjectParameter("THIRD", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<THIS_PROCEDURE_RESULT>("THIS_PROCEDURE", FIRST_PARAM, SECOND_PARAM, THIRD_PARAM); } 

Answer 1

So, first, first :-) I just want to make sure that we are on the same page before answering 3 parts of the question. EF is for ORM (Object Relational Mapping). This means that its goal is to transform relational data into code objects (and vice versa). The mechanism that he uses to do this is result sets (not return values). Therefore, most of the plumbing inside EF is specifically designed to work with result sets, as well as to automatically create SQL to obtain these result sets. However, since people requested it, EF now has the ability to execute stored procedures, but this ability is not comprehensive and is a kind of side effect for the core capabilities of the product. Having said that, EF really uses ADO.NET under covers and where you are going to receive answers, because ADO.NET processes all your scripts.

The first problem is how to get the results. In this case, EF will execute SP, and, presumably, it is mapped to some object that has properties corresponding to the result columns. This means that EF will create a collection (a simpler set of query results), each of which represents a row of data in the results. In your case, the return of your method is ObjectResult. ObjectResult is a collection of objects, and each element is of type THIS_PROCEDURE_RESULT, which, in turn, has a property for each displayed column of the result.

The second problem is how to receive messages. If a Raiserror is used with a specific severity range, this will cause ADO.NET to throw an exception (such as SqlException). EF will just skip this error. This SQLException instance will contain all the error and message information. To see this, you just need to catch the error:

 try { // call EF SP method here... } catch(SqlException se) { Debug.WriteLine(se.Message); } catch(Exception e) { // all non-DB errors will be seen here... } 

However, if the Raiserror statement has a warning or severity information, ADO.NET will not throw an exception. In this case, you should use the connection object event to view informational and warning messages from the database. To do this in EF, you must get the EntityConnection from the context of the EF object, and then get the Store connection from EntityConnection. If you are using SQL Server (the SqlClient ADO.NET provider), this will be an instance of SqlConnection. This instance contains an event called InfoMessage. You can attach an event handler to this event to block messages. More details here: http://support.microsoft.com/kb/321903

The final issue is how to get the return value. This one will suck. Based on my first paragraph, EF is not really intended to arbitrarily handle SP calls. Although it will display result sets in a collection of objects, it does not handle return values ​​from SP. You will need to use ADO.NET without an EF level to access the parameter collections of the SqlCommand object. One of the parameters is the returnValue type parameter, and it will contain the return value.