Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
Work With OracleDependency
Author:
Gehan Fernando
E-mail:
Click to e-mail author
Submitted:
2/7/2008
Version:
VB 2005
Compatibility:
VB 2005, VB 2008
Category:
Databases
Views:
12528
'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
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement