email article printer friendly get the code more resources

Access Oracle-Specific Features
Use Oracle's ODP.NET data provider to let your apps tap deeply into Oracle database functionality.
by Bob Beauchemin

March 2003 Issue

Technology Toolbox: ADO.NET, C#, Oracle Database, ODP.NET

Use the Oracle Data Provider for .NET (ODP.NET) to extend the base ADO.NET interfaces and base classes to put almost all the functionality the Oracle database offers within reach. ODP.NET allows you to expose Oracle-specific behaviors and optimize access in Oracle-specific .NET applications. Using database-specific ADO.NET data providers is almost always preferable to using the OLE DB and ODBC bridge providers (see the sidebar, "Let a Hundred Providers Bloom"). However, writing optimized code for the target database requires you to understand how different data providers work. I will examine ODP.NET's features and compare it to the SqlClient data provider. Along the way, I'll explain downloadable program examples that put ODP.NET to use. This article is based on the 1.0 release of ODP.NET (see Additional Resources).

Figure 1. ODP.NET Exposes Extended Data Types.

ADO.NET data providers implement provider-specific classes that implement common interfaces. Some of these classes extend base classes in the .NET Framework. The data-provider classes implemented in ODP.NET are part of the Oracle.DataAccess.Client namespace (see Table 1). A data provider must also map database data types to the .NET type system (see the sidebar, "Use ODP.NET to Help Map Data Types"). It can expose database-specific classes to map more closely if no .NET types are an exact fit. ODP.NET exposes a series of extended data types in the Oracle.DataAccess.Types namespace. Some of these are implemented as value types (structures, in C#), because .NET value types provide optimizations over making every type a class; others are implemented as reference types (classes) (see Figure 1).

Now that you understand the landscape, you can write some simple C# code to exercise ODP.NET. For example, you can connect to the Oracle database, execute a SQL SELECT statement, and return results through a forward-only, read-only OracleDataReader class (see Listing 1).

This code uses an OracleDataAdapter to return the entire resultset from the same SELECT statement and populate a disconnected ADO.NET DataSet. It then prints the count of rows in the first DataTable to the console:

OracleDataAdapter da = new 
   OracleDataAdapter("select * from 
   emp", "Data Source=zmv43;User 
   ID=scott;Password=tiger");
DataSet ds = new DataSet();
da.Fill(ds, "emp");
Console.WriteLine(
   "First table has {0} rows", 
   ds.Tables["emp"].Rows.Count);

(All the code examples use Oracle's sample schema for user SCOTT. ODP.NET uses the same names as the Microsoft provider for Oracle and the DataDirect provider to accommodate users of these providers who wish to convert to ODP.NET.)