dotConnect for Oracle Documentation
Devart.Data.Oracle Namespace / OracleDependency Class
Members Example

In This Topic
    OracleDependency Class
    In This Topic
    Represents a dependency between an application and an Oracle database.
    Syntax
    'Declaration
     
    Public Class OracleDependency 
       Implements System.IDisposable 
    public class OracleDependency : System.IDisposable  
    Remarks

    The OracleDependency class enables the application to receive notifications about changes in some portions of data or database state. This feature is available since Oracle 10.2.0.2.

    When you create an OracleDependency instance and add an OracleCommand object to it, a new OracleNotificationRequest is created, prepared, and assigned to the OracleCommand.Notification property. Upon the first execution of the OracleCommand, the OracleDependency object registers the notification and starts listening for messages from Oracle server on a port specified by the Port property. Every time a notification is received the OracleDependency fires the OnChange event. For continuous notifications (OracleNotificationRequest.IsNotifiedOnce is false) the registration stops when the OracleNotificationRequest.Timeout is expired, OracleCommand is disposed, or current Oracle session is terminated.

    This feature requires that the user has the appropriate privileges, specified with grant change notification to... statement.

    This feature is not available in Direct mode.

    This class is not available in Mobile Edition.

    Example

    To execute the following sample, you need that user Scott have the privilege to register change notifications. To grant him this privilege, the following script should be executed:

    grant change notification to Scott
    

    In the sample, the following actions are performed:

    1. an OracleDependency instance is registered for a simple select command;
    2. an insert command is executed, changing the result set associated with the select command;
    3. the notification is sent, it raises the OnChange event, which is then processed.
    static void Main(string[] args)
    {
            // Open the connection
            OracleConnection connection = new OracleConnection
                    ("Server = Ora; User Id = Scott; Password = tiger;");
            connection.Open();
                
            // Create the Select command retrieving all data from the Dept table.
            OracleCommand selectCommand = new OracleCommand("select * from dept", connection);
    
            // Create an OracleDependency object and set it to track the result set returned by selectCommand.
            OracleDependency dependency = new OracleDependency(selectCommand);
            
            // Setting object-based change notification registration
            dependency.QueryBasedNotification = false;
    
            // When the IsNotifiedOnce property is true, only the first change 
            // of the traced result set will generate a notification.
            // Otherwise, notifications will be sent on each change 
            // during the selectCommand.Notification.Timeout period.
            selectCommand.Notification.IsNotifiedOnce = true;
            // Specifies whether notifications will contain information on rows changed.
            selectCommand.Notification.RowLevelDetails = true;
    
            // Set the event handler to the OnChange event.
            dependency.OnChange += new OnChangeEventHandler(OnDeptChange);
                            
            // When the select command is executed at the first time, a notification 
            // on changes of the corresponding result set is registered on the server.
            selectCommand.ExecuteReader();
    
            // Set and execute an insert command. The Dept table data will be changed, 
            // and a notification will be sent, causing the OnChange event of the 'dependency' object.
            OracleCommand insertCommand = new OracleCommand
                    ("insert into dept values (100, 'New department', 'Some location')", connection);
            insertCommand.ExecuteNonQuery();
    
            // Pause the current thread to process the event.
            Thread.Sleep(10000);
    
    }
    
    // A simple event handler to handle the OnChange event.
    // Prints the change notification details.
    static void OnDeptChange(Object sender, OracleNotificationEventArgs args)
    {
            DataTable dt = args.Details;
    
            Console.WriteLine("The following database objects were changed:");
            foreach (string resource in args.ResourceNames)
                    Console.WriteLine(resource);
    
            Console.WriteLine("\n Details:");
            Console.Write(new string('*', 80));
            for (int rows = 0; rows < dt.Rows.Count; rows++)
            {
                    Console.WriteLine("Resource name: " + dt.Rows[rows].ItemArray[0]);
                    string type = Enum.GetName(typeof(OracleNotificationInfo), dt.Rows[rows].ItemArray[1]);
                    Console.WriteLine("Change type: " + type);
                    Console.Write(new string('*', 80));
            }
    }
    Sub Main(ByVal args() As String)
            ' Open the connection
            Dim connection = New OracleConnection( _
                    "Server = Ora; User Id = Scott; Password = tiger;")
            connection.Open()
    
            ' Create the Select command retrieving all data from the Dept table.
            Dim selectCommand = New OracleCommand("select * from dept", connection)
    
            ' Create an OracleDependency object and set it to track the result set returned by selectCommand.
            Dim dependency As OracleDependency = New OracleDependency(selectCommand)
            
            ' Setting object-based change notification registration
            dependency.QueryBasedNotification = False
    
            ' When the IsNotifiedOnce property is true, only the first change 
            ' of the traced result set will generate a notification.
            ' Otherwise, notifications will be sent on each change 
            ' during the selectCommand.Notification.Timeout period.
            selectCommand.Notification.IsNotifiedOnce = True
            ' Specifies whether notifications will contain information on rows changed.
            selectCommand.Notification.RowLevelDetails = True
    
            ' Set the event handler to the OnChange event.
            AddHandler dependency.OnChange, AddressOf OnDeptChange
    
            ' When the select command is executed at the first time, a notification 
            ' on changes of the corresponding result set is registered on the server.
            selectCommand.ExecuteReader()
    
            ' Set and execute an insert command. The Dept table data will be changed, 
            ' and a notification will be sent, causing the OnChange event of the 'dependency' object.
            Dim insertCommand = New OracleCommand( _
                    "insert into dept values (100, 'New department', 'Some location')", connection)
            insertCommand.ExecuteNonQuery()
    
            ' Pause the current thread to process the event.
            Thread.Sleep(10000)
    
    End Sub
    
    
    ' A simple event handler to handle the OnChange event.
    ' Prints the change notification details.
    Sub OnDeptChange(ByVal sender As Object, ByVal args As OracleNotificationEventArgs)
            Dim dt As DataTable = args.Details
    
            Console.WriteLine("The following database objects were changed:")
            For Each resource As String In args.ResourceNames
                    Console.WriteLine(resource)
            Next
    
            Console.WriteLine(vbCrLf + "Details: ")
            Console.Write(New String("*", 80))
    
            For rows As Integer = 0 To dt.Rows.Count - 1
                    Console.WriteLine("Resource name: " + dt.Rows(rows).ItemArray(0))
            Dim type As String = [Enum].GetName( _
                    GetType(OracleNotificationInfo), dt.Rows(rows).ItemArray(1))
            Console.WriteLine("Change type: " + type)
            Console.Write(New String("*", 80))
            Next
    End Sub
    Inheritance Hierarchy

    System.Object
       Devart.Data.Oracle.OracleDependency

    Requirements

    Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

    See Also