dotConnect for Oracle Documentation
In This Topic
    Mapping CUD Operations to Stored Routines
    In This Topic

    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

    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.


      Stored Procedure Mapping - Insert

    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.


      Stored Procedure Mapping - Update

    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.

      Stored Procedure Mapping - Delete
    15. Click OK.

    Microsoft EDM Wizard and EDM Designer

    After we have added DEPT_UPDATE, DEPT_DELETE, and DEPT_INSERT procedures to the model, the following XML was generated for them.
            <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>
    We need to perform some XML code changes manually to use this procedure in our model.
    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.

      edm_mappingdetails

    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
    
    	  
    

    Previous chapter | Content | Next chapter

    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