CODEWITHSUNDEEP
c#entity-frameworkstored-procedures
Monojit Sarkar
Monojit Sarkar

Reputation: 2451

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;

Upvotes: 13

Views: 19009

Answers (2)

Monojit Sarkar
Monojit Sarkar

Reputation: 2451

This way we can also call a stored procedure without using exec command.

using (var context = new BloggingContext()) 
{ 
    var blogs = context.Blogs.SqlQuery("dbo.GetBlogs").ToList(); 
}

You can also pass parameters to a stored procedure using the following syntax:

using (var context = new BloggingContext()) 
{ 
    var blogId = 1; 

    var blogs = context.Blogs.SqlQuery("dbo.GetBlogById @p0", blogId).Single(); 
}

Upvotes: -1

Monah
Monah

Reputation: 6794

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

Upvotes: 4

Related Questions