How to execute stored procedure and get return result in MVC/EF/LINQ

Question

Could any one guide me on how to execute a SQL Server stored procedure in ASP.NET MVC / EF
application and get results back?

SQL Server stored procedure

 CREATE PROCEDURE dbo.StoredProcedure2 AS
     declare @parameter2 int
     SET @parameter2 = 4

     RETURN @parameter2 

MVC code

 private readonly TestDatastoreContext _context = new TestDatastoreContext();

 public ViewResult Index(string id)
 {
        ViewData["EnvironmentId"] = id;

        using (_context)
        {
            _context.Database.Connection.Open();
            var command = _context.Database.Connection.CreateCommand();
            command.CommandText = "dbo.StoredProcedure2";
            command.CommandType = System.Data.CommandType.StoredProcedure;
            var test = (command.ExecuteScalar());
        }

        var bigView = new BigViewModel
        {
            VersionsModel = _context.Versions.ToList(),
            EnvironmentViewModel = _context.Environments.ToList(),
        };

        return View(model: bigView);
}

Solution 1

Your problem is this: you're returning the value from the stored procedure (using RETURN @paramter2), but your .NET code is trying to read a result set; something that would be "returned" by using a SELECT ..... statement inside the stored procedure

So change your stored procedure to this:

CREATE PROCEDURE dbo.StoredProcedure2 AS
     declare @parameter2 int
     SET @parameter2 = 4

     SELECT @parameter2 

and then your .NET code should work just fine.

The RETURN statement should be used for status codes only and it can return INT values only. If you want to use that, you'll have to define a SqlParameter for your stored procedure with a Direction.ReturnValue

Solution 2

Check this official doc on how to map the Stored Procedure to your Context:

Stored Procedures in the Entity Framework

After the mapping you'll be able to call the Stored Procedure this way:

var val = _context.StoredProcedure2();

Solution 3

One option is to simply do this:

MyReturnEntity ret = context.Database
         .SqlQuery<MyReturnEntity>("exec myStoredProc ?, ?", param1, parm2);

Comments

[Erik Funkenbusch]

That will only work if using an EDMX file. It won't work with code first, which the asker hasn't said which version he's using.

[J.W.]

I posted a link which has nice utility to work with code first.

[Teo]

it doesn't work: "Additional information: Incorrect syntax near '?'."

[dont_trust_me]

@Teo He's referring to the name of the parameters in the procedure.