Entity Framework 7 FromSql stored procedure return value

Question

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?

Stafford Williams · Accepted Answer · 2015-12-17 10:23:15Z

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

edited Dec 17, 2015 at 10:23

answered Dec 9, 2015 at 23:32

Stafford Williams

9,8169 gold badges55 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mattpm Over a year ago

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?

Stafford Williams Over a year ago

@mattpm depends on how your application is setup. Start here; learn.microsoft.com/en-us/ef/core/miscellaneous/…

DanO Over a year ago

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 - 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.

Collectives™ on Stack Overflow

Entity Framework 7 FromSql stored procedure return value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related