CODEWITHSUNDEEP
c#asp.net-mvclinqsql-server-2008entity-framework
user845405
user845405

Reputation: 1471

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

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

Upvotes: 9

Views: 59418

Answers (4)

Benjamin RD
Benjamin RD

Reputation: 12034

You can use this library: https://github.com/mrmmins/C-StoreProcedureModelBinding

Returns the values as a List, you only need create a simple class with the names and values types, like:

var productos = DataReaderT.ReadStoredProceadures<MyCustomModel>(myDbEntityInstance, "dbo.MySPName", _generic);

and MyCumtomModel class is something like:

public int id {get; set;}
public int salary {get; set;}
public string name {get; set;}
public string school {get; set;}

and generic like:

List<Generic> _generic = = new List<Generic>
                          {
                              new Generic
                                  {
                                      Key = "@phase", Type = SqlDbType.Int, Value = "207"
                                  }
                          }
                      };

And now, your products has the options like: products.First(), products.Count(), foreach etc.

Upvotes: 0

marc_s
marc_s

Reputation: 754598

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

Upvotes: 10

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93444

One option is to simply do this:

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

Upvotes: 2

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102428

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

Upvotes: 4

Related Questions