Entity Framework Core - Using Stored Procedure with output parameters

asp.net entity-framework entity-framework-core

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);
        }
1
9
7/22/2017 9:33:20 AM

Accepted Answer

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;
12
7/22/2017 10:34:02 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow