Work With OracleDependency(vb.net)

Work With OracleDependency(vb.net)

Version: VB 2005
Compatibility: VB 2005
Category: Databases

'With the introduction of Oracle Database 10g Release 2, you have a new option that addresses the
'Limitations inherent in previous approaches to dealing with changing data: database change notification.
'When you use database change notification, the database server will notify you automatically when an
'Event occurs that changes objects associated with a specific query.
'Using the database change notification feature is a three-step process:

Declarations:
'Add Oracle Data Access Layer

Code:
Imports System
Imports System.Data
Imports System.Threading

Imports Oracle
Imports Oracle.DataAccess.Client

Public Class FrmOraDep

'With the introduction of Oracle Database 10g Release 2, you have a new option that addresses the
'Limitations inherent in previous approaches to dealing with changing data: database change notification.
'When you use database change notification, the database server will notify you automatically when an
'Event occurs that changes objects associated with a specific query.
'Using the database change notification feature is a three-step process:

'1. Registration: During the registration process, you specify a query that the database should watch for
'Changes. ODP.NET automatically registers the events to watch for, based on the query. The database
'Watches for Data Manipulation Language (DML) events, Data Definition Language (DDL) events, and
'Global events. (A DML event occurs when the underlying data of a query is changed. A DDL event
'Occurs when the structure of an object in the query is changed. A global event occurs when an action
'With a greater scope than the query alone takes place-the database is shut down, for example.)

'2. Notification: Once a query has been registered with the database for change notification, you specify
'how you would like to receive that notification. You can receive the notification-automatically from the
'Database-as an event in your application code, or you can poll the database. Most database change
'notification applications have the database automatically alert end users about changes, rather than
'using polling. (Note that ODP.NET needs to open a client network port to listen for the notification
'message from the database.)

'3. Response: Your application responds to the change notification by taking some action, as
'appropriate. In most cases, you'll automatically update the cached data without requiring end user
'interaction. Alternatively, you can notify the user that the data has changed and ask if the user would
'like to update the cached data.



'For your application to utilize change notification, the application's database user must have the
'CHANGE NOTIFICATION database privilege. Before running the code in Listing 1, run the following
'statement, using a DBA connection in a tool such as SQL*Plus or Oracle Developer Tools for Visual
'Studio .NET, to ensure that the HR user can use the change notification feature:
'grant change notification to hr;

Private OraCon As OracleConnection = Nothing
Private OraCom As OracleCommand = Nothing
Private OraPrm As OracleParameter = Nothing

Private OraDep As OracleDependency = Nothing
Private IsNotified As Boolean = False

Private Sub ButtonOpenConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonOpenConnection.Click

Me.Cursor = Cursors.WaitCursor

Try
If OraCon Is Nothing Then
OraCon = New OracleConnection("Data Source=RAS04;Persist Security Info=True;User ID=GEHAN;Password=gehan456;Pooling=False")
OraCon.Open()
End If

MessageBox.Show("Oracle Connection Open.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)

ButtonUpdateTable.Enabled = True
ButtonUpdateTable.Focus()
Catch ex As Exception
ButtonUpdateTable.Enabled = False
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

Me.Cursor = Cursors.Default

End Sub

Private Sub ButtonUpdateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUpdateTable.Click

Me.Cursor = Cursors.WaitCursor

Try
If OraCon.State = ConnectionState.Closed Then OraCon.Open()

Dim SELECT_Str As String = "SELECT FIRSTNAME,LASTNAME,SALARY FROM employees WHERE EMPLOYEEID = 1"
OraCom = New OracleCommand()
With OraCom
.Connection = OraCon
.CommandType = CommandType.Text
.CommandText = SELECT_Str
End With

OracleDependency.Port = 1005
OraDep = New OracleDependency(OraCom)
OraCom.Notification.IsNotifiedOnce = False

AddHandler OraDep.OnChange, AddressOf OraDep_OnChange

OraCom.ExecuteNonQuery()

Dim UPDATE_Str As String = "UPDATE employees SET SALARY = SALARY + 10 WHERE EMPLOYEEID = 1"
Dim Trn As OracleTransaction = OraCon.BeginTransaction
Dim UpdateCmd As New OracleCommand(UPDATE_Str, OraCon)
UpdateCmd.ExecuteNonQuery()
Trn.Commit()

OraCon.Close()
While (IsNotified = False)
Application.DoEvents()
Debug.WriteLine("Wait For Notification....")
System.Threading.Thread.Sleep(500)
End While

MessageBox.Show("Work Completed")
Catch ex As Exception
MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
OraDep = Nothing
OraCom = Nothing
End Try
Me.Cursor = Cursors.Default

End Sub

Private Sub FrmOraDep_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

ButtonUpdateTable.Enabled = False

End Sub

Private Sub OraDep_OnChange(ByVal sender As Object, ByVal eventArgs As Oracle.DataAccess.Client.OracleNotificationEventArgs)

Debug.WriteLine("Database Change Notification Received.")
Dim DTable As DataTable = eventArgs.Details
Debug.WriteLine("Resource {0} Has Changed." + DTable.Rows(0)(0))
IsNotified = True

End Sub

End Class