1

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);
SledgeHammer
  • 7,338
  • 6
  • 41
  • 86
  • isn't there the `Database` object off context? Like `context.Database.SqlQuery("...");` – Jonesopolis Dec 16 '16 at 21:39
  • Possible duplicate of [How to run stored procedures in Entity Framework Core?](http://stackoverflow.com/questions/28599404/how-to-run-stored-procedures-in-entity-framework-core) – Eris Dec 16 '16 at 21:59
  • @Jonesopolis -- hmm... ok, that might solve the blah issue :), but what about the out parameter? Doesn't seem like it accepts a SqlParameter object like the samples show. – SledgeHammer Dec 16 '16 at 21:59
  • @Eris, do you see anything there about OUT params? – SledgeHammer Dec 16 '16 at 22:00
  • @SledgeHammer The second answer shows how to create a standard SQLCommand object from the DBContext information, which allows you to use normal parameters and set `ParameterDirection.Output` – Eris Dec 16 '16 at 22:01
  • @Eris, trying to do: SqlParameter sql = new SqlParameter("@exist", System.Data.SqlDbType.Bit) { Direction = System.Data.ParameterDirection.Output }; var v = this.Database.ExecuteSqlCommand("xxx", p1, address, city, state, zip, sql); but I keep getting an exception saying "@exist" is missing. – SledgeHammer Dec 16 '16 at 22:07
  • @Eris... hmm... I was able to get it working with the "native" SqlCommand call, but not with the FromSql or ExecuteSqlCommand... seems like they don't wire up the params correctly? – SledgeHammer Dec 16 '16 at 23:01

1 Answers1

0

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());
Peter Bill
  • 508
  • 3
  • 12