This tutorial describes how to build ODP.NET, Managed Driver applications. You will learn:
How to develop a basic ODP.NET, Managed Driver application from start to finish.
How to deploy ODP.NET, Managed Driver applications, which have notable setup differences from ODP.NET, Unmanaged Driver applications.
How to use ODP.NET, Managed Driver from Oracle Developer Tools for Visual Studio.
How to convert an existing unmanaged ODP.NET application to a managed ODP.NET application.
Time to Complete
Approximately 30 minutes
Background
Oracle Data Provider for .NET (ODP.NET) is an implementation of an ADO.NET data provider for Oracle Database. It was first released as a driver containing both managed and unmanaged code, also known as ODP.NET, Unmanaged Driver. To ease and simplify deployment, Oracle introduced a 100% .NET provider called ODP.NET, Managed Driver.
ODP.NET, Managed Driver APIs are a subset of the unmanaged driver APIs. This makes learning to use or migrate to the managed driver simple for existing unmanaged ODP.NET developers. Many developers will have an easier deployment experience with ODP.NET, Managed Driver. The deployment configuration options are different between the managed and unmanaged drivers, but they are easy to learn.
What Do You Need?
Before starting this tutorial, you should:
Install Microsoft Visual Studio 2015 or later.
Install Oracle Database 11g Release 2 or higher. This tutorial uses the Oracle HR sample schema. Review the tutorial Installing Oracle Database 12c on Windows if needed.
Using this tutorial does not require previous experience with ODP.NET, Managed Driver, but familiarity with ODP.NET, Unmanaged Driver is recommended.
Many of the deployment differences between ODP.NET, Unmanaged Driver and ODP.NET, Managed Driver relate to the client and network configuration setting locations. The settings syntax and format remain the same. If you are not familiar with their syntax and format, it is recommended you familiarize yourself with the basics of Using Easy Connect Naming Method and TNS connect descriptor and alias configuration.
Note: Database connection information, including the connection string attribute, Easy Connect, and TNS descriptor and alias, are unique for every database client. In the OBE, the database connection setting values are merely examples and likely will not match your client specific values. To complete the OBE, replace the current values with the values for your specific database instance.
Getting Started with ODP.NET, Managed Driver
In this section, you will create an application in Microsoft Visual Studio 2015 and use ODP.NET, Managed Driver to connect to the Oracle Database.
Open Visual Studio. Click File. Select New > Project.
In the Reference Manager - ODPnet dialog box, Select Assemblies > Extensions tab and wait for the assembly component list to load completely. Navigate to the Oracle.ManagedDataAccess assembly, select it and click OK.
Copy the code from Code_Snippet.txt into the Program.cs within the Main method.
Description of this imageNote: This ODP.NET program uses the Easy Connect method to connect to the Oracle Database in the connection string Data Source attribute. The attribute's value may be different depending on how your database connectivity is configured. Once connected, the application queries the Oracle Database to retrieve the FIRST_NAME column data of the employees who belong to DEPARTMENT_ID 60 from the EMPLOYEES table in the HR schema.
Click the Start button or press the F5 key to run the application.
In this section, you will use three different ways of configuring an ODP.NET, Managed Driver application to connect to the Oracle Database. These configuration methods slightly differ from how ODP.NET, Unmanaged Driver applications are configured to connect.
Connect using the Local Naming Parameters file, tnsnames.ora
One method to connect to the database is to use the local naming parameters file, tnsnames.ora, to resolve a database alias provided by the Data Source attribute in the connection string. The provider will look for tnsnames.ora in the application's current working directory. Thus, it is possible for each application to have its own customized set of database aliases.
Open Program.cs file. Comment following line of code in the Program.cs file.
Copy the tnsnames.ora file provided to you in the files directory and place it in the <.NET Project Directory>\bin\Debug directory.
Description of this imageNote: ODP.NET, Managed Driver looks for the tnsnames.ora file in the application's working directory. This is one of the directories ODP.NET, Managed Driver looks for the tnsnames.ora file. You may need to customize the tnsnames.ora file with your database specific connectivity information.
Click the Start button or press the F5 key to run the application.
Connect using the .NET Application Configuration File:
The second configuration method is to store the database connection information within a .NET configuration file. ODP.NET, Managed Driver searches the .NET application config file for the database connection information ahead of the local application directory. Therefore, it will no longer make use of the local tnsnames.ora file we setup previously. This option is preferred for developers who do not wish to deploy tnsnames.ora.
Delete the tnsnames.ora file from the <.NET Project Directory>\bin\Debug directory.
Copy the code from Config_file.txt into App.config within the <configuration> tag.
Description of this imageNote: The syntax and format of the connect descriptor match exactly to what is used in the tnsnames.ora file. There is no difference in those aspects compared to using ODP.NET, Unmanaged Driver. Also,
oracle.manageddataaccess.client tag is used to specify the configuration information for ODP.NET, Managed Driver.
version number=”*” tag specifies that all ODP.NET, Managed Driver versions will use these configuration settings.
dataSources tag contains data source alias and connect descriptor information that were previously in the tnsnames.ora file, which you may need to customize for your own deployment.
Click the Start button or press the F5 key to run the application. Observe the output. The list of employees in department '60' is displayed in the console.
If you are working with many ODP.NET, Managed Driver deployments on the same machine and want them all to use the same connect descriptor and alias information/file, use the TNS_ADMIN property to specify a centralized location for tnsnames.ora.
Description of this imageNote: The "TNS_ADMIN" attribute value indicates a centralized location for ODP.NET, Managed Driver to find the tnsnames.ora file. The "value" attribute specifies the path where the tnsnames.ora file is located. Customize this setting to where you have placed tnsnames.ora on your machine.
Click the Start button or press the F5 key to run the application. Observe the output. The list of employees in department '60' is displayed in the console.
Using ODP.NET, Managed Driver with Oracle Developer Tools for Visual Studio
In this section, you will use ODP.NET, Managed Driver to connect to the Oracle Database through a Server Explorer connection using Oracle Developer Tools for Visual Studio (ODT). To direct Visual Studio to auto-generate ADO.NET code that relies on the managed driver (such as TableAdapter or Entity Framework Database First), use an ODP.NET, Managed Driver connection in Server Explorer.
The second part of this section will use the Oracle connection to setup a PL/SQL stored procedure in the database. The stored procedure will be used later in the OBE to demonstrate that complex features, such as array parameters, are available in ODP.NET, Managed Driver.
Creating an ODP.NET, Managed Driver Connection
Open the Unmanaged to Managed folder provided in files folder. Double click on ODP.NET Performance Best Practices.sln. The ODP.NET, Unmanaged Driver project opens up in Visual Studio.
In the Change Data Source dialog box, select Oracle Database as the Data Source and ODP.NET, Managed Driver as the Data Provider. Click OK. This step is required only if Data Source is not already set to Oracle Database (ODP.NET, Managed Driver).
You will now setup a PL/SQL stored procedure that passes an array parameter with ODT. This stored procedure will be called in the final part of this tutorial to demonstrate that advanced ODP.NET features are available with the managed provider.
Open Solution Explorer, right-click array.sql and select Open.
Review the array.sql script. Select all the code in the file and select [Ctrl] + C to copy it to the clipboard.
Description of this imageNote: This script will create a PL/SQL package MYPACK with a PL/SQL stored procedure MYSP. The stored procedure has a PL/SQL associative array input parameter that just passes its values back as an output PL/SQL associative array.
Right-click the HR.ORCL connection in the Server Explorer and select Query Window.
Migrating from ODP.NET, Unmanaged Driver to ODP.NET, Managed Driver
If you are an experienced ODP.NET developer, you will have existing ODP.NET, Unmanaged Driver applications. These can be migrated easily to ODP.NET, Managed Driver by performing a few simple steps. ODP.NET, Managed Driver shares the same APIs as ODP.NET, Unmanaged Driver. Thus, no significant coding changes are required. The only changes relate to configuration, including the ADO.NET assembly, namespaces, and Oracle Client settings.
In this part of the tutorial, we will migrate an unmanaged ODP.NET application to a managed ODP.NET application. In a previous section, we explored the changes needed with respect to the database connect descriptor and alias settings. In this section, we will look at the changes required for the ADO.NET assembly and namespaces.
Note: While ODP.NET, Managed Driver has the same APIs as ODP.NET, Unmanaged Driver, the unmanaged driver APIs are currently a superset of the managed driver. In future releases, the managed driver will support more and more of ODP.NET, Unmanaged Driver's existing APIs.
Open Solution Explorer. Double click on ODP.NET Best Practices.cs to open it.
Observe the screen. The ODP.NET Best Practices.cs file opens up. Review the code. Review the connection string.
Description of this imageNote: The data source name mentioned in the connection string is pdborcl. You should change it to the data source name that you are using. The first part of the demo uses the MYPACK.MYSP stored procedure we created previously. It first creates an input array parameter and an output array parameter before executing the stored procedure. Finally, it shows the output array's values in the console. The second part of the demo shows how to use batch SQL, REF Cursors, and multiple active result sets from ODP.NET. As you can see, the code uses advanced ODP.NET features. You will migrate this code to ODP.NET, Managed Driver to show that you can use these features from the managed driver.
Click the Start button or press the F5 key to run the application. Observe the output. This will demonstrate that the code runs successfully with the unmanaged driver.
The Reference Manager - ODP.NET Best Practices dialog box opens up. Select Assemblies > Extensions and wait for the assembly component list to load completely. Select the Oracle.ManagedDataAccess assembly check box. Click OK. This will add the ODP.NET, Managed Driver assembly to the application.
Click the Start button or press the F5 key to run the application. Observe the output. This will demonstrate that the code runs successfully with the managed driver.
Description of this imageNote: The results when running with ODP.NET, Managed Driver will be exactly the same as when you earlier ran with ODP.NET, Unmanaged Driver.
