EntityFramework StoredProcedure retrieve parameter output

Question

EntityFramework StoredProcedure retrieve parameter output

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

How to retrieve the Output parameter of a storedProcedure?

SP Example:

create PROCEDURE StoreProcedure_Name 
    @ParametterWithNummvalue    varchar(50) = null, 
    @In_Parameter varchar(50), 
    @Out_Parameter varchar(10) out
AS

    set @Out_Parameter = 'teste'

RETURN 0

C # code

SqlParameter[] Params = {
                new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
                new SqlParameter("@In_Parameter", "teste novo"),
                new SqlParameter("@Out_Parameter", SqlDbType.VarChar, 10) { Direction = ParameterDirection.Output }};

            var r = Db.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", Params);

            var ReturnValue = Params[2].Value;

ReturnValue should contain the value "test" but it is returning empty.

sql-server c# entity-framework stored-procedures

asked by anonymous 29.03.2016 / 03:47

2 answers

Generation of GNRE Guides for the state of RJ Difficulty Menu image Bootstrap

score 1 · Answer 1

Hello, try to do this:

//Separe os parâmetros de entrada e saída
var parametterWithNummvalue = new SqlParameter();
parametterWithNummvalue.ParameterName = "@ParametterWithNummvalue";
parametterWithNummvalue.Direction = ParameterDirection.Input;
parametterWithNummvalue.SqlDbType = SqlDbType.VarChar;
parametterWithNummvalue.Size = 50;
parametterWithNummvalue.Value = null; //Substitua pelo valor desejado

var inParameter = new SqlParameter();
inParameter.ParameterName = "@InParameter";
inParameter.Direction = ParameterDirection.Input;
inParameter.SqlDbType = SqlDbType.VarChar;
inParameter.Size = 50;
inParameter.Value = null; //Substitua pelo valor desejado

var outParameter = new SqlParameter();
outParameter.ParameterName = "@OutParameter";
outParameter.Direction = ParameterDirection.Output;
outParameter.SqlDbType = SqlDbType.VarChar;
outParameter.Size = 10;
outParameter.Value = null; //Substitua pelo valor desejado

var valorRetornado = new SqlParameter();
valorRetornado.ParameterName = "@ValorRetornado";
valorRetornado.SqlDbType = SqlDbType.Int;
valorRetornado.Direction = ParameterDirection.Output;

//Chame a procedure com base nos parâmetros criados
var dados = Db.Database.SqlQuery("exec @ValorRetornado = StoreProcedure_Name @ParametterWithNummvalue, @InParameter, @OutParameter OUT", valorRetornado, parametterWithNummvalue, inParameter, outParameter);

Note that the way your procedure was created in the example will return two values, one in OUT (variable: outParameter) and one in RETURN (variable: return value).

/ em>

I hope I have helped.

score 0 · Answer 2

I was able to resolve only the Out expression after the outParameter parameter

In the end it looks like this:

SqlParameter[] Params = {
    new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
    new SqlParameter("@In_Parameter", "teste novo"),
    new SqlParameter("@Out_Parameter", SqlDbType.VarChar, 10) { Direction = ParameterDirection.Output }};

var r = Db.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter Out", Params);

var ReturnValue = Params[2].Value;