In the early days of databases, developers used to have knowledge on only one data access technology as they would usually concentrate on a single database. Later, numerous database products advanced quickly, leaving programmers in a confused state when selecting a particular data access methodology. The era of evolving architectures like client/server (two tier), three tier, and multi-tier (which includes web-enabled) has dramatically changed the way of accessing databases. The paradigm got shifted from simple "connection-oriented" applications to connection-less or disconnected (or offline) applications to meet the demands of devices like PDAs/Handhelds, Smart Phones, Pocket PCs etc.
We now have several types of data access methodologies to develop applications. Choosing the best data access methodology is totally dependent on the type of application you are working on.
ADO.NET is a rock-solid technology and a proof of Microsoft's commitment to the UDA (Universal Data Access) strategy. The ADO.NET layer in the .NET architecture internally contains a few .NET data components (or .NET data providers), which can be used to connect to and access any database.
The data access through ADO.NET is shown in the following figure (along with other data access methodologies available prior to .NET):
Even though Microsoft designed its own .NET data providers, it has also opened its doors (specification) to the public and is encouraging other database companies to develop their own .NET data providers. Microsoft made the data access model consistent among all of the .NET data providers and thus any .NET data provider should definitely conform to the standards and architecture of ADO.NET. One of those is Oracle's ODP.NET, a .NET data provider that can connect to and access Oracle databases with tight integrity.
The ODP.NET features optimized data access to the Oracle database from a .NET environment. It is one of the several data access methods to connect to and access Oracle databases. Oracle didn't simply stop giving support to Microsoft platform with only ODP.NET. Instead, it has extended its commitment for Microsoft .NET by adding Oracle database extensions for .NET and Oracle Developer Tools for Visual Studio.
The upcoming sections will give you a solid understanding of ODP.NET along with its features.
We now have several types of data access methodologies to develop applications. Choosing the best data access methodology is totally dependent on the type of application you are working on.
ADO.NET is a rock-solid technology and a proof of Microsoft's commitment to the UDA (Universal Data Access) strategy. The ADO.NET layer in the .NET architecture internally contains a few .NET data components (or .NET data providers), which can be used to connect to and access any database.
The data access through ADO.NET is shown in the following figure (along with other data access methodologies available prior to .NET):
Even though Microsoft designed its own .NET data providers, it has also opened its doors (specification) to the public and is encouraging other database companies to develop their own .NET data providers. Microsoft made the data access model consistent among all of the .NET data providers and thus any .NET data provider should definitely conform to the standards and architecture of ADO.NET. One of those is Oracle's ODP.NET, a .NET data provider that can connect to and access Oracle databases with tight integrity.
The ODP.NET features optimized data access to the Oracle database from a .NET environment. It is one of the several data access methods to connect to and access Oracle databases. Oracle didn't simply stop giving support to Microsoft platform with only ODP.NET. Instead, it has extended its commitment for Microsoft .NET by adding Oracle database extensions for .NET and Oracle Developer Tools for Visual Studio.
The upcoming sections will give you a solid understanding of ODP.NET along with its features.
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).
The Oracle Database Extensions for .NET is a new feature of Oracle Database 10g Release 2 on Windows that makes it easy to develop, deploy, and run stored procedures and functions written in any .NET-compliant language.
Oracle Database Extensions for .NET makes it possible to build and run any .NET-based stored procedures or functions with Oracle Database for Microsoft Windows. This feature is supported only from Oracle 10g version 2 (on Windows) onwards or Oracle 10g Express Edition (or Oracle 10g XE).
How come Oracle understands .NET? Oracle database doesn't need to understand .NET at all. It simply hosts the Microsoft .NET Common Language Runtime (CLR) in an external process, outside of the Oracle database process, but on the same computer. The integration of Oracle database with the Microsoft Common Language Runtime (CLR) enables applications to run .NET stored procedures or functions on Oracle database without any hurdles.
Application developers can write stored procedures and functions using any .NET-compliant language, such as C# and VB.NET, and use these .NET stored procedures in the database, in the same manner as other PL/SQL or Java stored procedures. .NET stored procedures can be used from PL/SQL packages, procedures, functions, and triggers.
Once the caller (or other PL/SQL stored procedures, packages, etc.) calls any of these .NET routines (stored procedures or functions), they get executed by the Oracle hosted Microsoft CLR and the results are automatically picked up by the Oracle PL/SQL engine. Once the control comes back to PL/SQL engine, it proceeds with the normal and traditional the PL/SQL process flow of execution.
To develop .NET CLR-based stored procedures or functions, you may need to have Oracle 10g version 2 or higher (for Windows) or at least Oracle 10g Express Edition together with Oracle Database Extensions for .NET installed. If you use Oracle 10g Express Edition, the extensions get automatically installed. But, if you install Oracle 10g version 2 (for Windows), you may have to go to custom install and select the extensions. Apart from the extensions, you also need to download Oracle Developer Tools for Visual Studio (with appropriate version) to develop and deploy .NET CLR-based routines in Oracle database.
Application developers build .NET stored procedures or functions using any .NET compliant language, such as C# and VB.NET, into a .NET assembly (generally a DLL), typically using Microsoft Visual Studio .NET 2003/2005. Obviously, we use Oracle Data Provider for .NET (ODP.NET) in .NET stored procedures and functions for Oracle data access. After building .NET procedures and functions into a .NET assembly, developers deploy them in Oracle database, using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.
Once the .NET stored procedure gets deployed, the PL/SQL wrappers for all of those routines get automatically created within the schema. The user invokes a .NET stored procedure or function through this PL/SQL wrapper (which would be the same as for normal PL/SQL stored procedures or functions). Oracle Deployment Wizard for .NET determines the probable mappings between Oracle data types and .NET data types, which the user can override. The mappings are handled seamlessly by the PL/SQL wrapper.
Oracle Developer Tools for Visual Studio is an add-in for Microsoft Visual Studio that tightly integrates the Visual Studio environment with Oracle database. You will be able to manipulate Oracle databases from within Visual Studio and without leaving Visual Studio.
At the time of this writing, Oracle Developer Tools for Visual Studio is available for both Microsoft Visual Studio.NET 2003 and Microsoft Visual Studio.NET 2005 versions. If you have both versions on your computer, you can install for both of those IDEs by installing Oracle Developer Tools for Visual Studio 2005.
Once you install Oracle Developer Tools for Visual Studio, Oracle Explorer automatically shows up in the View menu of Visual Studio as shown in the following figure:
Using Oracle Explorer in the Visual Studio environment, you can browse through your entire Oracle schema, launch several designers and wizards to work with different schema objects (like tables, views, etc.), execute queries directly against your schema (using SQL Query Window), automatically generate .NET code, and several more. The following is a small glimpse of Oracle Explorer:
To work with database tables (for example inserting, updating, etc.) you can keep yourself tied with Oracle Data Window. It also gives you the flexibility to run and test your PL/SQL stored procedures. Oracle Explorer also includes a fully integrated PL/SQL debugger (for Visual Studio 2005).
Apart from all of the above, you can easily develop and deploy .NET stored procedures and functions using .NET Deployment Wizard.
