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 the previous chapter we have considered mapping CUD operations to stored routines, which is very useful when you need the insert operation to return primary key values or other column values and in other cases. However, sometimes there are no such stored routines in the database, and adding ones is not allowed or is undesirable. In this case you may emulate the routines with PL/SQL blocks in the SSDL part of your model instead of creating actual stored routines.

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

Devart Entity Developer

1. Expand the storage node (<ModelName>.Store)
2. Right-click the Stored Procedures node, then point to Add and click New Command Text.

3. Paste the following code to the SQL Script box:

   DECLARE
     a ROWID;
   BEGIN
     INSERT INTO dept (deptno, dname)
     VALUES (:p0, :p1)
     RETURNING ROWID INTO a;
   
     OPEN :curparam FOR SELECT loc FROM dept WHERE ROWID=a;
   END;

4. Click the Parameters tab.
5. You will be asked whether to regenerate the parameters collection. Click Yes.
6. Click the p0 parameter in the Parameter Name list and select Int in the Data Type list.
7. Click the curparam parameter in the Parameter Name list and then click Remove.
8. Click OK.
9. Right-click the Dept class and then click Stored Procedure Mapping.
10. In the Commands list click Insert.
11. In the Stored Procedure list select the Procedure1 command text.
12. Click the corresponding Property column cell in the p0 row of tthe grid under Storage Procedure and select Deptno in the drop-down list.
13. Click the corresponding Property column cell in the p1 row of the grid under Storage Procedure and select DName in the drop-down list.
14. Click <Add Result Binding> in the Result Column Bindings grid and type LOC.
15. Click the corresponding Property column cell in the Result Column Bindings grid and select LOC in the drop-down list.
    
    
    
16. Click OK.

Microsoft EDM Wizard and EDM Designer

We need to perform some XML code changes manually to use this procedure in our model. We will add our command text function to it.

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 following Function element to the Schema element of the edmx:StorageModels tag.

           <Function Name="Procedure1" devart:ResultSetParameterName="curparam" IsComposable="false" BuiltIn="false" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion">
             <CommandText>declare
     a rowid;
   begin
     INSERT INTO dept (deptno, dname)
     VALUES (:p0, :p1)
     RETURNING rowid INTO a;
   
     open :curparam for select loc from dept where rowid=a;
   end;</CommandText>
             <Parameter Name="p0" Type="int" Mode="In" />
             <Parameter Name="p1" Type="NVARCHAR2" Mode="In" />
           </Function>

   Note that the following attribute must be added to the Schema element of the edmx:StorageModels tag, as we did in the Stored Procedure Returning Result Set using REF CURSOR Out Parameter chapter:

   xmlns:devart="http://devart.com/schemas/edml/StorageSchemaExtensions/1.0"

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 type in the name of the created command text - Procedure1.
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 LOC.
11. Choose LOC in the drop-down list in the Property column of the Result Column Bindings node.

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", Deptno = 2020 };
        context.AddToDepts(newDept);
        context.SaveChanges();
        Console.WriteLine("LOC: {0}", newDept.Loc);
      }

 
Dim context As SCOTTEntities
Using context = New SCOTTEntities
   Dim newDept As New Dept
   newDept.Dname = "New DNAME"
   newDept.Deptno = 2020
   context.AddToDepts(newDept)
   context.SaveChanges()
   Console.WriteLine("LOC: {0}", newDept.Loc)
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 Stored Routines  | Working with Oracle Pipelined Functions