Developer: ODP.NET

When It's Time to Change
By Alex Keh

ADO.NET 2.0 and database change notification improve flexibility and performance.

Oracle supports .NET developers in a variety of ways. Oracle Data Provider for .NET (ODP.NET) offers many features, including support for new Microsoft ADO.NET 2.0 features, such as factory classes. And as part of Oracle Database 10g Release 2, database change notification allows a .NET client to be alerted when the server data it consumes is changed. This feature makes it possible to cache data on the client side without its getting out of sync with the server's data. This article looks at how ODP.NET supports ADO.NET 2.0 and database change notification.


ADO.NET 2.0 introduces a new level of abstraction for the data access layer (DAL). Instead of using provider-specific classes that implement a generic interface, ADO.NET 2.0 offers the DbCommon classes, which inherit from the System.Data.Common namespace and allow you to use factory classes. Database provider factory classes allow an easier way to create one set of generic data access code (as opposed to datasource-specific access code) for any database supporting ADO.NET 2.0. A DbProviderFactories method call instantiates a concrete DbFactory class instance specific to your data provider. ODP.NET (Release and higher) is fully compliant with ADO.NET 2.0 and supports the creation of an OracleClientFactory class.

When ODP.NET is installed, each ADO.NET 2.0 database factory class is registered and added to the DbFactoryProviders section of the machine.config file. The invariant attribute in the machine.config file defines the name of the data provider: Oracle.DataAccess.Client.

Using the GetFactory method of the DbProviderFactories class and specifying the machine.config file's invariant value for ODP.NET returns a provider-specific instance of the DbProviderFactory class:

DbProviderFactory factory = DbProviderFactories.GetFactory(
"Oracle.DataAccess.Client"); // C#

The OracleClientFactory class supports the creation of all of the classes in System.Data.Common. Because these concrete classes inherit from the DbCommon abstract base classes, you can write generic DAL code by using DbCommon base class object names. There are some areas of the DAL that remain datasource-specific, including the connection string, SQL, and stored procedure calls.

Connection strings in ADO.NET are name-value pairs. The DbConnectionStringBuilder class has a dictionary that maps generic parameter names to provider-specific parameter names. The OracleConnectionStringBuilder class inherits from and extends the generic DbConnection-StringBuilder class to expose Oracle-specific connection string properties. You can use Oracle-ConnectionStringBuilder dynamically to set connection string parameters at runtime and/or obtain connection string parameters from the app.config file.

You can use the OracleDataSourceEnumerator class to provide an application with a dynamically generated collection of Oracle datasources. Your application can select a specific datasource to connect to, based on user inputs or predefined criteria.

The ADO.NET 2.0 schema discovery APIs provide a generic way to retrieve metadata from any datasource. With ODP.NET and higher, you can obtain Oracle metadata by using an OracleConnection.GetSchema method call. There are five types of metadata that can be exposed: MetaDataCollections, Restrictions, DataSourceInformation, DataTypes, and ReservedWords.

You can customize or extend the metadata by adding an entry similar to the following to the app.config file:

      <add name="MetaDataXml" 
        value="CustomMetaData.xml" />

When the GetSchema method is called, it checks the app.config file for an entry matching the invariant name of the data provider. The MetaDataXml name-value pair is used to specify the XML file containing the metadata customizations. The CustomMetaData.xml file should be placed under the CONFIG folder of the .NET Framework.

In ADO.NET 1.x, updating the database from DataSet occurred one row at a time. When a large number of rows changed, these client round-trips to the database server significantly degraded performance. In ADO.NET 2.0, it is now possible to batch row changes from DataSet into fewer database round-trips. By using the OracleDataAdapter.UpdateBatchSize property, you can specify the number of rows to update per round-trip. To update all the modified rows in one round-trip, you can set UpdateBatchSize to zero.

Database Change Notification

Database change notification, a feature of Oracle Database 10g Release 2, enables client applications to receive notifications when data manipulation language "DML" or data definition language "DDL" changes are made to a database object of interest. You can take advantage of this database feature in the .NET Framework 1.x and 2.0 releases.

To use database change notification, the application registers a query with the database. When a query has dependencies on underlying database objects and a change to an object is committed, the database publishes a change notification to the application. The notification contains only metadata about what data or objects have changed; it does not contain the changed data. You can create a client event handler to reissue the registered query to obtain the changed data.

Database change notification is particularly useful to applications that use cached results. Caching is particularly effective at improving application scalability by allowing rapid access to data without the need for expensive round-trips to the database. But this scalability comes at the price of data becoming stale.

Database change notification solves the problem of stale data in a cache. Although database change notification is similar to a trigger, in that both respond to a particular event, a trigger takes action immediately whereas a database notification is just an alert, not an action. It is up to the application to determine what action, if any, to undertake and when. The application can immediately refresh the stale objects, postpone the refresh, or ignore the notification.

Web applications often handle a variety of data that doesn't all need to be queried in real time. For example, a weather forecast is updated only periodically. End users don't need to query the database for each forecast request. Because many people will be requesting the same data, application performance and scalability are greatly enhanced if the results are cached and the data is retrieved from the cache. At some point, however, the weather forecast is updated and the cache must be refreshed.

The process of executing queries to refresh the cache after receiving a change notification requires the database user to have the CHANGE NOTIFICATION privilege. The user must be a non-SYS user to create a registration and must have SELECT privileges on all objects being registered for change notification. Connecting as this user, applications subscribe to change notifications that are published when an event of interest occurs.

The change notification developer creates a client-side notification handler for the registered queries. The following ODP.NET classes are used for building change notification applications:

  • OracleDependency creates a dependency between an application and an Oracle database. It enables the application to receive a notification of a data change, such as an UPDATE statement, or a schema change, such as DROP TABLE. The OnChange event in this class provides the client logic for what to do after the notification is received.
  • OracleNotificationEventArgs provides the event data for a change notification.
  • OracleNotificationRequest specifies the characteristics of a notification request and its notification. It is also a property of the OracleCommand object. Use the IsPersistent property to store the notification messages on disk instead of in memory, so that they will persist if the database is shut down prior to the messages' delivery to the application.

Database Change Notification Sample

The downloadable sample code for this article at includes a Windows Forms application for processing changes to the EMPLOYEES table in the HR schema. You can download Oracle Database 10g Express Edition from and install it to have all of the functionality (and the HR schema) you need in order to run the sample code.

Because a change notification only informs an application that a change has occurred but does not include the data changes, the OnChange event handler can requery the entire data set. With ODP.NET you can choose to retrieve only the rows that were changed in a single table query. You do this by adding the ROWID pseudocolumn to identify exactly which rows have changed. Set the OracleCommand object's AddRowid property to TRUE, so that ROWIDs are returned.

cmd.AddRowid = true;  // C#

To register the query for change notification, bind its OracleCommand to an OracleDependency object instance. This creates an Oracle-NotificationRequest that will create the registration in the database when the OracleCommand executes.

OracleDependency dep = new OracleDependency(cmd);  // C#

By default, the database notification handler will be removed after the first notification. To make a notification handler persist after the first database change, set the OracleNotification-Request.IsNotifiedOnce property to FALSE. This avoids the overhead of recreating the notification subscription after each change if the same database objects need to be monitored continuously.

cmd.Notification.IsNotifiedOnce = 
false;  // C#

Notifications occur asynchronously, so add an event handler delegate to process the notification.

dep.OnChange += new 
OnMyNotification);  // C#

The event handler processes the change notification's event data.

public static void OnMyNotification(
object src, OracleNotificationEventArgs args)  // C#

The OracleNotificationEventArgs class has a Details property that returns a DataTable containing detailed information about the current notification. Because the ROWID is part of the OracleCommand object, each DataRow object in the DataTable contains the ROWID of a changed row.

DataRow detailRow = 
args.Details.Rows[0];  // C#

Exactly what the Details DataTable contains depends on how much data was changed. Data that is changed is considered invalidated. You can issue a new query to update the client-side data, and if the number of changes is large, you can refresh the entire original set of rows, in which case you will not need ROWID information. When fewer rows are changed, however, you can append the ROWID for each invalidated row to a query to update the client data. To take full advantage of this type of change notification, modify the original query by adding a WHERE clause to limit the returned data to only the changed rows.

The following code appends the first ROWID value to the query string, where sql represents the original query string:

string sqlUpdate = sql + " where rowid = \'" + rowid + "\'";  // C#

The resulting query now looks like this (your ROWID values will be unique to your database and the specific update performed):

select * from employees 
where rowid = 'AAACwuAAEAAAABXAAA';

Any additional changed rows are added to the query inside the FOR loop. When two rows have changed, the sqlUpdate query looks something like this:

select * from employees 
where rowid = 'AAACwuAAEAAAABXAAA' 
or rowid = 'AAACwuAAEAAAABXAAB';

Execute the final query by using an OracleCommand object. An OracleDataAdapter's Fill method results in the DataGrid's being updated with the changed data values.

Oracle database change notification offers several features not available from Microsoft SQL Server. For example, Oracle Database supports all types of joins, whereas SQL Server does not support queries containing either outer joins or self-joins. SQL Server does not support notifications for statements using views, whereas Oracle database change notification supports views, with the exception of fixed views, such as V$ tables, and materialized views. SQL Server notifications also require explicit column references, whereas Oracle Database notifications support both SELECT * and explicit column references.

SQL Server notifications are not persistent. When a SQL Server notification is published, the notification handler is removed. If the notification handler is still needed, a new notification handler must be created by the application. Oracle Database change notifications can persist after repeated changes when you set OracleNotificationRequest.IsNotifiedOnce to TRUE.

Properly using database change notification will improve the performance and usability of your application. Here are a couple of recommended practices to help make the best use of this feature:

Oracle and .NET

sample code for this article
Oracle Database 10g Express Edition

  • Use database change notification with data that changes infrequently. The benefit of using database change notification is lost when data changes are frequent, such as with a stock ticker. A continuous stream of changes means the overhead of constant notification traffic and subsequent data updates. If the data changes almost as frequently as it is read, don't use change notification—just requery the database whenever you do a read.
  • Limit the number of database change notification registrations for a particular query. If hundreds of users are monitoring the same query for changes, hundreds of notifications will be sent to the client tier when one data change occurs. In such cases, rather than have each client in a system register for notification, move the registration code into a middle-tier element to consolidate the number of registrations and the workload.


Oracle continues to support .NET developers with enhancements that improve ease of use and application performance. ODP.NET supports ADO.NET 2.0 features, including factory classes. Oracle database change notification offers a powerful and easy-to-implement means of publishing database changes to a .NET application.

Alex Keh is a principal product manager for .NET data access at Oracle.

Send us your comments