Hi
I'm calling stored procedures in postgresql using entity framework.
The stored procedure signature is below:
CREATE OR REPLACE FUNCTION cbill.insert_service_order(p_service_order_id integer, p_product_order_id integer, p_milestone_id integer, p_service_type integer, p_transaction_type_id integer, p_user character varying, p_service_id integer, p_package_id integer, p_invoice_type_id integer,
p_costcentre_id integer, p_service_name character varying, p_contract_term integer)
RETURNS integer AS
If you notice it returns an integer.
I have mapped the model to call this stored procedure on the insert which generated the following in the "Create" method of my controller
[HttpPost]
public ActionResult Create(ServiceOrder serviceorder)
{
if (ModelState.IsValid)
{
db.ServiceOrders.AddObject(serviceorder);
db.SaveChanges();
return RedirectToAction("Index", "ServiceOrderAttribute", new
{
serviceOrderId = serviceorder.Id
});
}
How do i retrieve the returned integer from the function? I have tried serviceorder.Id after adding the object and saving changes, however that returns 0.
Note: I have set storedGenerated to "identity" as i'm generating the entity key value within the stored procedure.
Thanks
Getting return value from postgresql stored procedure
-
- Posts: 15
- Joined: Sat 26 Nov 2011 00:56
Here is a simple walkthrough:
1. Create database objects
2. Add *.edml (ObjectContext template) into your project with dept table and insert_into_dept function.
3. Open *.edml in Entity Developer designer:
- set the Concealed Function to True for insert_into_dept in Store part of your model in Model Explorer
- remove the p_deptno parameter in Model Explorer > Store > Stored Procedures > insert_into_dept (because it is OUT parameter)
- right click on Dept in designer > Configure Behaviour and set
Behaviour: Insert
----
Stored Procedure: insert_into_dept
the p_dname parameter corresponds to the Dname property
the p_loc parameter corresponds to the Loc property
----
Result Column Bindings:
the p_deptno column in result set corresponds to the Deptno property
----
- save the model
4. Run this code:
1. Create database objects
Code: Select all
CREATE TABLE DEPT (
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
CREATE OR REPLACE FUNCTION insert_into_dept(p_dname varchar, p_loc varchar, p_deptno OUT integer) RETURNS integer AS $$
BEGIN
p_deptno := 1;
insert into dept values (p_deptno, p_dname, p_loc);
END;
$$ LANGUAGE plpgsql;
3. Open *.edml in Entity Developer designer:
- set the Concealed Function to True for insert_into_dept in Store part of your model in Model Explorer
- remove the p_deptno parameter in Model Explorer > Store > Stored Procedures > insert_into_dept (because it is OUT parameter)
- right click on Dept in designer > Configure Behaviour and set
Behaviour: Insert
----
Stored Procedure: insert_into_dept
the p_dname parameter corresponds to the Dname property
the p_loc parameter corresponds to the Loc property
----
Result Column Bindings:
the p_deptno column in result set corresponds to the Deptno property
----
- save the model
4. Run this code:
Code: Select all
PostgreEntities ent = new PostgreEntities();
Dept d = new Dept();
d.Dname = "a";
d.Loc = "a";
ent.AddToDepts(d);
ent.SaveChanges();
Console.WriteLine(d.Deptno); // to make sure that the value is returned into d.Deptno