Entity Framework Core - Using Stored Procedure with output parameters

user_1367915 0
Viewed 21599

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);
        }
entity-framework-core entity-framework asp.net
4 Answers

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();
user_113259010

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);
user_59749650 
**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();
user_173162190
Related