Entity Framework 5.0.0 Function Import and ODP. NET Implicit ref cursor Binding introduction, 5.0.0odp

Source: Internet
Author: User
Tags windows 10 enterprise

Entity Framework 5.0.0 Function Import and ODP. NET Implicit ref cursor Binding introduction, 5.0.0odp

Source code

Summary: 1. describes how to use the function import function in Entity Framework.

2. describes how to use the implicit ref cursor binding (implicit ref cursor binding) of ODP. NET ).


Environment and tools:

Windows 10 Enterprise Edition

Microsoft Visual maxcompute Enterprise 2015

Oracle Database 11g Enterprise Edition Release

. 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)


Other requirements: permission to access the HR schema in the Oracle database.


1. Create a Winform project named FunctionImportTest. the. NET version is 4.0.

2. Open the Nuget Package Manager Console.

Run the following commands to install the EF and ODP. NET packages respectively:

Install-package-id EntityFramework-version 5.0.0-projectname FunctionImportTest

Install-package-id Oracle. ManagedDataAccess-projectname FunctionImportTest

3. Create a stored procedure in the HR solution of the Oracle database.

This stored procedure has an output parameter of the implicit type cursor variable.

 1 CREATE OR REPLACE PROCEDURE "HR"."PROC_GET_EMP_BY_DEPT_NAME" 2 ( 3     DEPT_NAME IN VARCHAR2 DEFAULT NULL, 4     CUR_EMPS  OUT SYS_REFCURSOR 5 ) AS 6     SQL_STMT VARCHAR2(256) := '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 BEGIN 9     IF DEPT_NAME IS NOT NULL THEN10         SQL_STMT := SQL_STMT || 'WHERE t2.department_name = ' || '''' || DEPT_NAME || '''';11     END IF;12 13     OPEN CUR_EMPS FOR SQL_STMT;14 END "PROC_GET_EMP_BY_DEPT_NAME";

4. Generate a conceptual model from the database

(1) Right-click the project name and choose Add --> Add Item. Select ADO. NET Entity Data Model, Set Name To HRModel, and click Add.


(2) Select generate a model from the database and click NEXT.

(3) select database connection. This article uses the HR solution in the Oracle database.

(4) Select the Entity Framework version.

(5) select database objects and settings. Select the created HR. PROC_GET_EMP_BY_DEPT_NAME stored procedure and click FINISH.

5. The Stored Procedure in Oracle has been imported. Then, map the output parameter CUR_EMPS of the stored procedure to a specific class.

The imported image is shown in:

6. Configure the app. config file and set the metadata of the implicit cursor parameter of the stored procedure.

The following is the configuration of oracle. manageddataaccess. client in the app. config file. When setting the attribute value, VS prompts are displayed.

1 <oracle. manageddataaccess. client> 2 <version number = "*"> 3 <dataSources> 4 <dataSource alias = "SampleDataSource" descriptor = "(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = localhost) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = ORCL) "/> 5 </dataSources> 6 <implicitRefCursor> 7 <! -- 8 Note: The solution name and stored procedure name are case sensitive. 9 if you want to retain the solution name and the lower-case letters in the stored procedure name, add & quot ;. 10 For example, <storedProcedure schema = "& quot; SchemaName & quot;" name = "& quot; StoredProcedureName & quot;"> 11. Otherwise, the EF framework converts these names to uppercase by default. 12 --> 13 <storedProcedure schema = "HR" name = "PROC_GET_EMP_BY_DEPT_NAME"> 14 <! -- The name of the cursor parameter is case sensitive. --> 15 <refCursor name = "CUR_EMPS"> 16 <bindInfo mode = "Output"/> 17 <! -- The ordinal number of the parameter starts from 0 --> 18 <metadata columnName = "FIRST_NAME" columnOrdinal = "0" columnSize = "20" nativeDataType = "Varchar2" providerType = "Varchar2" providerDBType =" string "dataType =" System. string "/> 19 <metadata columnName =" LAST_NAME "columnOrdinal =" 1 "columnSize =" 25 "nativeDataType =" Varchar2 "providerType =" Varchar2 "providerDBType =" String "dataType =" system. string "/> 20 <metadata columnName =" DEPARTMENT_NAME "columnOrdinal =" 2 "columnSize =" 30 "nativeDataType =" Varchar2 "providerType =" Varchar2 "providerDBType =" String "dataType =" system. string "/> 21 </refCursor> 22 </storedProcedure> 23 </implicitRefCursor> 24 </version> 25 </oracle. manageddataaccess. client>

7. map function import to type.

(1) Modify function import. Switch to the Model Browser window. Right-click the name of the imported stored procedure under Function Imports and click Edit.

(2) Modify function import and generate Complext Type.

① Modify the Function Import Name.

② Select Complex under Returns a Collection.

③ Click Get Column Information.

④ Click Create New Complex Type.

⑤ Change the name of Complex Type to EmployeeBasicInfo.

6. Click OK.

In this case, Complex Types has one more type.

8. Call the GetEmpByDeptName method to obtain data and bind it to the DataGridView control to display the data.




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.