Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Executing SQL Stored Procedure with Output Parameter from Entity Framework

Using EF, I'm trying to execute a stored procedure that returns a single string value, i.e. the status of an SQL Agent Job.

The stored procedure is declared as

CREATE PROCEDURE [dbo].[up_GetJobStatus](@JobStatus NVARCHAR(30) OUTPUT)
AS

-- some code omitted for brevity

SELECT @JobStatus = (
SELECT  
    CASE job_state 
    WHEN 1 THEN 'Executing'
    WHEN 2 THEN 'Waiting for thread'
    WHEN 3 THEN 'Between retries'
    WHEN 4 THEN 'Idle'
    WHEN 5 THEN 'Suspended'
    WHEN 6 THEN '<unknown>'
    WHEN 7 THEN 'Performing completion actions'
END
FROM @xp_results results 
INNER JOIN msdb.dbo.sysjobs sj
ON results.job_id = sj.job_id
WHERE sj.job_id = @job_id)

RETURN

I have verified the stored procedure is working correct as I can execute it in query window and it returns

    @JobStatus
  ------------
  1|Idle

However when executing with EF, the param value is NULL

var param = new SqlParameter
{
    ParameterName = "@JobStatus",
    DbType = DbType.String,
    Size = 30,
    Direction = System.Data.ParameterDirection.Output
};

var result = this.etlContext.Database.SqlQuery<string>("EXEC dbo.up_GetJobStatus @JobStatus OUTPUT", param);

I've also tried the ExecuteSqlCommand method but that didn't work either.

Any ideas?

like image 959
empo Avatar asked Feb 27 '14 12:02

empo


People also ask

Can stored procedure have output parameter?

The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.

Can a stored procedure return an output value to its caller?

Return data using an output parameter. If you specify the output keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits.

Can we use stored procedure in Entity Framework?

The Entity Framework has the capability of importing a Stored Procedure as a function. We can also map the result of the function back to any entity type or complex type.


2 Answers

  1. Create stored procedure in database

    CREATE PROCEDURE [dbo].myStoredProcName
        @inputParam1 VARCHAR(150),
        @inputParam2 VARCHAR(150),
        @myOutputParamBool BIT OUTPUT,
        @myOutputParamString VARCHAR(100) OUTPUT,
        @myOutputParamInt INT OUTPUT
    AS
    BEGIN
        -- sql here
    END
    
  2. Update entity model from database to include stored procedure as shown here

  1. Call the stored procedure from C# code

    // Type is System.Data.Entity.Core.Objects.ObjectParameter
    ObjectParameter myOutputParamBool = new ObjectParameter("myOutputParamBool", typeof(bool));
    ObjectParameter myOutputParamString = new ObjectParameter("myOutputParamString", typeof(string));
    ObjectParameter myOutputParamInt = new ObjectParameter("myOutputParamInt", typeof(Int32));
    
    using (var context = new SandCryptEntities())
    {
        context.myStoredProcName(inputParam1, inputParam2, myOutputParamBool, myOutputParamString, myOutputParamInt);   
    }
    
    bool myBool = Convert.ToBoolean(myOutputParamBool.Value);
    string myString = Convert.ToString(myOutputParamString.Value);
    int myInt = Convert.ToInt32(myOutputParamInt.Value);
    
like image 85
Augis Avatar answered Sep 18 '22 19:09

Augis


Here's an actual answer. Apparently there are serious issues with output parameters in entity framework when you're using DbContext/code first. This article has a good discussion and workaround: http://weblogs.asp.net/dwahlin/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters. Apparently it's supposed to be "fixed" with an update, but I haven't really seen that happen.

like image 27
sovemp Avatar answered Sep 19 '22 19:09

sovemp