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

c# entity-framework stored-procedures

Question

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;
1
12
9/16/2016 8:37:01 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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

5
9/16/2016 8:43:56 AM

Related Questions

In EF Code First and SQL 2005, what causes "Incorrect syntax at stored procedure name>"?

You only need EXEC if there are any statements before "GetDataFromMySp" in the batch. A stored procedure name by itself should be OK....I'd run SQL Profiler to see what is being actually being sent by EF....Things like "prepared statements" issue extra commands as part of the batch in addition to your command text....See this: ...http://social.msdn...
code-first ef-code-first entity-framework sql-server-2005 stored-procedures
asked by gbn

First, in EF4.1, create a stored procedure with an output parameter.

SqlQuery... works with output parameters but you must correctly define SQL query and setup ...SqlParameter...s. Try something like:...var outParam = new SqlParameter(); outParam.ParameterName = "TotalRows"; outParam.SqlDbType = SqlDbType.Int; outParam.ParameterDirection = ParameterDirection.Output; var data = dbContext.Database.SqlQuery<MyType>("s...
ef-code-first entity-framework parameters stored-procedures
asked by Ladislav Mrnka

Code-First Entity Framework w/ Stored Procedure returning results from complex Full-text Searches

Posting this as an answer rather than an edit to my question:...Taking some of the insight provided by @Drauka's (and google) here is what I did for my initial iteration....Created the stored procedure to do the full text searching. It was really too complex to be done in EF even if supported (as one example some of my entities are related via busi...
c# entity-framework full-text-search stored-procedures
asked by Matthew

Database.SqlQuery calling stored procedure that has multiple output parameters

The method you are trying to use only works for results of a query. It can't put the values of the output parameters into a new object for you automatically....You have to create the parameters explicitly and read their values after the stored procedure has been run....So if you have a stored procedure like this:...CREATE PROCEDURE dbo.insertemploy...
code-first entity-framework
asked by Peter Hansen

Entity framework and stored procedure with output parameter returning row (EF will receive complex type)

You can try with ...static void Main(string[] args) { using (SchoolEntities context = new SchoolEntities()) { var outputParameter = new ObjectParameter(“sum”, typeof(decimal)); context.SchoolBudgetForDateRange(new DateTime(2007, 1, 1), new DateTime(2008, 1, 1), outputParameter); Console.WriteLine(outputParameter.Va...
complextype entity-framework-6 output-parameter row stored-procedures
asked by Estuardo Arévalo

Executing a stored procedure that returns a result set and has output parameters with Entity Framework

It turns out that the output parameters aren't available until all results are read. I fixed my problem by appending ....ToList()... to the tend of the ...ExecuteFunction()... call. I figured it out by browsing the metadata in Visual Studio. You can see the relevant documentation here: ...I don't see this mentioned in the actual documentation on...
c# entity-framework entity-framework-6 stored-procedures
asked by Tim Coker

EF 6 Code First From Database - Calling existing stored procedure

You need to define a DTO for each stored procedure. You just create simple classes where the propery names of the classes match the name of the columns that are returned by the stored procedure. So, when you call the Stored Procedure from EF, your objects are built and populated. The performance is great! Here I'm pasting an example:...dbContext.Da...
c# entity-framework entity-framework-6 sql-server
asked by Francisco Goldenstein

Incorrect Syntax Near '0' - when using sp_executeSQL to execute stored procedure with output parameter

In SSMS (T-SQL) you cannot use parameter place-holders, you need to state them explicitly:...exec sp_executesql N'EXEC [dbo].[ApplyList] @oldlist, @username, @newlist, @errormessage output', N'@oldlist int,@username nvarchar(50), @newlist int,@errormessage varchar(2000) output', @oldlist='20',@username=N'mk',@newlist='18',@errormessage...
entity-framework-6 sp-executesql sql-server
asked by Alex K.

Calling Oracle stored procedure using Entity Framework with output parameter?

In this case, you shouldn't be calling:...var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam); ...But instead be calling:...var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam); ...Notice that the onl...
c# entity-framework entity-framework-6 oracle oracle-manageddataaccess
asked by Jeff

how to call stored procedure in EF6 with output parameters

You can implement the following to retrieve the data for a stored procedure call ...using(var db = new iConext()) { var details = db.Database.SqlQuery<iType>("exec iProc @param", new SqlParameter("@param", iValue)); } ...iType... is int or string or long or can be a ComplexType...@param... is one or more required parameters...iContext... is fo...
asp.net-mvc entity-framework-6 output-parameter stored-procedures string
asked by Pradip

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow