Introduction to Building ODP.NET, Managed Driver Applications


Options



Before You Begin

Purpose

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:
  1. Install Microsoft Visual Studio 2015 or later.
  2. 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.
  3. Install ODAC 12c Release 4 or higher.
  4. Extract these files into your working directory.
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.

  1. Open Visual Studio. Click File. Select New > Project.

    ODPnet
    Description of this image
  2. Select Visual C# > Windows > Console Application. Rename the project as ODPnet. Click OK.

    ODPnet
    Description of this image
  3. Observe the screen. The project ODPnet opens up. Program.cs is shown on the screen.

    ODPnet
    Description of this image
  4. Select View > Solution Explorer.

    ODPnet
    Description of this image
  5. Right click References in the Solution Explorer and Choose Add Reference....

    ODPnet
    Description of this image
  6. 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.

    ODPnet
    Description of this image
  7. Add the following code in the Program.cs file, to add the managed ODP.NET namespace.

    using Oracle.ManagedDataAccess.Client;
    ODPnet
    Description of this image
    The Oracle.ManagedDataAccess.Client namespace accesses the ODP.NET, Managed Driver to connect to the Oracle Database.
  8. Open the Code_Snippet.txt from the files folder that was provided to you.

    ODPnet
    Description of this image
  9. Copy the code from Code_Snippet.txt into the Program.cs within the Main method.

    ODPnet
    Description of this image
    Note: 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.
  10. Click the Start button or press the F5 key to run the application.

    ODPnet
    Description of this image
  11. Observe the output. The list of employees in department '60' is displayed in the console.

    ODPnet
    Description of this image
    Note: Close the command prompt to stop the application.

Deploying a ODP.NET, Managed Driver 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.

  1. Open Program.cs file. Comment following line of code in the Program.cs file.

    “Data Source=localhost:1521/pdborcl; Pooling=false;”;
    ODPnet
    Description of this image
  2. Uncomment the following line of code in the Program.cs file.

    “Data Source=pdborcl; Pooling=false;”;
    ODPnet
    Description of this image
  3. Copy the tnsnames.ora file provided to you in the files directory and place it in the <.NET Project Directory>\bin\Debug directory.

    ODPnet
    Description of this image
    Note: 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.
  4. Click the Start button or press the F5 key to run the application.

    ODPnet
    Description of this image
  5. Observe the output. The list of employees in department '60' is displayed in the console.

    ODPnet
    Description of this image
    Note: Close the command prompt to stop 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.

  1. Delete the tnsnames.ora file from the <.NET Project Directory>\bin\Debug directory.

    ODPnet
    Description of this image
  2. Navigate to Solution Explorer window. Double click the App.config file to open.

    ODPnet
    Description of this image
  3. Observe the App.config file.

    ODPnet
    Description of this image
  4. Open the Config_file.txt from the files folder that was provided to you.

    ODPnet
    Description of this image
  5. Copy the code from Config_file.txt into App.config within the <configuration> tag.

    ODPnet
    Description of this image
    Note: 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.
  6. 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.

    ODPnet
    Description of this image

Connect using the TNS_ADMIN Property:

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.

  1. Open App.config file. Comment the dataSource tag.

    ODPnet
    Description of this image
  2. Uncomment the setting tag.

    ODPnet
    Description of this image
    Note: 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.
  3. 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.

    ODPnet
    Description of this image

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

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

    ODPnet
    Description of this image
  2. Select View > Server Explorer.

    ODPnet
    Description of this image
  3. Right-click Data Connections and select Add Connection...

    ODPnet
    Description of this image
  4. The Add Connection dialog box opens. If Data Source is not already set to Oracle Database (ODP.NET, Managed Driver), click Change...

    ODPnet
    Description of this image
  5. 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).

    ODPnet
    Description of this image
  6. Enter the following details in the Add Connection dialog box and click OK.

    • User name: HR
    • Password: HR
    • Data source name: ORCL
    ODPnet
    Description of this image
    Note: The connection information may be different for your specific database setup.
  7. Expand the Data Connections. Observe HR.ORCL under it.You can now use ODP.NET, Managed Driver to browse and modify the HR schema.

    ODPnet
    Description of this image
  8. Expand the HR.ORCL connection to see the various contents in it.

    ODPnet
    Description of this image

Using the Connection

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.

  1. Open Solution Explorer, right-click array.sql and select Open.

    ODPnet
    Description of this image
  2. Review the array.sql script. Select all the code in the file and select [Ctrl] + C to copy it to the clipboard.

    ODPnet
    Description of this image
    Note: 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.
  3. Right-click the HR.ORCL connection in the Server Explorer and select Query Window.

    ODPnet
    Description of this image
  4. Press [Ctrl] + V to paste the code in the Query Window. Select all the text in the query window and click the Execute button.

    ODPnet
    Description of this image
    Note: Make sure to select all the code in the query window before executing or else the script will not execute properly.
  5. Observe the output. The PL/SQL package has been created.

    ODPnet
    Description of this image

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.

  1. Open Solution Explorer. Double click on ODP.NET Best Practices.cs to open it.

    ODPnet
    Description of this image
  2. Observe the screen. The ODP.NET Best Practices.cs file opens up. Review the code. Review the connection string.

    ODPnet
    Description of this image
    Note: 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.
  3. 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.

    ODPnet
    Description of this image
  4. Comment the following lines of code. These namespaces specify that the application uses ODP.NET, Unmanaged Driver.

    using Oracle.DataAccess.Client;
    using Oracle.DataAccess.Types;
    ODPnet
    Description of this image
  5. Goto Solution Explorer, right click References and select Add Reference....

    ODPnet
    Description of this image
  6. 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.

    ODPnet
    Description of this image
  7. Uncomment the following lines of code. These namespaces specify that the application uses ODP.NET, Managed Driver.

    using Oracle.ManagedDataAccess.Client;
    using Oracle.ManagedDataAccess.Types;
    ODPnet
    Description of this image
  8. 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.

    ODPnet
    Description of this image
    Note: The results when running with ODP.NET, Managed Driver will be exactly the same as when you earlier ran with ODP.NET, Unmanaged Driver.

Want to Learn More?