Entity Framework 5.0.0 Function Import and implicit REF CURSOR Binding

Source

Summary: 1, how to use the Function Import feature in the Entity Framework.

2, shows how to use Odp.net's implicit REF cursor binding (implicit REF cursor binding).

Environment and tools:

Windows 10 Enterprise Edition

Microsoft Visual Studio Enterprise 2015

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

. NET Framework 4.0

Odp.net (Nuget package Id:Oracle.ManagedDataAccess; Nuget package version:12.1.2400)

Entity Framework (Nuget package id:entityframework; Nuget package version:5.0.0)

Additional requirements: HR schema access rights in Oracle database.

1, create a WinForm project name of Functionimporttest,. NET version is 4.0.

2. Open the NuGet Package Manager console.

Execute each of the following two commands to install the EF and odp.net packages in turn:

　　Install-package-id entityframework-version 5.0.0-projectname Functionimporttest

Install-package-id Oracle.manageddataaccess-projectname Functionimporttest

3, create a stored procedure inside the HR scenario in the Oracle database.

This stored procedure has an out-of-parameter type of an implicit cursor variable.

1 CREATE OR REPLACE PROCEDURE" HR". " Proc_get_emp_by_dept_name "2 (3Dept_nameinch VARCHAR2 DEFAULT NULL,4 Cur_emps out Sys_refcursor5) as6Sql_stmtVARCHAR2( the) := 'SELECT t1.first_name, T1.last_name, t2.department_name from Employees T1' ||7                               'JOIN Departments t2 on t1.department_id = t2.department_id';8 BEGIN9     IFDept_name is  not NULL  ThenTenSQL_STMT:=Sql_stmt|| 'WHERE t2.department_name =' || " '" ||Dept_name|| " '"; One     END IF; A  -     OPENCur_emps forsql_stmt; - END"Proc_get_emp_by_dept_name";

4. Generating a conceptual model from a database

(1), right click on the project name and select Add-->add item. Select the ADO Data Model and set the name to Hrmodel, then click Add.

　

(2), select Generate model from database, click Next.

(3), select Database connection. This article uses the HR scenario in the Oracle database.

(4), select the Entity Framework version.

(5), select database objects and settings. Select the hr.proc_get_emp_by_dept_name stored procedure that you have created above and click Finish.

5, the stored procedures in Oracle are already imported. The parameter cur_emps of the stored procedure is then mapped to a specific class.

The import looks like the following:

6. Configure the app. Config file to set the metadata information for the stored procedure's implicit cursor parameters.

The following is the configuration of the Oracle.manageddataaccess.client section of the App. Config file. When setting property values, vs basically has hints.

1 <oracle.manageddataaccess.client>2         <version Number="*">3             <datasources>4                 <DataSourcealias= "Sampledatasource"Descriptor= "(Description= (address= (protocol=tcp) (Host=localhost) (port=1521)) (Connect_data= (SERVICE_NAME=ORCL)))"  />5             </datasources>6             <Implicitrefcursor>7                 <!-- 8 Note: The schema name and the name of the stored procedure are case-sensitive. 9 Add &quot; If you want to preserve the lowercase letters in the scheme name and stored procedure name. Ten For example <storedprocedure schema= "&quot; schemaname&quot; "Name=" &quot; storedprocedurename&quot; " > One Otherwise, the EF framework converts these names to uppercase by default.  A                  - -                 <StoredProcedureSchema= "HR"name= "Proc_get_emp_by_dept_name"> -                     <!--the name of the cursor parameter is case-sensitive.  - the                     <Refcursorname= "Cur_emps"> -                         <BindinfoMode= "Output"/> -                         <!--the ordinal number of the parameter starts at 0 - -                         <metadataColumnName= "First_Name"columnordinal= "0"ColumnSize= " the"Nativedatatype= "Varchar2"ProviderType= "Varchar2"Providerdbtype= "String"DataType= "System.String" /> +                         <metadataColumnName= "Last_Name"columnordinal= "1"ColumnSize= "+"Nativedatatype= "Varchar2"ProviderType= "Varchar2"Providerdbtype= "String"DataType= "System.String" /> -                         <metadataColumnName= "Department_name"columnordinal= "2"ColumnSize= "+"Nativedatatype= "Varchar2"ProviderType= "Varchar2"Providerdbtype= "String"DataType= "System.String" /> +                     </Refcursor> A                 </StoredProcedure> at             </Implicitrefcursor> -         </version> -     </oracle.manageddataaccess.client>

7, Map function Import to type.

(1), modify function Import. Switch to the Model browser window. Right-click on the name of the stored procedure you just imported under function imports and click Edit.

(2), modify the function import and generate the Complext Type.

① Modify function Import Name.

② Select Returns a Collection of complex below.

③ Click Get Column information.

④ Click Create New Complex Type.

⑤ Modify the name of the complex type to employeebasicinfo.

⑥ Click OK.

At this point, Complex types has one more type below it.

8, call the Getempbydeptname method to get the data, and then bind with the DataGridView control to display the data.

Entity Framework 5.0.0 Function Import and implicit REF CURSOR Binding