Entity Framework Core - Using Stored Procedure with output parameters

Question

I'm trying to use stored procedures with new Entity Framework Core. I need to start new project soon which will be ASP.Net 5, but not sure if Entity Framework will fit for the job. The application will be triggering several stored procedures per minute and I need output parameters. Will EF be good for this or should I use ADO.Net?

I have tried FromSql and database.ExecuteSqlCommand but no luck.

using (AppDbContext db = factory.Create())
        {
            var in1 = new SqlParameter
            {
                ParameterName = "ParamIn1",
                DbType = System.Data.DbType.Int64,
                Direction = System.Data.ParameterDirection.Input
            };
            var in2 = new SqlParameter
            {
                ParameterName = "ParamIn2",
                DbType = System.Data.DbType.String,
                Direction = System.Data.ParameterDirection.Input
            };
            var out1 = new SqlParameter
            {
                ParameterName = "ParamOut1",
                DbType = System.Data.DbType.Int64,
                Direction = System.Data.ParameterDirection.Output
            };
            var out2 = new SqlParameter
            {
                ParameterName = "ParamOut2",
                DbType = System.Data.DbType.String,
                Direction = System.Data.ParameterDirection.Output
            };

            var result = db.Database.ExecuteSqlCommand("exec spTestSp", in1, in2, out1, out2);
        }

Nkosi · Accepted Answer · 2017-07-22 10:34:02Z

24

It should work, but I believe you also need to include the parameter names and the OUT keyword in the command statement

var sql = "exec spTestSp @ParamIn1, @ParamIn2, @ParamOut1 OUT, @ParamOut2 OUT";
var result = db.Database.ExecuteSqlCommand(sql, in1, in2, out1, out2);

var out1Value = (long) out1.Value;
var out2Value = (string) out2.Value;

edited Jul 22, 2017 at 10:34

answered Jul 22, 2017 at 10:26

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3230660 Over a year ago

Confirmed this works. There are several variations of how to do this floating around which do not work.

JB's Over a year ago

Whenever paste be sure to Set Length for OUT PARAMETER SqlParameter job1 = cmd2.Parameters.Add("@job", SqlDbType.VarChar, 50);

JB's Over a year ago

@Muflix it was giving an error to me when I was not specifying Length

Muflix Over a year ago

Can you please provide full example with parameter declares ?

DEEPMOC · Accepted Answer · 2021-11-03 06:40:38Z

6

**This is a working example**

1)  Create sample SP

--exec [dbo].sampleCalc 100,0,''
CREATE PROCEDURE[dbo].sampleCalc
   @inputPara int,
   @outputPara1 INT OUTPUT,
   @outputPara2 varchar(50) OUTPUT
AS
BEGIN
    set @outputPara1 = @inputPara + 100
    set @outputPara2 = 'result is ' + convert(varchar(50), @outputPara1)
    print @outputPara1
    print @outputPara2
END

2) C#

var param = new SqlParameter[] {
new SqlParameter() {
 ParameterName = "@inputPara",
 SqlDbType =  System.Data.SqlDbType.Int,
 Direction = System.Data.ParameterDirection.Input,
 Value = 100
},
new SqlParameter() {
 ParameterName = "@outputPara1",
 SqlDbType =  System.Data.SqlDbType.Int,
 Direction = System.Data.ParameterDirection.Output,
},
new SqlParameter() {
 ParameterName = "@outputPara2",
 SqlDbType =  System.Data.SqlDbType.VarChar,
 Direction = System.Data.ParameterDirection.Output,
 Size = 50
}};


var sql = "exec sampleCalc @inputPara, @outputPara1 OUT, @outputPara2 OUT";
var resultObj = _context.Database.ExecuteSqlRaw(sql, param.ToArray());

var out1Value = param[1].Value.ToString();
var out2Value = param[2].Value.ToString();

answered Nov 3, 2021 at 6:40

DEEPMOC

611 silver badge1 bronze badge

3 Comments

Gert Arnold Over a year ago

Please explain why and how this complements all existing answers. It's not necessary to post new answers if they don't show anything essentially new.

RoastBeast Over a year ago

@GertArnold I believe that DEEPMOC was trying to show how this could be done using the Entity Framework ExecuteSqlRaw method. It helped me with my question!

jpp1jpp1 Over a year ago

Me too, IIRC I had to use ExecuteSqlRaw because of Blazor and IDbContextFactory recommendation.

Imran Faruqi · Accepted Answer · 2020-02-11 19:59:51Z

3

Full example of the solution as described above by @Nkosi and partially by @Whistler for the ease of future readers!

In my example, I was trying to execute a stored procedure existed in my database to get specific path.

string tableName = "DocumentStore";
string path;

        var in1 = new SqlParameter
        {
            ParameterName = "TableName",
            Value = tableName,
            Size = Int32.MaxValue,
            DbType = System.Data.DbType.String,
            Direction = System.Data.ParameterDirection.Input
        };

        var out1 = new SqlParameter
        {
            ParameterName = "Path",
            DbType = System.Data.DbType.String,
            Size = Int32.MaxValue,
            Direction = System.Data.ParameterDirection.Output
        };

        //_context is DbContext Object
        _context.Database.ExecuteSqlCommand("EXEC GetFileTableRootPath @TableName, @Path OUT", in1,out1);

        path = out1.Value.ToString();

answered Feb 11, 2020 at 19:59

Imran Faruqi

77310 silver badges21 bronze badges

1 Comment

ibram Over a year ago

if someone comes up here, Size is meant for the size of the data type's Byte (e.g. 4 for int32 or 8 for int64) or the string size described in the sql table, like varchar(32) with size=32

Manfred Wippel · Accepted Answer · 2019-04-25 11:47:22Z

2

The StoredProcedureEFCore extention supports output parameters

Then the usage is like this

List<Model> rows = null;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 300L)
   .AddParam("limitOut", out IOutParam<long> limitOut)
   .Exec(r => rows = r.ToList<Model>());

long limitOutValue = limitOut.Value;

ctx.LoadStoredProc("dbo.ReturnBoolean")
   .AddParam("boolean_to_return", true)
   .ReturnValue(out IOutParam<bool> retParam)
   .ExecNonQuery();

bool b = retParam.Value;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 1L)
   .ExecScalar(out long l);

answered Apr 25, 2019 at 11:47

Manfred Wippel

2,1142 gold badges17 silver badges15 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core - Using Stored Procedure with output parameters

4 Answers 4

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related