Base Classes and Provider Factory Classes

With ADO.NET, data classes derive from the base classes defined in the System.Data.Common namespace. Developers can create provider-specific instances of these base classes using provider factory classes.

Provider factory classes allow generic data access code to access multiple data sources with a minimum of data source-specific code. This reduces much of the conditional logic currently used by applications accessing multiple data sources.

Using Oracle Data Provider for .NET, the OracleClientFactory class can be returned and instantiated, enabling an application to create instances of the following ODP.NET classes that inherit from the base classes:

In general, applications still require Oracle-specific connection strings, SQL or stored procedure calls, and declare that a factory from ODP.NET is used.

DbDataSource

ODP.NET Core supports the ADO.NET DbDataSource abstraction through the OracleDataSource class. The new OracleDataSource class can retrieve connections and execute commands directly as an alternative to using OracleConnection and OracleCommand.

Since the class encapsulates all Oracle database connection configuration, OracleDataSource is portable and can be registered in dependency injection as a connection factory. It is ideal when provider agnostic code is preferred when connecting to Oracle databases.

OracleDataSource does not have a constructor; it must be created using the OracleDataSourceBuilder class or through the OracleClientFactory.CreateDataSource(string connectionString) method.

In ODP.NET, many connection configuration options are not available on the connection string. For those configuration options, developers can use OracleDataSourceBuilder, then build an OracleDataSource from it.

ODP.NET Core 23.8 began support for OracleDataSource and OracleDataSourceBuilder with .NET 8 runtime or higher.

OracleDataSource Connections

OracleDataSource can return open or closed connections synchronously or asynchronously. It can also create commands and batch operations that use the connection string.

OracleDataSource Code Sample:

OracleClientFactory factory = new OracleClientFactory();

// Create OracleDataSource
DbDataSource dataSource = new OracleDataSourceBuilder("user id=hr; 
password=<PASSWORD>; data source=oracle").Build();

// Create closed connection, which uses the connection string set on OracleDataSource
DbConnection connection = dataSource.CreateConnection();
connection.Open();

connection.Close();

// Alternatively, an OracleCommand can be created directly from the OracleDataSource, which will be ready to execute commands
DbCommand cmd = dataSource.CreateCommand("select * from employees");
DbDataReader rdr = command.ExecuteReader();
while (reader.Read())
{
  Console.WriteLine(reader.GetString(0));
}
reader.Close();

When pooling is enabled, each OracleDataSource instance has its own corresponding connection pool, allowing connections to be dispensed faster by skipping the look up process. Because OracleDataSource corresponds to a specific connection pool, an OracleDataSource is immutable once created. However, OracleConnections created from an OracleDataSource are mutable.

OracleDataSource connections use a different pool from traditional OracleConnection pools even when they have the same connection properties. By the same principle, OracleConnection.ClearAllPools() does not affect pools maintained by OracleDataSource objects. OracleDataSource has its own ClearPool() method. When an OracleDataSource is disposed, its corresponding pool is cleared.

After an OracleConnection is created from an OracleDataSource, its properties can be changed when the connection is closed. However, some connection properties are used to assign the pool a unique identifier. Modifying one or more of those properties removes the connection from the existing OracleDataSource pool and either creates a new pool or uses another existing pool.

Passwords can be updated through the OracleDataSource.Password and OracleDataSource.ProxyPassword properties. Upon the next connection open, the new password will be used to authenticate the user. These properties do not update the database password. Passwords are the only mutable OracleDataSource property.

OracleDataSource Performance Counters

Pool names in ODP.NET performance counters are prefixed with "ODS: " for OracleDataSource connections, such as "ODS: user id=HR; data source=oracle".

Two OracleDataSource instances with the same connection string will have separate pools. Oracle recommends setting pool name values to distinguish each uniquely. The pool name can be set via OracleConnection.PoolName or OracleDataSourceBuilder.UsePoolName.

Developers are responsible for ensuring there each pool has a unique pool name.

The pool name must be set before the pool is created. ODP.NET will not use the pool name value after the pool is created nor will it throw an error.

OracleDataSource Connection Event Callbacks

Just like OracleConnection properties, OracleDataSource and OracleDataSourceBuilder can employ event callbacks. Their event behavior is near equivalent to OracleConnection's except for HAEvent. OracleConnection HAEvent is static, which means it registers events for all OracleConnection instances. OracleDataSource HAEvent is not static; it is instance based like the other events. HAEvent registered on the OracleDataSourceBuilder only affects connections created from the OracleDataSource, not all connections.

This table shows the OracleConnection, OracleDataSource, and OracleDataSourceBuilder event properties and their common or unique behavior.

OracleDataSource Commands

OracleDataSource.CreateCommand() returns a new OracleDataSourceCommand class, which is derived from OracleCommand. The class functions almost identically to OracleCommand with a few differences.

First, the connection object is not accessible to the app. OracleDataSourceCommand internally opens a connection before an execution call and closes it after the completing the execution except for ExecuteReader. The reader depends on the connection to retrieve results. The connection closes when the reader closes.

Applications typically defer retrieving all their ODP.NET connected types, such as LOB and LONG data, which requires the connection to remain open to be able to fetch data in the future. When using connected types with OracleDataSourceCommand and without ExecuteReader, set the InitialLOBFetchSize or InitialLONGFetchSize property to a sufficiently large value to retrieve all the result set LOB and LONG data immediately. If any LOB or LONG data remains and the app attempts to fetch it, an exception is thrown.

OracleDataSourceCommand can only be created through OracleDataSource.CreateCommand().

Since connection objects are not accessible to OracleDataSource, the OracleDataSourceCommand.Connection and OracleDataSourceCommand.Transaction properties throw an InvalidOperationException when accessed.

ExecuteNonQuery() example

OracleDataSource ds = new OracleDataSourceBuilder("<CONNECTION STRING>").Build();
OracleCommand cmd = ds.CreateCommand("INSERT INTO mytable VALUES(0, 1)");
// ODP.NET internally opens a connection when ExecuteNonQuery is called
OracleDataReader rdr = cmd.ExecuteNonQuery();
// ODP.NET closes the connection after ExecuteNonQuery completes.

ExecuteReader() example

OracleDataSource ds = new OracleDataSourceBuilder("<CONNECTION STRING>").Build();
OracleCommand cmd = ds.CreateCommand("SELECT * FROM mytable");
// ODP.NET internally opens a connection when ExecuteReader is called
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
    Console.WriteLine(rdr.GetString(0));
}
rdr.Close();
// ODP.NET closes the connection when the reader is closed.