Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to retrieve output parameter from stored procedure by EF code first

i am new in EF and working with EF code first. just got a link https://code.msdn.microsoft.com/How-to-retrieve-output-e85526ba which show how to use read output type param by EF db first. so anyone tell me how to retrieve output parameter from stored procedure by EF code first ?

if possible give me small sample code or redirect me to relevant articles.

thanks

I got a solution

var outParam = new SqlParameter();
outParam.ParameterName = "TotalRows";
outParam.SqlDbType = SqlDbType.Int;
outParam.ParameterDirection = ParameterDirection.Output;

var data = dbContext.Database.SqlQuery<MyType>("sp_search @SearchTerm, @MaxRows, @TotalRows OUT", 
               new SqlParameter("SearchTerm", searchTerm), 
               new SqlParameter("MaxRows", maxRows),
               outParam);
var result = data.ToList();
totalRows = (int)outParam.Value;
like image 423
Monojit Sarkar Avatar asked Sep 16 '16 08:09

Monojit Sarkar


People also ask

How do I call a stored procedure in code first approach?

To use a Stored Procedure with the Code First model, we need to override the OnModelCreating method of DBContext and add the following code to map the Stored Procedure. The MapToStoreProcedures method has two overloaded methods, one method is without a parameter.

Can stored procedure have output parameter?

A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.

Can a stored procedure return an output value to its caller?

If you specify the output keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits.

How do I get the output of a stored procedure?

To create an output parameter for a stored procedure, you use the following syntax: parameter_name data_type OUTPUT. A stored procedure can have many output parameters. In addition, the output parameters can be in any valid data type e.g., integer, date, and varying character.

How do you call a stored procedure in SQL with parameters?

Code language:SQL (Structured Query Language)(sql) Calling stored procedures with output parameters To call a stored procedure with output parameters, you follow these steps: First, declare variablesto hold the values returned by the output parameters Second, use these variables in the stored procedure call.

Why does my stored procedure return a different value for each entity?

Sometimes the stored procedures will return data that doesn't map to existing entities. It might return only a subset of the data required for an entity or it might return a scalar value.

How do I generate a stored procedure in Entity Framework?

Generating stored procedures. Entity Framework can be used to generate simple non-query procedures for entities if you prefer to use those rather than the DbSet's Add and Remove methods. You enable this feature as part of the migration configuration by using the MapToStoredProcedures method.


1 Answers

To retrieve the data for a stored procedure call, you can use the following

using(var db = new YourConext())
{
       var details = db.Database.SqlQuery<YourType>("exec YourProc @p", 
                      new SqlParameter("@p", YourValue));
}

YourType: might be int or string or long or even a ComplexType

@p: in case if the stored procedure has parameters and you can define as many as you need from parameters

if you need more information about SqlQuery , you might check the following

  1. Writing SQL queries for entities
  2. Entity Framework Code First and Stored Procedures

Hope this will help you

like image 55
Monah Avatar answered Sep 18 '22 15:09

Monah