CODEWITHSUNDEEP
c#entity-framework
katit
katit

Reputation: 17915

Calling stored procedure with parameters

I have a stored procedure that returns a value, not a dataset, and I have problems getting it to work with EF4.

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

Here is what I did: I added a procedure to the model, and imported a function.

The first issue that I had was the fact that parameters in my procedure are named like @_Parameter_in. That made EF to bring them in as p_Parameter_in because it wouldn't work with an underscore as the first character. Then when I called the stored procedure, I can see in the SQL Profiler call that it looks for @p_Parameter_in, and of course there was an issue with that.

Now I renamed the parameters and looked into SQL Trace - everything looks and works great. The problem is that I can't get the value out. Here is how my code looks:

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

After calling newKey.Value, it's always 0 or whatever value I set it to. It doesn't bring the value back. I suspect my issue is with how I import the function. I use Scalars and data type Int32. "Create new Complex type" is disabled for me for some reason. Anybody had that issue?

Upvotes: 8

Views: 14948

Answers (3)

Bindum
Bindum

Reputation: 91

I had the same issue as Jan Remunda described. Someone changed the return type from Integer to Entity.Core.Objects.ObjectResult(of Integer?) which resulted in always returning nothing.

For us the solution was to force EF to read the return of the stored proc by adding the .FirstOrDefault() function to the return.

Upvotes: 0

Jan Remunda
Jan Remunda

Reputation: 7930

The problem is that EF process output parameters after datareader ends reading. That's usually after calling DataBind() function. I had same problem with longer processing procedures. I solve this by using .ToList() function on ObjectResult.

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

Single rows or values can you probably solve with FirstOrDefault() function.

Upvotes: 2

Dave
Dave

Reputation: 3621

I'm guessing here since I can't see the stored procedure that you're calling. I think you're wanting to retrieve a scalar value that has been returned by the stored procedure, rather than the value of an output parameter as defined in the stored procedure.

I believe you want to define a new Function within the EDM and point this at the stored procedure. A quick google has this potential solution: http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

HTH

Upvotes: 7

Related Questions