Using Database Change Notification With ODP.NET and Oracle Database 11g
Using Database Change Notification
With ODP.NET and Oracle Database 11g
This tutorial introduces the Database Change Notification
feature supported by the Oracle Data Provider for .NET and Oracle database.
Approximately 30 minutes
This tutorial covers the following topics:
 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
The Oracle Data Provider for .NET provides
a notification framework that supports Database Change Notification
in the database server. Database Change Notification enables applications
to receive notifications from a registered callback routine when there is a
change in a query result set, schema objects, or the state of the database that
could affect the results of a cached client result set.
Using this feature in ODP.NET, an application
can maintain the validity of the client-side cache (for example, the ADO.NET
DataSet) easily. The client does not need to maintain a connection to the database.
It will receive notifications even when no longer connected to the database.
This feature is intended for queries
whose results change infrequently over time and for those who cannot maintain
a large number of active database connections.
In this tutorial, an application will
display a DataSet grid containing the values of the HR.EMPLOYEES table. After
populating the grid, the application will disconnect from the database and await
notification that data has changed. Once the change is detected, the application
will reconnect to the database and refresh the data.
This tutorial demonstrates the flexibility
of Database Change Notification feature, including persisting notification registrations
even after numerous changes and distinguishing at the row level whether a change
would affect the client's query results. This feature is called Database Change
Notification (DCN) in Oracle Database 10g Release 2. In Oracle Database
11g, this feature was renamed to Continuous Query Notification (CQN).
Both these names refer to the same feature.
Back to Topic List
Before you perform this tutorial, you should:
 |
Install and create an Oracle Database 11g or
Oracle Database 10g Release 2 database server
|
| |
Install Visual Studio 2005 with Visual C#
|
| |
Install ODP.NET 11g with the Oracle Database
11g Client or from OTN
|
|
Download and unzip
"Change Notification.zip" into your working directory |
Back to Topic List
A user or role must have CHANGE NOTIFICATION privileges prior
to being able to run applications that accept Change Notification
callbacks.
| 1. |
From your windows program menu, start Visual Studio.
In Visual Studio, from View menu select Server Explorer.
A Server Explorer panel opens.
|
| 2. |
Right click Data Connections
and select Add Connection
from the menu.
An Add Connection dialog box opens.
|
| 3. |
In the Add Connection dialog box, ensure the Data Source
is set to Oracle Database Server (Oracle ODP.NET).
Note: If the Data Source is not set to Oracle
Database Server (Oracle ODP.NET), click "Change"
to select the appropriate data source and the data source provider.
In the Change Data Source dialog box, select Oracle
Database Server from the Data Source list and select Oracle Data
Provider for .NET from the Data Provider drop down list. Click OK.
|
| 4. |
In the Add Connection dialog box, make sure the Connection
Details tab is selected. From the Data Source drop down list, select the
Oracle Database instance SID you are using.
Enter user name as SYS, password as oracle and select the SYSDBA role from the dropdown list. Click OK
to connect.
Note: Your sys password may not be oracle.
Check with your database administrator for the sys password.
|
| 5. |
Similarly, create a connection for HR schema. In Server
Explorer, right click Data Connections and select Add Connection.
In the Add Connection dialog box, the appropriate data
source and data source provider is already selected.
In the Connection Details tab, enter user name as HR
and password as hr. Click OK.
Note: Your HR schema's password
may not be hr. Check with your database administrator for the HR
schema's password.
The Server Explorer shows the two connections.
|
| 6. |
Right click SYS.ORCL connection node in Server Explorer
and select Query Window. This
will open the Query Window.
|
| 7. |
In the Query Window, enter the following grant statement:
grant change
notification to hr
Click the Execute
Query button.
You get a successfully completed message in the query
output window. Close the Query Editor.
|
Back to Topic List
A Change Notification Visual Studio solution has been precreated
for you to load and run.
This program will select all records
in the EMPLOYEES table in the HR schema where the EMPLOYEE_ID value is less
than 200, then place the results in an ADO.NET DataSet. The DataSet does not
maintain a connection to the database server, so it no longer holds onto any
database resources, allowing for better scalability.
Before the SELECT statement is executed, an OracleDependency
object is bound to an OracleCommand. When the SELECT statement is executed,
this OracleDependency will create a notification registration on the database
server.
Even though the original DataSet is
disconnected, when another user modifies the EMPLOYEES table, the notification
handler will inform the .NET application a change to the source data has occurred.
On the client side, this will trigger an event handler that refreshes the DataSet
results.
| 1. |
In Visual Studio, from File menu, select Open > Project/Solution.
In the Open project dialog box, navigate to the folder where you extracted
the "Change Notification.zip" file, select DB Change
Notification solution file. Click Open.
|
| 2. |
From View menu, select Solution Explorer. In the Solution Explorer,
right click Form1.cs and select View Code.
The Form1.cs code window opens.
|
| 3. |
You may need to modify connection string attributes
to connect to Oracle. You don't need to change the Data Source connection
string attribute if your data source is a single local instance with the
Service Name as orcl or Net Service Name as orcl.
If you need to use a different data source alias, modify
the Data Source value in the connection string variable, "constr".
Note: You will connect to the HR schema, so check
that User Id is set to HR and Password is set correctly. In the example
code, HR's password is "hr". If this is not your HR schema's
password, check with your DBA for the correct password.
|
| 4. |
Add the following code under their respective comments.
The comments explain what each line of code does.
cmd.AddRowid
= true;
OracleDependency dep = new OracleDependency(cmd);
cmd.Notification.IsNotifiedOnce = false;
dep.OnChange += new OnChangeEventHandler(OnMyNotificaton);
|
| 5. |
In the Visual Studio, from Debug menu, select Start
Debugging. This will run the demo. If any break points are hit,
press F5 to continue running until
a grid containing the values of the HR. EMPLOYEES table appears.
Note: When you run this program for the first
time, you may receive the message as shown in the screenshot below from
Windows Firewall. Click on Unblock to allow the change notifications
to be sent from the database server to the client.
Do not exit the running demo.
|
Back to Topic List
Now the Change Notification application is running
and ready to receive notifications that data in the HR.EMPLOYEES table
has been changed. Let's modify the EMPLOYEES table data.
| 1. |
Click the (+)
plus sign next to the HR.ORCL node in Server Explorer to expand it.
Expand the Tables node.
Right click the EMPLOYEES table and select Retrieve Data...
to show it's data in Visual Studio.
You may get a Microsoft Visual Studio dialog box asking
you confirm whether you want to retrieve more number of rows than the
set limit of 100. Click Yes.
A window opens showing the employees table data.
|
| 2. |
In the newly opened Data Window in Visual Studio (NOT
in the Change Notification application grid), modify
one of the SALARY values. Click Save to cause the
value to be committed to Oracle Database.
|
| 3. |
In a second or two, the Change Notification
application opens a Notification Alert window, informing that the result
set has changed.
Note: See the taskbar at the bottom of the desktop
if you don't see the window. It may be hidden. (If a breakpoint fires
before seeing this window, press F5 to continue)
Click OK. Close the Oracle Database Change Notification
Demo.
|
Back to Topic List
Right now, our application receives the Change Notification
callback, but our event handler contains nothing more than the dialog box you
saw in the previous section. Let's add code to connect to the database and fetch
the rows that have been modified.
For single table SELECTs, the Change Notification callback
will return the ROWID information for all modified rows in the table. The ROWID
will be appended to the original query so that you can retrieve only the rows
that were changed, not the entire data set which would be less optimal. You
will then refresh the DataSet with just the new data.
| 1. |
Click the Form1.cs tab. In the dropdown box select OnMyNotificaton(..
This will take you to the OnMyNotificaton callback routine.
|
| 2. |
Add following lines of code under their respective comments.
The comments explain what each line of code does.
The following lines of code append ROWID(s) to the base
query to retrieve just modified row(s).
DataRow detailRow = args.Details.Rows[0];
string rowid = detailRow["Rowid"].ToString();
string sqlUpdate = sqlSelect + "where rowid = \'"+ rowid +"\'";
Add the following lines of code to append on to the sqlUpdate statement if there are additional updated rows.
for (int i = 1; i < args.Details.Rows.Count; i++)
{
detailRow = args.Details.Rows[i];
rowid = detailRow["Rowid"].ToString();
sqlUpdate = sqlUpdate + " or rowid = \'"+ rowid +"\'";
}
|
| 3. |
Add following lines of code to refresh the data.
OracleConnection con2 = new OracleConnection(constr);
OracleCommand cmd2 = new OracleCommand(sqlUpdate,con2);
con2.Open();
OracleDataAdapter da2 = new OracleDataAdapter(cmd2);
da2.Fill(ds, tablename);
|
| 4. |
From Build menu, select Build Solution.
Ensure there are no errors or warnings reported.
|
| 5. |
From Debug menu, select Start Debugging. A grid containing the values
of the HR. EMPLOYEES table appears.
|
| 6. |
You will now change multiple rows in the database and
see ODP.NET update only those rows that were changed.
In the Solution Explorer, double click multiple_row_change.sql.
|
| 7. |
Select the following code from the multiple_row_change.sql.
Press CTRL+C to copy the code.
update employees set salary=salary+10 where employee_id=100 or employee_id=101;
commit;
In Server Explorer, right-click HR.ORCL and select Query Window.
In the Query Window, paste the code and make sure both the statements
are selected. Click Execute Query to run the query.
|
| 8. |
You get the Notification Alert. Click OK.
Check the modified rows refreshed in the application
grid.
Note: The Oracle database
identifies the changed rows via their ROWIDs. The OracleNotificationEventArgs
instance passes the ROWID information back to ODP.NET. Only the rows that
have changed are retrieved, not the entire table. This saves the application
from refreshing data that is still valid.
In addition, you will notice that for each distinct
database change, you receive one notification. Even though two rows were
modified, the modification occurred as one discrete event. As such, only
one notification was delivered.
Close the Oracle Database Change Notification Demo window.
|
Back to Topic List
Beginning with Oracle Database 11g, ODP.NET can now
register its queries with the database and receive either object-change notifications
or result-set-change notifications.
Object-change notifications result from DML or DDL changes
to the objects (e.g. tables) associated with the queries. Result-set-change
notifications (the default) result from DML or DDL changes to the specific result
set rows associated with the queries.
Result-set-change notifications provide better granularity
for client applications by focusing only on the specific result set selected,
while object-change notification handlers are more lightweight.
Note: This part of the tutorial can only be run
if you are using ODP.NET 11g and Oracle Database 11g.
| 1. |
From the Visual Studio menu, select Debug > Start Debugging. The Oracle Database Change
Notification demo windows shows the Employees records.
Now, you change multiple rows in the database. ODP.NET
will update only those rows that were changed.
If not already open, from the Solution Explorer, open
multiple_row_change.sql.
|
| 2. |
Select the following code from the multiple_row_change.sql.
Press CTRL+C to copy the code.
Update
employees set salary=salary+10 where employee_id=200 or employee_id=201;
commit;
In
Server Explorer, right-click HR.ORCL and select Query Window.
In the Query Window, paste the code and make sure both the statements
are selected. Click Execute Query to run the query.
When you have done that, you will notice that
the Notification Alert window has not appeared.
That is because the update statement affects
rows outside of the original query, so the database notification handler
took no action.
|
Back to Topic List
In this tutorial, you learned how to:
|
Grant privileges to HR for Change
Notification |
|
Load and run the Change Notification
Demo Application |
|
Modify the Employees table and
watch a Change Notification event occur |
|
Add a callback routine to handle
the Change Notification Event |
|
Use result-set change notifications |
Back to Topic List
Move
your mouse over this icon to hide all screenshots.
|
Bookmark
