Receive Oracle Database Change Notifications Using the WCF Service Model1

Article
01/05/2022

This topic demonstrates how to configure the Oracle Database adapter to receive query notification messages from an Oracle database. To demonstrate notifications, consider a table, ACCOUNTACTIVITY, with a “Processed” column. When a new record is inserted to this table, the value of the Status column is set to ‘n’. You can configure the adapter to receive notifications by registering for notifications using a SQL statement that retrieves all records that have “Processed” column as ‘n’. You can do so by specifying the SQL statement for the NotificationStatement binding property. Once the adapter client receives the notification, it can contain the logic to do any subsequent tasks on the Oracle database. In this example, for the sake of simplicity, the adapter client lists all the records in the table that have the “Processed” column as ‘n’.

Configuring Notifications with the Oracle Database Adapter Binding Properties

The table below summarizes the Oracle Database adapter binding properties that you use to configure receiving notifications from Oracle database. You must specify these binding properties while running the .NET application to receive notifications.

Binding Property	Description
InboundOperationType	Specifies the inbound operation that you want to perform. To receive notification messages, set this to Notification.
NotificationPort	Specifies the port number that ODP.NET must open to listen for database change notification from Oracle database.
NotificationStatement	Specifies the SELECT statement used to register for query notifications. The adapter gets a notification message only when the result set for the specified SELECT statement changes.
NotifyOnListenerStart	Specifies whether the adapter sends a notification to the adapter clients when the listener is started.

For a more complete description of these properties, see Configure the binding properties for Oracle Database. For a complete description of how to use the Oracle Database adapter to receive notifications from Oracle database, read further.

Configuring Notifications Using the WCF Service Model

To receive the notifications using the WCF service model, you must:

Generate a WCF service contract (interface) for the Notification operation from the metadata exposed by the adapter. To do this, you could use the Add Adapter Service Reference Plug-in.
Generate a WCF client for the Select operation on the ACCOUNTACTIVITY table. To do this, you could use the Add Adapter Service Reference Plug-in.
Implement a WCF service from this interface.
Host this WCF service using a service host (System.ServiceModel.ServiceHost).

The WCF Service Contract and Class

You can use the Add Adapter Service Reference Plug-in to create a WCF service contract (interface) and supporting classes for the Notification operation. For more information about generating a WCF service contract, see Generate a WCF client or a WCF service contract for Oracle Database solution artifacts.

The WCF Service Contract (Interface)

The following code shows the WCF service contract (interface) generated for the Notification operation.

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03", ConfigurationName="Notification_OperationGroup")]
public interface Notification_OperationGroup {

    // CODEGEN: Generating message contract since the wrapper namespace (http://Microsoft.LobServices.OracleDB/2007/03/Notification/) of message Notification
    // does not match the default value (http://Microsoft.LobServices.OracleDB/2007/03)
    [System.ServiceModel.OperationContractAttribute(IsOneWay=true, Action="http://Microsoft.LobServices.OracleDB/2007/03/Notification")]
    void Notification(Notification request);
}

The Message Contracts

Following is the message contract for the Notification operation.

[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.MessageContractAttribute(WrapperName="Notification", WrapperNamespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", IsWrapped=true)]
public partial class Notification {

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", Order=0)]
    public microsoft.lobservices.oracledb._2007._03.Notification.NotificationDetails[] Details;

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", Order=1)]
    public string Info;

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", Order=2)]
    public string[] ResourceNames;

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", Order=3)]
    public string Source;

    [System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://Microsoft.LobServices.OracleDB/2007/03/Notification/", Order=4)]
    public string Type;

    public Notification() {
    }

    public Notification(microsoft.lobservices.oracledb._2007._03.Notification.NotificationDetails[] Details, string Info, string[] ResourceNames, string Source, string Type) {
        this.Details = Details;
        this.Info = Info;
        this.ResourceNames = ResourceNames;
        this.Source = Source;
        this.Type = Type;
    }
}

WCF Service Class

The Add Adapter Service Reference Plug-in also generates a file that has a stub for the WCF service class implemented from the service contract (interface). The name of the file is OracleDBBindingService.cs. You can insert the logic to process the Notification operation directly into this class. The following code shows the WCF service class generated by the Add Adapter Service Reference Plug-in.

namespace OracleDBBindingNamespace {

    public class OracleDBBindingService : Notification_OperationGroup {

        // CODEGEN: Generating message contract since the wrapper namespace (http://Microsoft.LobServices.OracleDB/2007/03/Notification/) of message Notification
        // does not match the default value (http://Microsoft.LobServices.OracleDB/2007/03)
        public virtual void Notification(Notification request) {
            throw new System.NotImplementedException("The method or operation is not implemented.");
        }
    }
}

Receiving Database Change Notifications Using WCF Service Model

This section provides instructions on how to write a .NET application to receive query notifications using the Oracle Database adapter.

To receive query notifications

Use the Add Adapter Service Reference Plug-in to generate a WCF client for Select operation on the ACCOUNTACTIVITY table. You will use this client to perform Select operations after receiving a notification message. Add a new class, TableOperation.cs to your project and add the following code snippet to perform a Select operation.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Notification_ServiceModel
{
    class TableOperation
    {
        public void TableOp()
        {
            //////////////////////////////////////////////////////////////////////
            // CREATING THE CLIENT AND SETTING CLIENT CREDENTIALS
            //////////////////////////////////////////////////////////////////////

            SCOTT_Table_ACCOUNTACTIVITYClient client = new SCOTT_Table_ACCOUNTACTIVITYClient("OracleDBBinding_SCOTT_Table_ACCOUNTACTIVITY");
            client.ClientCredentials.UserName.UserName = "SCOTT";
            client.ClientCredentials.UserName.Password = "TIGER";

            ////////////////////////////////////////////////////////////////////
            // OPENING THE CLIENT
            //////////////////////////////////////////////////////////////////////
            try
            {
                Console.WriteLine("Opening the client ...");
                client.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                throw;
            }

            ////////////////////////////////////////////////////////////////////////////////////////
            // SELECTING THE LAST INSERTED VALUE
            ////////////////////////////////////////////////////////////////////////////////////////

            Console.WriteLine("The application will now select the last inserted record");

            microsoft.lobservices.oracledb._2007._03.SCOTT.Table.ACCOUNTACTIVITY.ACCOUNTACTIVITYRECORDSELECT[] selectRecords;

            try
            {
                selectRecords = client.Select("*", "WHERE PROCESSED = 'n'");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                throw;
            }

            Console.WriteLine("The details of the newly added records are:");
            Console.WriteLine("********************************************");
            for (int i = 0; i < selectRecords.Length; i++)
            {
                Console.WriteLine("Transaction ID   : " + selectRecords[i].TID);
                Console.WriteLine("Account ID       : " + selectRecords[i].ACCOUNT);
                Console.WriteLine("Processed Status : " + selectRecords[i].PROCESSED);
                Console.WriteLine();
            }
            Console.WriteLine("********************************************");
        }
    }
}

Use the Add Adapter Service Reference Plug-in to generate a WCF service contract (interface) and helper classes for the Notification operation.

For more information, see Generate a WCF client or a WCF service contract for Oracle Database solution artifacts. You can optionally specify the binding properties while generating the service contract and helper classes. This guarantees that they are properly set in the generated configuration file.

Implement a WCF service from the interface and helper classes generated in step 2. The Notification method of this class can throw an exception to abort the operation, if an error is encountered processing the data received from the Notification operation; otherwise the method does not return anything. You must attribute the WCF service class as follows:

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

Within the Notification method, you can implement your application logic directly. This class can be found in OracleDBBindingService.cs. This code in this example sub-classes the OracleDBBindingService class. In this code, the notification message received is written to the console. Additionally, the TableOp method within the TableOperation class is invoked to perform the Select operation.

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

    public class NotificationService : OracleDBBindingNamespace.OracleDBBindingService
    {
        public override void Notification(Notification request)
        {
            Console.WriteLine("\nNew Notification Received");
            Console.WriteLine("*************************************************");
            Console.WriteLine(request.Info);
            Console.WriteLine(request.Source);
            Console.WriteLine(request.Type);
            Console.WriteLine("*************************************************");

            TableOperation Ops = new TableOperation();
            Ops.TableOp();

        }
    }

You must implement the following class to pass credentials for the Oracle database. In the latter part of the application, you will instantiate this class to pass on the credentials.

class NotificationCredentials : ClientCredentials, IServiceBehavior
{
    public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
    {
        bindingParameters.Add(this);
    }

    public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
    { }

    protected override ClientCredentials CloneCore()
    {
        ClientCredentials clone = new NotificationCredentials();
        clone.UserName.UserName = this.UserName.UserName;
        clone.UserName.Password = this.UserName.Password;
        return clone;
    }
}

Create an OracleDBBinding and configure the adapter to receive query notifications by specifying the binding properties. You can do this either explicitly in code or declaratively in configuration. At a minimum, you must specify the InboundOperationType and NotificationStatement binding properties.
```
OracleDBBinding binding = new OracleDBBinding();
binding.InboundOperationType = InboundOperation.Notification;
binding.NotificationStatement = "SELECT TID,ACCOUNT,PROCESSED FROM APPS.ACCOUNTACTIVITY WHERE PROCESSED = 'n'";
binding.NotifyOnListenerStart = true;
binding.NotificationPort = 10;
```
Important

The value for the NotificationPort binding property must be set to the same port number that you must have added to the Windows Firewall exceptions list. For instructions on how to add ports to Windows Firewall exceptions list, see https://go.microsoft.com/fwlink/?LinkId=196959.

Important

If you do not set the NotificationPort binding property, the adapter will assume the default value of -1 for this binding property. In such a case, you will have to completely disable Windows Firewall to receive notification messages.

Specify Oracle database credentials by instantiating the NotificationCredentials class you created in Step 4.

NotificationCredentials credentials = new NotificationCredentials();
credentials.UserName.UserName = "SCOTT";
credentials.UserName.Password = "TIGER";

Create an instance of the WCF service created in step 3.

// create service instance
NotificationService service = new NotificationService();

Create an instance of System.ServiceModel.ServiceHost by using the WCF service and a base connection URI. You must also specify the credentials here.

// Enable service host
Uri[] baseUri = new Uri[] { new Uri("oracledb://adapter") };
ServiceHost serviceHost = new ServiceHost(service, baseUri);
serviceHost.Description.Behaviors.Add(credentials);

Add a service endpoint to the service host. To do this:

Use the binding created in step 5.
Specify a connection URI that contains credentials and, if required, an inbound ID.

Specify the contract as "Notification_OperationGroup".

// Add service endpoint: be sure to specify Notification_OperationGroup as the contract
Uri ConnectionUri = new Uri("oracledb://adapter");
serviceHost.AddServiceEndpoint("Notification_OperationGroup", binding, ConnectionUri);

To receive notification message, open the service host.

// Open the service host to begin receiving notifications
serviceHost.Open();

To stop receiving notifications, close the service host.
```
serviceHost.Close();
```

Example

The following example shows a .NET application to receive notification messages for the ACCOUNTACTIVITY table.

Note

The following code snippet instantiates a TableOperation.cs class and invokes the TableOp method. The class and the method are described in Step 1.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Adapters.OracleDB;
using Microsoft.ServiceModel.Channels;
using System.ServiceModel;
using System.ServiceModel.Description;
using System.ServiceModel.Channels;
using System.Collections.ObjectModel;

namespace Notification_ServiceModel
{
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

    public class NotificationService : OracleDBBindingNamespace.OracleDBBindingService
    {
        public override void Notification(Notification request)
        {
            Console.WriteLine("\nNew Notification Received");
            Console.WriteLine("*************************************************");
            Console.WriteLine(request.Info);
            Console.WriteLine(request.Source);
            Console.WriteLine(request.Type);
            Console.WriteLine("*************************************************");

            TableOperation Ops = new TableOperation();
            Ops.TableOp();

        }
    }

    class NotificationCredentials : ClientCredentials, IServiceBehavior
    {
        public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)
        {
            bindingParameters.Add(this);
        }

        public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)
        { }

        protected override ClientCredentials CloneCore()
        {
            ClientCredentials clone = new NotificationCredentials();
            clone.UserName.UserName = this.UserName.UserName;
            clone.UserName.Password = this.UserName.Password;
            return clone;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            ServiceHost serviceHost = null;
            try
            {
                Console.WriteLine("Sample started...");
                Console.WriteLine("Press any key to start receiving notifications...");
                Console.ReadLine();

                OracleDBBinding binding = new OracleDBBinding();
                binding.InboundOperationType = InboundOperation.Notification;
                binding.NotificationStatement = "SELECT TID,ACCOUNT,PROCESSED FROM APPS.ACCOUNTACTIVITY WHERE PROCESSED = 'n'";
                binding.NotifyOnListenerStart = true;
                binding.NotificationPort = 10;

                // This URI is used to specify the address for the ServiceEndpoint
                // It must contain the InboundId that was used to generate
                // the WCF service callback interface
                Uri ConnectionUri = new Uri("oracledb://adapter");

                // This URI is used to initialize the ServiceHost. It cannot contain
                // an InboundID; otherwise,an exception is thrown when
                // the ServiceHost is initialized.
                Uri[] baseUri = new Uri[] { new Uri("oracledb://adapter") };

                NotificationCredentials credentials = new NotificationCredentials();
                credentials.UserName.UserName = "SCOTT";
                credentials.UserName.Password = "TIGER";

                Console.WriteLine("Opening service host...");
                NotificationService service = new NotificationService();
                serviceHost = new ServiceHost(service, baseUri);
                serviceHost.Description.Behaviors.Add(credentials);
                serviceHost.AddServiceEndpoint("Notification_OperationGroup", binding, ConnectionUri);
                serviceHost.Open();
                Console.WriteLine("Service host opened...");
                Console.WriteLine("Waiting for notification...");

                Console.WriteLine("\nHit <RETURN> to stop receiving notification");
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception :" + e.Message);
                Console.ReadLine();

                /* If there is an error it will be specified in the inner exception */
                if (e.InnerException != null)
                {
                    Console.WriteLine("InnerException: " + e.InnerException.Message);
                    Console.ReadLine();
                }
            }
            finally
            {
                // IMPORTANT: you must close the ServiceHost to stop polling
                if (serviceHost.State == CommunicationState.Opened)
                    serviceHost.Close();
                else
                    serviceHost.Abort();
            }

        }
    }
}