In EF Core, with dbContext
, how do you call a stored procedure with input AND an output param? I know the basic gyst is:
Set<blah>.FromSql("storedProcName @p0 = {0}, @p1 = {1}", p0, p1);
But, what do I put for "blah" since there is no entity, it's just a bool output... and how do I get said output?
EDIT: That question doesn't solve the issue.
DAMN... FINALLY got it working... you have to use the brace syntax and add the OUTPUT keyword on the last param.
SqlParameter paramOut = new SqlParameter("@p5", SqlDbType.Bit) { Direction = ParameterDirection.Output };
this.Database.ExecuteSqlCommand("exec usp_xxx @p1={0}, @p2={1}, @p3={2}, @p4={3}, @p5={4}, @exist={5} OUTPUT",
p1, p2, p3, p4, p5, paramOut);
You could write a utility method like:
private static int ExecuteSqlCount(string statement, SqlParameter[] paramsSql)
{
using (Entities dbContext = new Entities())
{
var total = dbContext.Database.SqlQuery<int>(statement, paramsSql).First();
return total;
}
}
}
Assuming the stored procedure takes the two parameters as shown and returns an integer, it would be called like:
var parameters = new List<SqlParameter>();
string statement = "exec uspPersonAdd @personName = @name, @activeFlag = @active";
parameters.Add(new SqlParameter("@name", person.PersonName));
parameters.Add(new SqlParameter("@active", person.Active));
int id = ExecuteSqlCount(statement, parameters.ToArray());