Using arguments to call a stored procedure

c# entity-framework

Question

I'm having trouble getting a stored method that returns a value rather than a dataset to work with EF4.

This is what I've seen: http://dotnet.dzone.com/news/how-retrieve-stored-procedure

What I did was import a function and add a procedure to the model.

The first difficulty I encountered was the names of the parameters in my process, which are@_Parameter_in . That prompted EF to include them asp_Parameter_in because using an underscore as the first character might create problems. Following that, I can see in the SQL Profiler call that the stored procedure searches for@p_Parameter_in and naturally there was a problem with it.

I changed the parameter names and checked SQL Trace; everything seems to be operating as it should. The issue is that I'm unable to extract the value. This is how my code appears:

System.Data.Objects.ObjectParameter newKey = new System.Data.Objects.ObjectParameter("NewKey_out", typeof(Int32));
newKey.Value = 0;
context.GetNextSurrogateKey_v2("tTest", newKey);

After a callnewKey.Value always has the value I set it to or 0. It doesn't restore the value. My problem could be with the way I imported the function, I think. I use scalers and data types.Int32 . For some reason, "Create new Complex type" is not enabled for me. Anybody had that issue?

1
8
9/9/2016 3:46:02 PM

Accepted Answer

Since I can't see the stored procedure you're calling, I'm assuming here. Instead of obtaining the value of an output parameter as specified in the stored procedure, I believe you are attempting to access a scalar value that the stored method has already returned.

In the EDM, I think you want to create a new Function and direct it to the stored procedure. Google quickly yields the following probable answer: http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

HTH

7
2/21/2011 4:56:37 PM

Popular Answer

Because EF processes output parameters after datareader has finished reading, this is an issue. Typically, it comes after invoking the DataBind() method. I had the same issue with extended processing times. I resolve this by utilizing the ObjectResult's.ToList() method.

var rowsCount = new ObjectParameter("RowsCount", typeof(Int32));    
var result = db.GetProductList(pageSize, pageNumber, rowsCount).ToList();
this.ProductsCount = (int?)rowsCount.Value;

You can probably fix single rows or values using the FirstOrDefault() method.



Related Questions





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