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 differences from ODP.NET, Unmanaged Driver application
- 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
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 an 100% .NET provider called
ODP.NET, Managed Driver.
ODP.NET, Managed Driver's APIs are a subset of the unmanaged driver's APIs. This makes learning of "how to use or migrating to the managed driver" simple for existing ODP.NET developers. Many developers will experience new benefits when deploying ODP.NET, Managed Driver. The deployment configuration options are different between the managed and unmanaged drivers, but they are easy to learn.
Before starting this tutorial, you should:
- Install Microsoft Visual Studio 2012
- Review the tutorial Installing Oracle Database 12c on Windows
- Install Oracle Database
- Install ODAC 12c Release 1 from here
- Extract these files into your working directory
While using this tutorial does not require previous experience
with ODP.NET, Managed Driver, 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 quickly familiarize
yourself with the basics of Oracle Easy
Connect 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 from scratch in Microsoft Visual Studio 2012 and use ODP.NET, Managed Driver to connect to the Oracle Database.
Deploying a ODP.NET, Managed Driver Application
In this section, we will look at three different ways of deploying an ODP.NET, Managed Driver application to connect to the Oracle Database. These connectivity methods slightly differ from how ODP.NET, Unmanaged Driver applications are configured to connect.
Note: The default ODAC GUI installer creates a
TNS_ADMIN property in the machine.config file. This
property will override usage of the tnsnames.ora file
located in the application's working directory. Before
continuing this OBE, we recommend you to comment out this
property in the machine.config file.
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.
Connect using the .NET Application Configuration File:
The second connection method is to store the database
connection information within a .NET configuration file.
ODP.NET, Managed Driver searches the .NET application
config, web.config, and machine.config file for the
database connection information. Therefore, it will no
longer make use of the tnsnames.ora file.
Note: This option is preferred for
developers who do not wish to deploy tnsnames.ora.
Connect using the TNS_ADMIN
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.
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).
When your application needs to use Visual Studio to
auto-generate ADO.NET code that relies on the managed driver
(such as TableAdapter code generation or Entity Framework
Database First), you connect in Server Explorer using ODP.NET,
Managed Driver. Else, if your code relies on ODP.NET Unmanaged
Driver, you would connect in Server Explorer using ODP.NET,
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
Using the Connection
In this part of the OBE, we will setup a PL/SQL stored
procedure that passes an array parameter with the tools.
This stored procedure will be called in the final part of
the OBE to demonstrate that advanced ODP.NET features are
available with the managed provider.
Migrating from ODP.NET, Unmanaged Driver to ODP.NET, Managed Driver
If you are an experienced ODP.NET developer, you will most
likely 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 such as, ADO.NET assembly, namespaces, and Oracle
In this part of the OBE, we will migrate an unmanaged ODP.NET application to a managed ODP.NET application. In the previous section, we explored the changes in setting 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.
In this tutorial, you have learned how to:
- Use ODP.NET, Managed Driver to connect to the Oracle
- Connect and deploy ODP.NET, Managed Driver in multiple ways
- Use ODP.NET, Managed Driver from Visual Studio Server Explorer to connect to Oracle Database
- Migrate an ODP.NET, Unmanaged Driver application to an
ODP.NET, Managed Driver application
- Oracle Data Access Components (ODAC) for Windows downloads
- Additional .NET OBEs in the Oracle Learning Library
- Lead Curriculum Developer: Supriya Ananth
- Other Contributors: Alex Keh, Christian Shay
To navigate this Oracle by Example tutorial, note the following:
- Hide Header Buttons:
- Click the title to hide the buttons in the header. To show the buttons again, click the title again.
- Topic List:
- Click a topic to navigate to that section.
- Expand All Topics:
- Click the button to show or hide the details for the sections. By default, all topics are collapsed.
- Hide All Images:
- Click the button to show or hide the screenshots. By default, all images are displayed.
- Click the button to print the content. The content that is currently displayed or hidden is printed.
To navigate to a particular section in this tutorial, select the topic from the list.