email article printer friendly get the code more resources

Use Oracle With ADO.NET
Access Oracle databases from your .NET apps.
by Bob Beauchemin

January 2003 Issue

Technology Toolbox: VB.NET, ADO.NET

Many Oracle customers use Microsoft operating systems and data APIs, and a substantial number of Microsoft customers use Oracle databases. If your organization belongs to either group, you've undoubtedly encountered a plethora of potentially confusing software choices among APIs, data drivers, and providers. I'll help you wend your way through the choices to select and deploy a winning software combination that'll enable your .NET programs to call into Oracle databases. I'll show you how to integrate Microsoft's new .NET Framework with existing Oracle databases in your organization. (Although you can use classic ADO with .NET through COM interop, I'll stick with ADO.NET in this article.)

The first step in calling Oracle from ADO.NET is to choose a data provider. Data providers—the ADO.NET equivalent of OLE DB providers or ODBC drivers—are .NET libraries that enable connectivity to a particular source of data, usually a relational database. Two categories of data providers enable ADO.NET data access: Bridge providers let you use data libraries designed for previous data-access technologies, and native providers use a model specific to ADO.NET.

Figure 1. Choose a Data-Access Architecture.

With Oracle databases, you have three provider choices. The OleDb data provider—shipped as part of the ADO.NET 1.0 base class libraries—allows ADO.NET to connect to a database using an OLE DB provider as a bridge between ADO.NET and the database. The Odbc data provider—released by Microsoft shortly after the release of .NET on the Web—is similar to the OleDb data provider, except it uses an Open Database Connectivity (ODBC) driver as a bridge. The native .NET data provider exposes native ADO.NET classes—such as Connection, Command, and DataReader—to communicate directly with the database. Underneath, native providers can use either native methods such as the Oracle Call Interface (OCI) or the direct protocol (the Oracle Transparent Network Substrate [TNS] protocol) to communicate with the database.

The OleDb and Odbc data providers exist only to provide a bridge from the old data-access worlds of OLE DB and ODBC to the new world of ADO.NET. Native providers are almost always the best choice. They not only offer performance gains, but they also remove an extra layer of indirection to the database (see Figure 1). I'll follow the native-provider path in this article. You can use a bridge provider only if you want to use similar code to expose multiple databases—for example, for a software package that uses both Oracle and Informix databases (where no Informix native provider is available, but an OLE DB provider and ODBC driver exist) or Oracle and Ocelot databases (where only an ODBC driver is available for Ocelot).