newSingleton newSingleton - 4 months ago 27
C# Question

How to access 'Results', 'Messages', and 'Return Value' of a Stored Procedure using Entity Framework 4

QUESTION



How do I access the 'Results', 'Messages', and 'Return Value' of a Stored Procedure using Entity Framework 4.4 and C# 4.0? Below is the Stored Proc that takes three parameters. One way or another when I run the store procedure I should, I hope, be able to access all three values for 'Results', 'Messages', and 'Return Value'. Can someone help me figure out how to do that with EF? Using the code that is generated out of EF all I seem to be able to access is the 'Results' of the query ( returned rows )

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

So, first things first :-) Just want to make sure we're on the same page before I answer the 3 parts of the question. EF is designed to be an ORM (object-relational-mapper). That means its purpose for being is to translate relational data to code objects (and vice-versa). The mechanism it uses for this is result sets (not return values). So most of the plumbing inside EF is specifically designed to operate on result sets, and also to automatically generate SQL for getting those result sets. However, since people requested it, EF now has the capability to execute stored procedures, but that ability is not comprehensive, and is sort of a side-effect to the main capabilities of the product. Having said that, EF does use ADO.NET under the covers, and that's where you are going to get your answers because ADO.NET does handle all your scenarios.

First problem - how to get results. EF will execute the SP in this case, and presumably, it's mapped to some object that has properties that match the result columns. That means that EF will create a collection (enumerable query result set to be more precise) of objects, 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 item is of type THIS_PROCEDURE_RESULT, which in turn has a property for each mapped column of the result.

Second problem - how to get messages. If Raiserror is used with a certain range of severity, will cause ADO.NET to throw and exception (of type SqlException). EF will just just surface (pass through) that error. That SQLException instance will contain all the error & message information. To see it, you just have 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 is of a warning or info severity, ADO.NET will not throw an exception. In that case, you have to use an event of the connection object to see info and warning messages from the databse. To do this in EF, you have to get the EntityConnection from the EF object context, and then get the Store Connection from the EntityConnection. If you are using SQL Server (SqlClient ADO.NET provider), this will be a SqlConnection instance. That instance contains an event called InfoMessage. You can hook up an event handler to that event to trap messages. More info here: http://support.microsoft.com/kb/321903

Last problem - how to get Return Value. This one is going to suck. Based on my first paragraph, EF isn't really designed to arbitrarily handle SP calls. While it will map result sets to object collections, it doesn't handle return values from SPs. You will have to use ADO.NET without the EF layer in order to access the Parameters collections of the SqlCommand object. One of the parameters is of parameter-type ReturnValue, and it will contain the return value itself.