This project is read-only.
================ DISCONTINUED ================

OracleTableDependency

================ DISCONTINUED ================
OracleTableDependency is a C# class used to send events when the content of a specified database table change: is a high-level implementation to access notifications feature on ORACLE following the Service Oriented Database Architecture.

OracleTableDependency creates a table change dependency between the application and an ORACLE table, giving you the possibility to receive notifications for every DML operation done on the records.

Basically, it is an enhancement of .NET OracleDepenency with the advantage of send events containing values for the record inserted, changed or deleted, as well as the DML operation (insert/delete/update) executed on the table. This is the real difference with. NET OracleDepenency: this class, in fact, does not tell you what data was changed on the database.
================ DISCONTINUED ================

OracleTableDependency supports:
  • C# generic pattern
  • IDisposable pattern
  • Transaction: when data change operations are executed under transaction, the OracleTableDependency change event is not triggered until the commit is not called.

Under The Hood
================ DISCONTINUED ================
Assuming we want monitor the Customer table contents, we create a OracleTableDependency object specifying the Customer table and the following database objects will be generated:
  • Oracle Type
  • Queue
  • Queue Table
  • Stored Procedure
  • Table Trigger
  • Oracle Job
Figure 1. Database Object created


How to use
================ DISCONTINUED ================
Create a OracleTableDependency object and register to receive notifications via an event handler. Table 1 shows the members of the OracleTableDependency class.

Table 1. Members of the OracleTableDependency Class
Member Description
Start() This method starts the listener for receiving change notifications.
Stop() This method stops the listener.
OnChange This event occurs when a notification is received for any change done on table content.

To set up a dependency, you have to do the following:
  • Create an instance of OracleTableDependency passing the connection string and the table name to be monitored.
  • Subscribe to OracleTableDependency's OnChange event.
  • Call Start() method to start receiving records change notifications.
  • Call Stop() method to end receiving notifications. This operation will also destroy all database objects created for receiving notifications.
Let’s take a detailed look at each of these steps. Supposing the following database table:

CREATE TABLE "SYSTEM"."PRODUCTS" (
	"ID" NUMBER(*,0) NOT NULL, 
	"NAME" VARCHAR2(50 BYTE), 
	"Long Description" VARCHAR2(4000 BYTE), 
	 CONSTRAINT "PRODUCTS_PK" PRIMARY KEY ("ID"));

If a C# client needs be notified every time a record is modified, deleted or changed, you define the model containing the properties you'd like to have filled in with changed table values:

public class Item
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

The model can avoid to define all table columns if you are not interested in some of them.

Create the OracleTableDependency object passing the connection string and table name. Then create an event handler for OracleTableDependency's Changed event:

static void Main()
{
    var tableName = "PRODUCTS";
    var mapper = new ModelToTableMapper<Item>();
    mapper.AddMapping(c => c.Description, "Long Description");

    using (var tableDependency = new OracleTableDependency<Item>(
        connectionString,
        tableName, 
        modelToTableMapper: mapper))
    {
        tableDependency.OnChanged += Changed;
        tableDependency.OnError += tableDependency_OnError;

        tableDependency.Start();
        Console.WriteLine("Waiting for receiving notifications: " + 
                          "change some records in the table...");
        Console.WriteLine("Press a key to exit");
        Console.ReadKey();
    }
}

static void tableDependency_OnError(object sender, ErrorEventArgs e)
{
    Console.WriteLine(e.Error.Message);
}

static void Changed(object sender, RecordChangedEventArgs<Item> e)
{
    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;
        Console.WriteLine("DML operation: " + e.ChangeType);
        Console.WriteLine("ID: " + changedEntity.Id);
        Console.WriteLine("Name: " + changedEntity.Name);
        Console.WriteLine("Long Description: " + changedEntity.Description);
    }
}

Now you are ready to receive notifications: execute some insert, update or delete operation on PRODUCTS table and that change will be immediately received:



More examples Dependency NuGet package
================ DISCONTINUED ================ Contribute

Please, feel free to help and contribute with this project adding your comments, issues or bugs found.

If you find OracleTableDependency useful, please consider making a donation.

Donate to TableDependency

Contacts

Christian Del Bianco
christian.delbianco@gmail.com
christian.delbianco

Last edited Aug 15, 2016 at 9:41 PM by delbianco, version 24