SignalR & Oracle DB Change Notification [Part 2 of 2]

What is Oracle change Notification ?

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.

Features you get after you complete this tutorial

# Send real time message to any user for change in DB

# Alert about the operation like update / insert / delete

# Alert about any changes based on the SELECT statement defined.

# Functionality can be extended to any ticket selling systems, room booking systems where you wish to block the booking based on availability of certain resources in real time.

# This can also be very useful if the same DB is used by multiple platforms and multiple systems.

# This is a proof of concept and can be used any application you can think of…

How to implement this feature to send message to the connected clients about the changes.

Please refer to my previous post of installing signalR which can be used to push real time messages to all connected clients from the server.

In this project we will explore using this feature to integrate with oracle change notification feature.

Step 1: Open previously created project

Step 2: Install odp.net from Nuget

 

 

Open oracle SQL+ and grant change notification privilage to the user.

 

grant change notification to haneef

Add Connection Parameters to the web config  and also add the parameter name as below

 

Add DB Change method to the home controller.

 

Note the select parameter and constructor.,

This will be used to track any changes in the DB level

Whenever there is a change in the said SELECT statment out put the AlertUser method is triggered

 

This will in turn pass the message to the client using the Notify method which in turn uses the signal R which we added in previous message.

 

Output Sample

Download Complete Working solution from here