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