This topic is applicable to Entity Framework v1 - v6. It is not applicable to Entity Framework Core because Entity Developer generates ADO.NET code for invoking stored procedures in this case.

In this chapter we consider use of stored routines for insert/update/delete operations

• Database objects creation
• Devart Entity Developer
• Microsoft EDM Wizard and EDM Designer
• Creating New Entity Instance

Database objects creation

Using stored routines for insert/update/delete operations is a widely met task in developing applications, working with databases. And it is often necessary that insert operation should return primary key column values. Let's see, how it is done with Entity Framework.

Code of the stored routines:

CREATE PROCEDURE DEPT_UPDATE(pDEPTNO INT, pDNAME VARCHAR, pLOC VARCHAR) IS
BEGIN
  UPDATE DEPT
    SET DNAME = pDNAME,
    LOC = pLOC
    WHERE DEPTNO = pDEPTNO;
END;
/

CREATE PROCEDURE DEPT_DELETE(pDEPTNO INT) IS
BEGIN
  DELETE FROM DEPT
    WHERE DEPTNO = pDEPTNO;
END;
/

CREATE PROCEDURE DEPT_INSERT(pDNAME VARCHAR, pLOC VARCHAR, curParam OUT
SYS_REFCURSOR)
IS
  OUT_DEPTNO NUMBER;
BEGIN
  INSERT INTO DEPT(DNAME, LOC) VALUES(pDNAME, pLOC) RETURNING DEPTNO INTO
    OUT_DEPTNO;
  OPEN curParam FOR SELECT OUT_DEPTNO AS "OUT_DEPTNO" FROM DUAL;
END dept_insert;
/

Devart Entity Developer

1. Expand the connection node in the Database Explorer, then your schema node and then the Stored Procedures node.
2. Drag the DEPT_UPDATE, DEPT_DELETE, and DEPT_INSERT procedures to the storage model node in the Model Explorer to avoid automatic method creating.
3. Right-click the Dept class and then click Stored Procedure Mapping.
4. In the Commands list click Insert.
5. In the Stored Procedure list select the DEPT_INSERT stored procedure.
6. Click <Add Result Binding> in the Result Column Bindings grid and type OUT_DEPTNO.
7. Click the corresponding Property column cell of the Result Column Bindings grid, and select Deptno in the drop-down list.
   
   
   
   
   
8. The corresponding Dept properties are automatically mapped to the parameters in the grid below the Stored Procedure drop-down list because they have the same names. If the parameter names were different, you would need to map them by selecting the corresponding property from the drop-down list in the Property column.
9. In the Commands list click Update.
10. In the Stored Procedure list select the DEPT_UPDATE stored procedure.
11. The corresponding Dept properties are automatically mapped to the parameters in the grid below the Stored Procedure drop-down list because they have the same names. If the parameter names were different, you would need to map them by selecting the corresponding property from the drop-down list in the Property column.
    
    
    
    
    
12. In the Commands list click Delete.
13. In the Stored Procedure list select the DEPT_DELETE stored procedure.
14. The corresponding DeptNo Property is automatically mapped to the corresponding parameter in the grid below the Stored Procedure drop-down list.
    
    
    
15. Click OK.

Microsoft EDM Wizard and EDM Designer

        <Function Name="DEPT_DELETE" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT">
            <Parameter Name="PDEPTNO" Type="decimal" Mode="In" />
        </Function>
        <Function Name="DEPT_INSERT" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT">
            <Parameter Name="PDNAME" Type="VARCHAR2" Mode="In" />
            <Parameter Name="PLOC" Type="VARCHAR2" Mode="In" />
            <Parameter Name="CURPARAM" Type="REF CURSOR" Mode="Out" />
        </Function>
        <Function Name="DEPT_UPDATE" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT">
            <Parameter Name="PDEPTNO" Type="decimal" Mode="In" />
            <Parameter Name="PDNAME" Type="VARCHAR2" Mode="In" />
            <Parameter Name="PLOC" Type="VARCHAR2" Mode="In" />
        </Function>

1. Right-click the model and select Open With from the popup menu.
2. Click XML Editor in the Choose the program you want to use to open this file box and then click OK.
3. If the model was already opened in the model designer, Visual Studio will ask you whether to close the designer. Click Yes.
4. Add the devart:ResultSetParameterName attribute with the "curParam" value to the FUNCTION tag of DEPT_INSERT function, and remove its curParam Parameter tag. The result XML for the function should look like this:

           <Function Name="DEPT_INSERT" Aggregate="false" BuiltIn="false"
   NiladicFunction="false" IsComposable="false"
   ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT"
   devart:ResultSetParameterName="CURPARAM">
               <Parameter Name="PDNAME" Type="VARCHAR2" Mode="In" />
               <Parameter Name="PLOC" Type="VARCHAR2" Mode="In" />
           </Function>

5. Save the file and close it.
6. Reopen it in the ADO.NET EDM Designer.
7. Right-click the DEPT entity in the Model Browser window and choose Stored Procedure Mapping in the popup menu.
8. Click <Select Insert Function> and select DEPT_INSERT from the drop-down list.
9. Assign the corresponding Dept properties to the parameters in the grid below by selecting the corresponding property from the drop-down list in the Property column.
10. Click <Add Result Binding> in the Result Column Bindings node and type OUT_DEPTNO.
11. Choose Deptno in the drop-down list in the Property column of the Result Column Bindings node.
12. Click <Select Update Function> and select DEPT_UPDATE from the drop-down list.
13. Assign the corresponding Dept properties to the parameters in the grid below by selecting the corresponding property from the drop-down list in the Property column.
14. Click <Select Delete Function> and select DEPT_DELETE from the drop-down list.
    
    
    
    
15. Assign the Deptno property to the only parameter in the grid below by selecting the corresponding property from the drop-down list in the Property column.

Creating New Entity Instance

This example shows how to create a new entity instance and save it to database. When the entity is saved to the database, its Deptno field is updated. This new value is output to the console.

        using (SCOTTEntities context = new SCOTTEntities()) {
        Dept newDept = new Dept() { Dname = "New DNAME", Loc = "New LOC" };
        context.AddToDepts(newDept);
        context.SaveChanges();
        Console.WriteLine("DEPTNO: {0}", newDept.Deptno);
      }

	  
Dim context As SCOTTEntities
Using context = New SCOTTEntities
   Dim newDept As New Dept
   newDept.Dname = "New DNAME"
   newDept.Loc = "New LOC"
   context.AddToDepts(newDept)
   context.SaveChanges()
   Console.WriteLine("DEPTNO: {0}", newDept.Deptno)
End Using

	  

See Also

Entity Framework Tutorial  | Using Entity Data Model Wizard  | Stored Routines in Entity Framework Model  | Creating Database and Model  | Stored Procedure Returning Result Set using REF CURSOR Out Parameter  | Stored Function Returning REF CURSOR  | Mapping CUD Operations to Command Texts  | Working with Oracle Pipelined Functions