October 26, 2014

Entity Framework: DBContext ExecuteSqlCommand and Stored-procedure does not return value

I have stored-procedures for CRUD and I expect all of my insert, update and delete stored-procedures would return an integer value after it is executed but the Entity Framework's DBContext ExecuteSqlCommand does not get the value that I use the command return in my stored-procedure.

Solution for now, I use an output parameter in my stored-procedures and I get the value that I return.
Ex:

My stored-procedure:

PROCEDURE [dbo].[TestReturnScalarValue]
    @par1 int,

    @ReturnValue INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @ReturnValue = @par1
END
My C# code:

public virtual int ExecuteStoredProcedureInt(string query, SqlParameter Output, params SqlParameter[] parameters)
        {
            DbContext.Database.ExecuteSqlCommand(query, parameters);
            return (int)Output.Value;
        }


To execute the above behavior in C#:

var par1= new SqlParameter("par1", 11);

var ReturnValue = new SqlParameter("ReturnValue", System.Data.SqlDbType.Int)
            {
                Direction = System.Data.ParameterDirection.Output
            };

var returnValue = ExecuteStoredProcedureInt(";EXEC [dbo].[TestReturnScalarValue] @ReturnValue, @par1", ReturnValue, par1); 

No comments: