I have an existing database with lots of complex stored procedure and I want to use those procedure through EF 4. I have done the following:
Customer
.MyFunction
, complex type.Resulting code:
CustomerEntities entity = new CustomerEntities();
var result = entity.MyFunction("XYZ", ref o_MyString);
Now my stored procedure has an output parameter which I used to call by the ref (in WebForm). But I am getting the below error:
cannot convert from 'ref string' to 'System.Data.Objects.ObjectParameter'
Please help
Edit
When I am trying to save I am getting the below error
A mapping function binding specifies a function Model.Store.P_GetCustomer with an unsupported parameter: o_MyString. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.
The Output Parameters in Stored Procedures are used to return some value or values. 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.
Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module.
To call a stored procedure with output parameters, you follow these steps: First, declare variables to hold the values returned by the output parameters. Second, use these variables in the stored procedure call.
Output parameters are returned in ObjectParameter
instance. So you must use code like:
var oMyString = new ObjectParameter("o_MyString", typeof(string));
var result = ctx.MyFunction("XYZ", oMyString).ToList();
var data = oMyString.Value.ToString();
The reason is that function import cannot use ref parameter because output parameter is not filled until you process result set from the database = if you don't call ToList
or iterate the result of the stored procedure the output parameter is null.
msdn suggests the following:
CREATE PROCEDURE dbo.GetDepartmentName @ID INT , @Name NVARCHAR(50) OUTPUT AS SELECT @Name = Name FROM Department WHERE DepartmentID = @ID
Solution
using (SchoolEntities context = new SchoolEntities()) { // name is an output parameter. ObjectParameter name = new ObjectParameter("Name", typeof(String)); context.GetDepartmentName(1, name); Console.WriteLine(name.Value); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With