Can't we access Oracle without ODP.NET? Yes, we can. It is not compulsory for you to work with ODP.NET. As mentioned in the following section, we can still connect to and access Oracle using other alternative methods. But, in terms of features and performance, ODP .NET is your best choice for connecting .NET applications with Oracle database. Let us see how!
Note
I am limiting the discussion to only .NET applications or clients that are trying to access Oracle databases. I will not be discussing application development prior to .NET.
There exist four main methodologies to access Oracle database from a .NET application:
Microsoft's .NET data provider for ODBC (or ODBC.NET)
Microsoft's .NET data provider for OLEDB (or OLEDB.NET)
Microsoft's .NET data provider for Oracle
Oracle's data provider for .NET (or ODP.NET)
Before discussing each of the above methodologies, let us understand their nature from the following figure:
Microsoft's .NET data providers for ODBC and OLEDB are not intentionally developed exclusively for Oracle database. Those are generic .NET data providers mainly targeted for most of the common data sources. If you plan to use either of those two .NET data providers, you are likely to face performance problems.
From the above figure, you can observe that there exists a separate layer for each of those .NET data providers. In other words, ODBC.NET or OLEDB.NET would not directly execute the queries or commands. Those operations would be carried to another intermediate layer (or data access bridge) and further get executed at Oracle database. The existence of this intermediate layer really kills the performance (or response time) of execution. So, if you are trying to access Oracle database from a .NET application, neither of those would be a good choice.
Coming to the next choice, it is somewhat promising. Microsoft contributed a separate .NET Framework data provider (or Microsoft's Data Provider for Oracle) to connect to and access Oracle. It enables data access to Oracle data sources through Oracle client connectivity software without having any intermediate layers. This really improves performance over the previous two choices. Before using this provider in your .NET applications, you should install and configure Oracle client software (version 8.1.7 or later) on the development machine and test it.
The Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle database from any .NET client. It is the best in performance together with great flexibility. It allows developers to take advantage of native Oracle data types (including XML data type), XML DB, binding array parameters, Multiple Active Result Sets (MARS), Real Application Clusters (RAC), advanced security, etc.
As we are trying to develop .NET applications with access to Oracle database, we must have .NET Framework installed on our machine. Any Windows Operating System (preferably Windows Server 2003 or Windows XP Professional) supporting .NET can be used to work with ODP.NET.
At the time of this writing, .NET Framework 3.0 is the latest in market; but Oracle hasn't released ODP.NET compatible with that version yet. Not only that, Visual Studio 2008 (or "Orcas") supporting .NET Framework 3.0/3.5 is still in its beta version. For our purpose .NET Framework 2.0 is the latest in market, and you can download it free from Microsoft's website.
Even though .NET Framework (including SDK and .NET runtime) alone is enough to develop .NET-based applications, it is better to have some GUI-based RAD environment (or IDE) installed, so that we can develop .NET applications in no time. Microsoft Visual Studio 2005 Professional Edition is the preferred GUI to develop .NET 2.0-based applications. If you install Microsoft Visual Studio 2005 Professional Edition, all the necessary components (including .NET Framework SDK and runtime) get automatically installed.
The next is Oracle database. It is preferred to have at least Oracle 8.1 on your machine (or on a separate server). If you want to test with the latest version of Oracle on your own machine, you can download it free from Oracle's website for your development purposes. The lightest Oracle database version available (free) at the time of this writing is Oracle Database 10g Express Edition (or XE). Certain of the features like .NET CLR extensions (for .NET CLR-based stored procedure development) for Oracle are available only from Oracle 10g version 2.0 (Oracle 10.2) onwards. If you want to have distributed transaction support (like COM+ or Enterprise Services, etc.), then you may have install and configure Oracle Services for MTS.
If you install Oracle database version 9i release 2 or later on your own system, no special Oracle client is necessary to work with ODP.NET. If your database is at some other location, then you may have to install and configure Oracle 9i Release 2 or higher client on your machine to work with ODP.NET. Oracle Net Services get automatically installed when Oracle 9i Release 2 or higher client is installed on your machine. This may be required when you try to access an Oracle database on a network.
Another important optional component is Oracle Developer Tools for Visual Studio 2005. This is a wonderful add-in, which gets injected right into Visual Studio 2005. Using this add-in (called Oracle Explorer), you can connect to any Oracle database and work with schema or data without leaving the Visual Studio 2005 environment. It is particularly useful if you are likely to deal with .NET CLR extensions for Oracle. I strongly recommend having it installed on your machine, if you are working with Visual Studio Environment.
If you are developing ASP.NET applications, it is better to have IIS configured on your machine, to test web applications over the network. If you are developing Smart Phone or Pocket PC applications, you may need to install Smart Device Extensions for Visual Studio (which automatically installs .NET Compact Framework for Smart Devices).