dotConnect for Oracle Documentation
In This Topic
    Stored Procedure Returning Result Set using REF CURSOR Out Parameter
    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 an usage of stored procedures with ORM tools.


    Procedure creation

    Code of the procedure:

    CREATE PROCEDURE GET_DEPT_PROC(curParam OUT SYS_REFCURSOR)
    IS
    BEGIN
     OPEN curParam FOR
     SELECT *
      FROM DEPT
      ORDER BY DEPTNO;
    END;

    Devart Entity Developer

    Expand the connection node in the Database Explorer, then your schema node and then the Stored Procedures node. Drag the GET_DEPT_PROC to the diagram sheet. The function will be created in the storage model, its REF CURSOR parameter is hidden and its ResultSetParameterName attribute is automatically set to the needed parameter. Corresponding method will be automatically created in the conceptual model. However, you need to set the return type for it manually. To do this perform the following actions:

    1. Double-click the method in the Model Explorer.
    2. Select DEPT from the Value Types drop-down list.

      methodeditor1

    3. Click OK.

    You also may drag the procedure from Database Explorer to the storage model node in the Model Explorer. In such case only the storage model is updated, the corresponding method is not created. To create corresponding method, drag the procedure from the Model Explorer to the diagram sheet or right-click it in the Model Explorer and select Create Method from the popup menu.

    Microsoft EDM Wizard and EDM Designer

    After we have added GET_DEPT_PROC procedure to the model, the following XML was generated for it.
         
    <Function Name="GET_DEPT_PROC" Aggregate="false" BuiltIn="false"
    NiladicFunction="false" IsComposable="false"
    ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT">
                <Parameter Name="CURPARAM" Type="REF CURSOR" Mode="Out" />
            </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.

      edm_openwith

    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 following attribute to the Schema tag, which is in the Storage Model:
      xmlns:devart="http://devart.com/schemas/edml/StorageSchemaExtensions/1.0"
    5. Add the devart:ResultSetParameterName attribute with the "CURPARAM" value to the FUNCTION tag of the GET_DEPT_PROC function, and remove its CURRPARAM Parameter tag. The result XML for the function should look like this:
      <Function Name="GET_DEPT_PROC" Aggregate="false" BuiltIn="false"
      NiladicFunction="false" IsComposable="false"
      ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT"
      devart:ResultSetParameterName="CURPARAM">
              </Function>
    6. Save the file and close it.
    7. Reopen it in the ADO.NET EDM Designer.
    8. Locate the GET_DEPT_PROC in the Model Browser. Right-click it and select Create Function Import from the popup menu.
    9. In the Add Function Import dialog box click Entities and then select DEPT from the Entities drop-down list. You also may change the Function Import Name.

      edm_addfunctionimport

    10. Click OK.

    Using the Method

    This example illustrates call of the method, retrieving the collection of entities, and entity output to console. Object context name and case of the Depts class and its fields may differ in your case.

        using (SCOTTEntities context = new SCOTTEntities()) {
           var depts = context.GetDeptProc();
           foreach (Dept item in depts)
               Console.WriteLine("Dept {0}: '{1}', '{2}'", item.Deptno, item.Dname, item.Loc);
        }

    
            Using context = New SCOTTEntities
                Dim depts = context.GETDEPTPROC()
                For Each dept As DEPT In depts
                    Console.WriteLine("Dept {0}: '{1}', '{2}'", dept.DEPTNO, dept.DNAME, dept.LOC)
                Next
            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 Function Returning REF CURSOR  | Mapping CUD Operations to Stored Routines