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 a database and model creation and their usage with ORM tools.

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

Database objects creation

In order to complete this walkthrough, you need to create the DEPT table and a set of stored routines after this we will create the Entity Framework model using Entity Developer or Microsoft EDM Wizard with this table in the model. See the script for the table and the routines below. In this walkthrough we will use the SCOTT schema for our database objects.

CREATE TABLE DEPT (
  DEPTNO NUMBER(9),
  DNAME VARCHAR2(20),
  LOC VARCHAR2(20),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);
/

CREATE SEQUENCE DEPT_SEQ;
/

CREATE TRIGGER FOR_DEPT
  BEFORE INSERT ON DEPT FOR EACH ROW
BEGIN
  SELECT DEPT_SEQ.NEXTVAL INTO :NEW.DEPTNO FROM DUAL;
END;
/

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

CREATE FUNCTION GET_DEPT_FUNC RETURN SYS_REFCURSOR
AS
 cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT *
      FROM DEPT
    ORDER BY DEPTNO;
  RETURN cur;
END;
/

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;
/

INSERT INTO DEPT (DNAME, LOC) VALUES ('ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DNAME, LOC) VALUES ('RESEARCH', 'DALLAS');
INSERT INTO DEPT (DNAME, LOC) VALUES ('SALES', 'CHICAGO');
INSERT INTO DEPT (DNAME, LOC) VALUES ('OPERATIONS', 'BOSTON');

Devart Entity Developer

After you have created these database objects, perform the following steps:

1. Choose Create From Database from the File menu.
2. The Database Reverse Engineering Wizard will appear. Click Entity Framework and then click the Next button.
3. Choose dotConnect for Oracle in the Provider box and set the connection parameters to connect to the database with the created objects. You can use Test Connection button to verify the entered parameters. Click the Next button.
   
   
   
   
4. Expand the database node in the Choose source tree and then the Tables node. Select the DEPT table node check box and click Next.
   
   
   
   
5. Set naming options for classes and properties. Click Next.
6. Specify the namespace for the generated classes, enter name for Entity Container, and click the Next button.
   
   
   
   
7. Clear the Run generator for model check box if it is selected and click the Finish button.

Microsoft EDM Wizard and EDM Designer

Perform the following steps to create such model in Visual Studio:

1. Create your project in the Visual Studio.
2. Right-click it in the Solution Explorer and choose Add -> New Item from the popup menu.
3. Click ADO.NET Entity Data Model in the Templates box, specify the name for the model, and click Add.
   
   
   
   
4. Click Generate from database and then click Next.
   
   
   
5. Click the New Connection button.
6. In the appeared Choose Data Source Dialog Box click Oracle Database in the Data source list and select dotConnect for Oracle in the Data provider drop-down list, then click OK.
   
   
   
7. Specify the connection parameters and click OK.
8. Specify whether to include the sensitive data in the connection string and click Next.
   
   
   
9. Select the check boxes for the DEPT table and GET_DEPT_PROC, GET_DEPT_FUNC, DEPT_UPDATE, DEPT_DELETE, DEPT_INSERT stored procedures.

   Note:
   For Entity Developer we did not select the stored routines because in Entity Developer it is very easy to add them later. In Microsoft EDM Designer you may use the Update Wizard to add a stored procedure into an existing model, but remember that Update Wizard overwrites the storage model and may discard your manual changes. So, it is better to add everything we need when creating model.

   
   
   
   
   
10. Specify the Model Namespace and click Finish.

See Also

Entity Framework Tutorial  | Using Entity Data Model Wizard  | Stored Routines in Entity Framework Model  | Stored Procedure Returning Result Set using REF CURSOR Out Parameter  | Stored Function Returning REF CURSOR  | Mapping CUD Operations to Stored Routines