3

I am trying to return a value using the new FromSql command in Entity Framework 7. My stored procedure returns a value of 0 if all goes well and 1 if an error occurs.

Using FromSql with DbSet we can for example do 

_dbContext.ExampleEntity.FromSql('someSproc', 'param')

How will you get the scalar return value of 0 or 1 from this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
luxury
  • 53
  • 1
  • 6

1 Answers1

3

Looks like stored procedure support is still on the backlog.

Here's an example extension method you can call using _dbContext.ExecuteStoredProcedure("someSproc", "param");.

public static class DbContextExtension 
{
    public static int ExecuteStoredProcedure(this DbContext context, string name, string parameter)
    {
        var command = context.Database.GetDbConnection().CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = name;
        var param = command.CreateParameter();
        param.ParameterName = "@p0";
        param.Value = parameter;
        command.Parameters.Add(param);
        return (int)command.ExecuteScalar();
    }
}
Stafford Williams
  • 9,696
  • 8
  • 50
  • 101
  • Hi Stafford, that looks spot on for what I need. Question, how do you get/instantiate the context in the first place in order to be able to call the extension method above? – mattpm Dec 14 '16 at 03:36
  • 1
    @mattpm depends on how your application is setup. Start here; https://learn.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext – Stafford Williams Dec 14 '16 at 03:38
  • This does not work for me - I get "ExecuteScalar requires an open and available Connection. The connection's current state is closed." In order to get it to work I have to explicitly open the connection, as shown here - https://stackoverflow.com/a/39543702/432085. I am injecting my DbContext via the Startup.cs in an ASP.Net Core API, and I have no issues with other DbContext operations. Would be curious to know how you got this to work. – DanO Sep 22 '17 at 17:10